Relationele data importeren in SQL vanuit Excel

Geschreven door: op maandag 29 maart 2021

Leestijd: 6 minuten


In SQL Server kan je snel data importeren vanuit Excel-bestanden met behulp van OPENROWSET. Deze functie impoteert een werkblad in het Excel-bestand en plaatst de inhoud in een query, die je kan SELECT-en in een tabel.

Zie: 

    SELECT O.* INTO #TempImportTable
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\Items.xlsx;HDR=YES;IMEX=1;', 'SELECT * FROM [Blad1$]') AS O;

Excel is een krachtig programma om data mee te bewerken en iedereen kan ermee overweg. Het is echter een spreadsheet en geen relationele database. In SQL koppel je tabellen aan elkaar, die in een bepaalde relatie staan tot elkaar. Je kan een lijst van drie items hebben, die in een bepaalde volgorde gekoppeld zijn aan een ander item. Excel is hiervoor niet gemaakt.

Hieronder geef ik een oplossing voor het importeren van relaties in SQL vanuit Excel.

Ten eerste moeten we een bepaalde conventie hanteren voor de relaties in Excel. Je kan één kolom gebruiken en daarin kommagescheiden identifiers zetten. Kán vanuit data, maar vanuit het beheer in Excel niet wenselijk. Dan kan je andere werkbladen nemen of tabellen in Excel, maar ook dat werkt niet fijn in Excel en maakt het importeren moeilijk. Ik kies ervoor om _[type_relatie]_[volgorde_in_tabel] te hanteren:

Voor dit voorbeeld maak ik een simpel datamodel in SQL:

blog-sql-import-relationeel-3.jpg

En importeer ik deze data:

SQL:   

DROP TABLE IF EXISTS #TempImportTable;

    SELECT O.* INTO #TempImportTable
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\Items.xlsx;HDR=YES;IMEX=1;', 'SELECT * FROM [Blad1$]') AS O;

    MERGE Items
        AS T

    USING(
        SELECT ItemId, Name FROM #TempImportTable
    )
        AS S

        ON S.ItemId = T.ItemId

    WHEN MATCHED THEN
        UPDATE SET
            Name = S.Name

    WHEN NOT MATCHED BY TARGET THEN
        INSERT (ItemId, Name)
        VALUES(
            S.ItemId,
            S.Name
        )
    ;

    SELECT * FROM Items;
    SELECT * FROM SubItems;

Resultaat:

De data is uiteraard via een MERGE eenvoudig te updaten, maar de relaties worden niet meegenomen. Ik kan uiteraard kijken of _SubItem_1 bestaat en dan iets doen en dan kijken naar _SubItem_2, et cetera. Bestaat 3, doe dan dit. Misschien bestaat 3 niet, maar 4 wel. Dat is niet erg efficient. Ik zou het gemakkelijker kunnen maken door een LOOP te gebruiken, maar dat is niet waar SQL qua performance sterk in is, als het gaat om gróte datasets.

Mijn oplossing is als volgt:

  1. Haal alle uniek kolommen op die beginnen met _subItem_.
  2. Maak een tijdelijke tabel met daarin de kolommen en waardes, via UNPIVOT.
  3. Plaats via een replace de namen van de kolommen (want dynamische kolomnamen mogen niet met PIVOT/UNPIVOT).
  4. Voer de dynamische SQL uit en update de SubItems-Tabel via MERGE.
  5. Dan kan je daarna nog een aparte DELETE statement maken om de vervallen data te verwijderen.

Haal alle uniek kolommen op die beginnen met _subItem_   

 DECLARE @subItemIndex TABLE(i nvarchar(255));

    INSERT INTO @subItemIndex (i)
    Select name From tempdb.sys.columns
        Where object_id=OBJECT_ID('tempdb.dbo.#TempImportTable')
        AND name LIKE '_subItem_%'
    ;

    SELECT * FROM @subItemIndex

 

Maak een tijdelijke tabel met daarin de kolommen en waardes, via UNPIVOT

    DROP TABLE IF EXISTS #TempPivotTable;
    CREATE TABLE #TempPivotTable(ItemId int, [order] int, SubItemId int)

    DECLARE @pivotSql nvarchar(max) = N'
        INSERT INTO #TempPivotTable (ItemId, [order], subItemId)
        SELECT ItemId, replace(subItemColumn, ''_SubItem_'', '''') as [order], subItemId
        FROM   
        (SELECT ItemId, ___SUBITEMS___  FROM #TempImportTable) p  
        UNPIVOT  
        (subItemId FOR subItemColumn IN   
            (___SUBITEMS___)  
        )AS subitemsUnpivot;
    ';

 

Plaats via een replace de namen van de kolommen (want dynamische kolomnamen mogen niet met PIVOT/UNPIVOT)
   

 DECLARE @replacePivotSql nvarchar(max) = N'';

    SET @replacePivotSql = STUFF(
        (
        SELECT ',' + QUOTENAME(i) FROM @subItemIndex 
        FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'
        ), 1, 1, '')
    ;


    SET @pivotSql = REPLACE(@pivotSql, '___SUBITEMS___', @replacePivotSql);

    print @pivotSql;
    /*
        INSERT INTO #TempPivotTable (ItemId, [order], subItemId)
        SELECT ItemId, replace(subItemColumn, '_SubItem_', '') as [order], subItemId
        FROM   
        (SELECT ItemId, [_SubItem_1],[_SubItem_2],[_SubItem_3]  FROM #TempImportTable) p  
        UNPIVOT  
        (subItemId FOR subItemColumn IN   
            ([_SubItem_1],[_SubItem_2],[_SubItem_3])  
        )AS subitemsUnpivot;
    */

    EXECUTE sp_executesql @pivotSql;

Voer de dynamische SQL uit en update de SubItems-Tabel via MERGE
 

  MERGE SubItems
        AS T
    USING(
        SELECT ItemId, [order], SubItemID FROM #TempPivotTable
    )
        AS S
        ON S.ItemId = T.ItemId AND S.[order] = T.[Order]
    WHEN NOT MATCHED THEN
        INSERT
            (
                ItemId,
                [order],
                [SubItemId]
            )
            VALUES(
                S.ItemId,
                S.[order],
                S.[SubITemId]
            )
    WHEN MATCHED THEN
        UPDATE SET
            T.SubITemId = S.SubItemID
    ;

Resultaat:


Dan kan je daarna nog een aparte DELETE statement maken om de vervallen data te verwijderen

    DELETE FROM...

En zo heb je data uit dynamische kolommen geïmporteerd in je relationeel model.


Andere blogartikelen

Bel 072 5345 888
Meer dan 40 bedrijven vertrouwen op ons
Bekijk al onze tevreden klanten ›
Open Nieuwsbrief Inschrijving Footer

E-book

Zo wordt uw website een lead generator:
In 3 stappen uw website van visitekaartje naar salesfunnel

Download het E-book »

E-book

Zo wordt uw website een lead generator 
In 3 stappen uw website van visitekaartje naar salesfunnel

Download het E-book ›