Relationele data importeren in SQL vanuit Excel
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:
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: