1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen

[VBA][Access]Daten über eine Stagetable

Komplexerer import mit hilfe einer Stagetable

  1. Yaslaw
    [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

    STG_DEALS.csv
    Code (Text):
    1. id,user,deal_date,amount
    2. 3;Hans;20170207;EUR 10.500,00-
    3. 4;Peter;20170208;USD 5.454,00
    4. 5;Sandra;20170216;EUR 16.546.345,35-
    Für einen Sinnvollen Import gibt es nun 2 Problemfelder.
    • deal_date: Das Format YYYYMMDD ist kein Datum
    • amount: Da drin sind gleich 3 Probleme.
      1. Die Währung ist mit drin
      2. Mein englisches Acces erwartet das Format #'##0.00 und nicht #.##0,00
      3. Das Minus ist am Schluss der Zahl
    Meine Zieltabellen TBL_USER und TBL_DEALS sehen so aus
    TBL_USER
    Code (Text):
    1. id            | user
    2. --------------|----------
    3. Autoincrement | Text(50)
    4. --------------|----------
    5.      11       | Hans
    6.      12       | Sandra
    TBL_DEALS
    Code (Text):
    1. internal_id   | external_id | user_id | deal_date  | ccy     | amount    
    2. --------------|-------------|---------|------------|---------|--------------
    3. Autoincrement | Numeric     | Numeric | Date       | Text(3) | Numeric
    4. --------------|-------------|---------|-----------_|---------|--------------
    5.             1 |           3 |      11 | 07.02.2017 | EUR     |     -10'500.00
    6.             2 |           4 |      13 | 08.02.2017 | USD     |       5'454.00
    7.             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
    STG_DEALS
    Code (SQL):
    1. CREATE TABLE STG_DEALS (
    2.    id TEXT(10),
    3.    USER TEXT(50),
    4.    deal_date TEXT(10),
    5.    amount TEXT(50),
    6.    user_id NUMERIC,
    7. );
    Import
    Als nächstes importieren wie die Daten einfach aus der Datei.
    Dazu habe ich eine Spezifikation hinterlegt. Ich nenne sie IN_STG_DEALS
    IN_STG_DEALS.png

    Dann die VBA-Funktion um das CSV in die STG zu importieren

    importDeals.bas
    Code (Visual Basic):
    1. Public Sub importDeals()
    2.     'Die alten Stagedaten entfernen
    3.    CurrentDb.execute "delete from STG_DEALS"
    4.     'Das CSV einlesen
    5.    DoCmd.TransferText acImportDelim, "IN_STG_DEALS", "STG_DEALS", "C:\am_access_apps\deals.csv", True
    6. End Sub
    Nun habe ich das folgende in meiner Tabelle STG_DEALS

    STG_DEALS
    Code (Text):
    1. id | user   | deal_date | amount          
    2. ---|--------|-----------|-------------------
    3. 3  | Hans   | 20170207  | EUR 10.500,00-  
    4. 4  | Peter  | 20170208  | USD 5.454,00    
    5. 5  | Sandra | 20170216  | EUR 16.546.345,35-
    Normalisieren der User
    in meiner TBL_USER sind 2 User bereits vorhanden (Hans & Sandra). Der 3te User muss hinzugefügt werden.

    VW_RUN_INSERT_USER

    Code (SQL):
    1. INSERT INTO TBL_USER (USER)
    2. SELECT d.USER
    3. FROM STG_DEALS d LEFT JOIN TBL_USER u ON d.USER = u.USER
    4. 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

    VW_RUN_ADD_USER_ID_TO_STG
    Code (SQL):
    1. UPDATE
    2.    STG_DEALS d
    3.    INNER JOIN TBL_USER u
    4.    ON  u.USER = d.USER
    5. SET d.user_id = u.id;
    Somit haben wir in der STG_DEALS nun den folgenden Datenstand

    STG_DEALS
    Code (Text):
    1. id | user   | deal_date | amount             | user_id
    2. ---|--------|----------_|--------------------|--------
    3. 3  | Hans   | 20170207  | EUR 10.500,00-     | 11    
    4. 4  | Peter  | 20170208  | USD 5.454,00       | 13    
    5. 5  | Sandra | 20170216  | EUR 16.546.345,35- | 12    
    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:
    - strToDate() Parst einen String mittels eines Datumsformates in en Datum
    - toDblGeneric() Eine Funktion die einiges mächtiger ist als cDbl() von Microsoft

    VW_RUN_APPEND_TO_DEALS
    Code (SQL):
    1. INSERT INTO TBL_DEALS(external_id, user_id, deal_date, ccy, amount)
    2. SELECT
    3.    d.id AS external_id,
    4.    d.user_id,
    5.    strToDate(d.deal_date, 'YYYYMMDD') AS deal_date,
    6.    LEFT(d.amount, 3) AS ccy,
    7.    toDblGeneric(d.amount) AS amount
    8. FROM STG_DEALS d
    Fertiger Code
    Jetzt nur noch alles zusammensetzn und fertig ist unser sauberer Import
    Code (Visual Basic):
    1. Public Sub importDeals()
    2.     'Die alten Stagedaten entfernen
    3.    CurrentDb.execute "delete from STG_DEALS"
    4.     'Das CSV einlesen
    5.    DoCmd.TransferText acImportDelim, "IN_STG_DEALS", "STG_DEALS", "C:\am_access_apps\deals.csv", True
    6.     'Normalisieren des Users
    7.    CurrentDb.execute "VW_RUN_INSERT_USER"
    8.     CurrentDb.execute "VW_RUN_ADD_USER_ID_TO_STG"
    9.     'Daten in die TBL_DEALS überführen
    10.    CurrentDb.execute "VW_RUN_APPEND_TO_DEALS"
    11. End Sub
    Und die Daten
    TBL_USER
    Code (Text):
    1. id | user
    2. ---|----------
    3. 11 | Hans
    4. 12 | Sandra
    5. 13 | Peter
    TBL_DEALS
    Code (Text):
    1. internal_id | external_id | user_id | deal_date  | ccy | amount    
    2. ------------|-------------|---------|------------|-----|-------------
    3. 2           | 3           | 11      | 07.02.2017 | EUR |    -10500    
    4. 3           | 4           | 13      | 08.02.2017 | USD |      5454      
    5. 4           | 5           | 12      | 16.02.2017 | EUR | -16546345.35