Komplexerer import mit hilfe einer Stagetable
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
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-
Für einen Sinnvollen Import gibnt es nun 2 Problemfelder.
Meine Zieltabellen TBL_USER und TBL_DEALS sehen so aus
id | user --------------|---------- Autoincrement | Text(50) --------------|---------- 11 | Hans 12 | Sandra
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
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
CREATE TABLE STG_DEALS ( id TEXT(10), USER TEXT(50), deal_date TEXT(10), amount TEXT(50), user_id NUMERIC, );
Als nächstes importieren wie die Daten einfach aus der Datei. Dazu habe ich eine Spezifikation hinterlegt. Ich nenne sie IN_STG_DEALS
Dann die VBA-Funktion um das CSV in die STG zu importieren
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
Nun habe ich das folgende in meiner Tabelle 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- |
in meiner TBL_USER sind 2 User bereits vorhanden (Hans & Sandra). Der 3te User muss hinzugefügt werden.
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;
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
UPDATE STG_DEALS d INNER JOIN TBL_USER u ON u.user = d.user SET d.user_id = u.id;
Somit haben wir in der STG_DEALS nun den folgenden Datenstand
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
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:
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
Jetzt nur noch alles zusammensetzn und fertig ist unser sauberer Import
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
Und die Daten
id | user ---|---------- 11 | Hans 12 | Sandra 13 | Peter
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