Excel- en tekstbestanden gebruiken in SQL Server 2005 Management Studio

Tijdens een klus in Amersfoort kreeg ik eens een aantal .csv bestanden en een Microsoft Excel bestand in mijn spreekwoordelijke schoot geworpen. Deze bestanden moesten gesynchroniseerd worden met tabellen in een MS SQL database. In deze post laat ik zien hoe je dit kunt doen met behulp van de Microsoft SQL Server Management Studio Express.

Importeren van tekstbestanden (.txt en .csv)

SELECT *
INTO tabelA
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Data;','select * from tabelB.csv')

Importeren van Excel bestanden

SELECT *
INTO tabelA FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DataBase=C:\Data\tabelB.xls;', 'Select * from [sheet1$]')

Het zou kunnen dat MS SQL Server Management gaat zeuren over “Ad hoc access to … has been denied“. Start hier voor “SQL Server Surface Area Configuration” via Start, All Programs, Microsoft SQL Server 2005, Configuration Tools. Klik dan op “Surface Area Configuration for Features” en zet bij “Ad Hoc Remote Queries” het vinkje “Enable OPENROWSET en OPENDATASOURCE support” aan.

Verschil bepalen

Er zijn waarschijnlijk nogal wat records in de nieuwe tabel bijgekomen en om dit verschil te bekijken voeren we de volgende query uit :

select * from tabelB where ID not in (select ID from tabelA)

Nu we de geïmporteerde tabellen in de database hebben staan en we de verschillen hebben gezien, kunnen we gaan synchroniseren indien nodig. Voor het updaten hebben we in beide tabellen een ID nodig waar we op kunnen selecten bij het updaten omdat we niet handmatig alle voor alle verschillen een update willen uitvoeren. Ditzelfde geldt voor inserten, immers : gemak dient de mens!

Updaten

UPDATE TabelA
SET Naam = TabelB.Naam,
Voorvoegsels = TabelB.voorvoegsels,
Voorletters = TabelB.voorletters,
Straat = TabelB.straat,
Huisnummer = TabelB.huisnummer,
Postcode = TabelB.postcode,
Plaats = TabelB.plaats,
Land = TabelB.land
FROM TabelB INNER JOIN
TabelA ON TabelA.ID = TabelB.ID;

Inserten

INSERT INTO TabelA(
ID,
naam,
Voorvoegsels,
Voorletters,
Straat,
Huisnummer,
Postcode,
Plaats,
Land
)
SELECT
newid() as ID,
naam,
Voorvoegsels,
Voorletters,
Straat,
Huisnummer,
Postcode,
Plaats,
Land
FROM TabelB
AS TempTabelB
WHERE ID not in (select ID from TabelA)

De functie newid() zit standaard in MS SQL ingebakken en creëert (de naam zegt het al) een nieuwe id zoals bijvoorbeeld deze : “e2b9c6cc-e749-400c-8678-7db36ba2fb4f”. Dit ID is goed te gebruiken wanneer bij een insert niet automatisch een ID gegenereerd wordt.

De conversie van Excel bestand of csv naar een tabel in de database en de update/insert heb ik in een SQL-bestand gezet zodat deze met behulp van Microsoft SQL Server Management Studio Express gemakkelijk uit te voeren is; Simpelweg openen en op F5 drukken!

Heeft iemand nog op of aanmerkingen? Ik hoor het graag!