Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [VBA][Access]Daten über eine Stagetable importieren ======
//Komplexerer import mit hilfe einer Stagetable//

===== Ausgangslage =====
Ich habe ein Datenfile. In dem Fall als csv. Kann aber auch Excel etc. sein.
Dieses bekomme ich von einem anderen System. An den Datenfeldern und ihrer Formatierung kann man nicht rütteln. Leider

<code csv STG_DEALS.csv>id,user,deal_date,amount
3;Hans;20170207;EUR 10.500,00-
4;Peter;20170208;USD 5.454,00
5;Sandra;20170216;EUR 16.546.345,35-</code>

Für einen Sinnvollen Import gibnt es nun 2 Problemfelder.
  * **deal_date**: Das Format YYYYMMDD ist kein Datum
  * **amount**: Da drin sind gleich 3 Probleme. 
    - Die Währung ist mit drin
    - Mein englisches Acces erwartet das Format #'##0.00 und nicht #.##0,00
    - Das Minus ist am Schluss der Zahl

Meine Zieltabellen TBL_USER und TBL_DEALS sehen so aus
<code text TBL_USER>id            | user
--------------|----------
Autoincrement | Text(50)
--------------|----------
     11       | Hans
     12       | Sandra</code>
<code text TBL_DEALS>internal_id   | external_id | user_id | deal_date  | ccy     | amount     
--------------|-------------|---------|------------|---------|--------------
Autoincrement | Numeric     | Numeric | Date       | Text(3) | Numeric
--------------|-------------|---------|-----------_|---------|--------------
            1 |           3 |      11 | 07.02.2017 | EUR     |     -10'500.00
            2 |           4 |      13 | 08.02.2017 | USD     |       5'454.00
            3 |           5 |      12 | 16.02.2017 | EUR     | -16'546'345.35</code>

Das gibt einiges zu tun um diese Datei zu überführen
Als erste erstelle ich eine Stage-Tabelle. Diese entspricht den Feldern der Quelldatei. Alle Felder sind einfach mal als Textfelder definiert.


Zusätzlich ist noch das Feld user_id als Zahl vorhanden. Der Grund wird später ersichtlich
<code sql STG_DEALS>create table STG_DEALS (
	id TEXT(10),
	user TEXT(50),
	deal_date TEXT(10),
	amount TEXT(50),
	user_id NUMERIC,
);</code>

===== Import =====
Als nächstes importieren wie die Daten einfach aus der Datei.
Dazu habe ich eine Spezifikation hinterlegt. Ich nenne sie IN_STG_DEALS
{{:vba:tutorials:in_stg_deals.png?400|}}

Dann die VBA-Funktion um das CSV in die STG zu importieren
<code vb importDeals.bas>Public Sub importDeals()
    'Die alten Stagedaten entfernen
    CurrentDb.execute "delete from STG_DEALS"
    'Das CSV einlesen
    DoCmd.TransferText acImportDelim, "IN_STG_DEALS", "STG_DEALS", "C:\am_access_apps\deals.csv", True
End Sub</code>

Nun habe ich das folgende in meiner Tabelle STG_DEALS
<code text STG_DEALS>id | user   | deal_date | amount             | user_id
---|--------|-----------|--------------------|--------- 
3  | Hans   | 20170207  | EUR 10.500,00-     | 
4  | Peter  | 20170208  | USD 5.454,00       | 
5  | Sandra | 20170216  | EUR 16.546.345,35- | </code>

===== Normalisieren der User =====
in meiner TBL_USER sind 2 User bereits vorhanden (Hans & Sandra). Der 3te User muss hinzugefügt werden.

<code sql VW_RUN_INSERT_USER>insert into TBL_USER (user) 
select d.user 
from STG_DEALS d left join TBL_USER u on d.user = u.user
where u.user is null;</code>

Somit wurde der neue User angelegt Ich könnte die Daten auch später erst mit der TBL_USER.user_id verknüpfen, doch ich mache das lieber jetzt. Dazu haben wir in der Stage-Tabelle das Feld user_id angelegt
<code sql VW_RUN_ADD_USER_ID_TO_STG>UPDATE 
	STG_DEALS d 
	inner JOIN TBL_USER u 
	ON  u.user = d.user
SET d.user_id = u.id;</code>

Somit haben wir in der STG_DEALS nun den folgenden Datenstand
<code text STG_DEALS>id | user   | deal_date | amount             | user_id
---|--------|----------_|--------------------|--------
3  | Hans   | 20170207  | EUR 10.500,00-     | 11     
4  | Peter  | 20170208  | USD 5.454,00       | 13     
5  | Sandra | 20170216  | EUR 16.546.345,35- | 12     </code>

===== Migrieren der Deals nach TBL_DEALS =====
Nun ist es an der Zeit, die Daten aus der STG_DEALS in die TBL_DEALS zu überführen.
Ich verwende der Einfachheit 2 Funktionen, die ich bereits geschrieben habe: 
  - [[vba:cast:strtodate|strToDate()]] Parst einen String mittels eines Datumsformates in ein Datum
  - [[vba:cast:todoublegeneric|toDoubleGeneric()]] EIne Funktion die einiges mächtiger ist als cDbl() von Microsoft

<code sql VW_RUN_APPEND_TO_DEALS>insert into TBL_DEALS(external_id, user_id, deal_date, ccy, amount)
select
	d.id as external_id,
	d.user_id,
	strToDate(d.deal_date, 'YYYYMMDD') as deal_date,
	left(d.amount, 3) as ccy,
	toDblGeneric(d.amount) as amount
from STG_DEALS d</code>

===== Fertiger Code =====
Jetzt nur noch alles zusammensetzn und fertig ist unser sauberer Import
<code vb importDeals>Public Sub importDeals()
    'Die alten Stagedaten entfernen
    CurrentDb.execute "delete from STG_DEALS"
    'Das CSV einlesen
    DoCmd.TransferText acImportDelim, "IN_STG_DEALS", "STG_DEALS", "C:\am_access_apps\deals.csv", True
    'Normalisieren des Users
    CurrentDb.execute "VW_RUN_INSERT_USER"
    CurrentDb.execute "VW_RUN_ADD_USER_ID_TO_STG"
    'Daten in die TBL_DEALS überführen
    CurrentDb.execute "VW_RUN_APPEND_TO_DEALS"
End Sub
</code>

Und die Daten
<code text TBL_USER>id | user
---|----------
11 | Hans
12 | Sandra
13 | Peter</code>
<code text TBL_DEALS>internal_id | external_id | user_id | deal_date  | ccy | amount      
------------|-------------|---------|------------|-----|-------------
2           | 3           | 11      | 07.02.2017 | EUR |    -10500      
3           | 4           | 13      | 08.02.2017 | USD |      5454        
4           | 5           | 12      | 16.02.2017 | EUR | -16546345.35</code>
Yaslaw.Info

User Tools

Site Tools

Page Tools
