Relationele data importeren in SQL vanuit Excel header image

Relationele data importeren in SQL vanuit Excel

maandag 29 maart 2021 ·Leestijd: 2 minuten
contacteer auteur:


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:

 


Andere blogartikelen

Tips voor een snellere B2B website
Tips voor een snellere B2B website

03 augustus 2022

Uw B2B website verbeteren zodat u resultaten kunt behalen
Uw B2B website verbeteren zodat u resultaten kunt behalen

03 augustus 2022

Financiële Onderbouwing van de B2B Webshop Business Case
Financiële Onderbouwing van de B2B Webshop Business Case

02 augustus 2022

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 â€º