Excel- en tekstbestanden gebruiken in SQL Server 2005 Management Studio

 
13 februari 2008

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!


Werken met ?
Kijk dan bij onze mogelijkheden voor zowel starters als ervaren engineers.


Categorieën: Development

Tags: , ,


Reacties (4)

  • Charles Storm schreef:

    Al sinds jaren programmeer ik semi-ptrofessioneel. Nooit eerder zo’n aardige, transparante uitleg gezien. Was juist op zoek naar XLS naar SQL. Met dank

    Geplaatst op 21 september 2011 om 12:56 Permalink

  • Kijk dat zijn nog eens tips! Vooral : “Export of database(s) to text-file(s) or other databases” en “Import of data into databases, ranging from text-files to excel sheets” spreekt me wel aan ;) Ga ik vanavond eens even uittesten dat tooltje… Bedankt!

    Geplaatst op 20 februari 2008 om 11:57 Permalink

  • Ik heb tegewoordig Pentaho Data Integration (PDI) in m’n “toolchain” zitten. Het transformeren van data (in database, uit database, naar andere formaten) en allerlei andere operaties handig uit te voeren. (http://kettle.pentaho.org/)

    Geplaatst op 20 februari 2008 om 6:59 Permalink

  • Jaap Taal schreef:

    Erg nuttig om weten dat zoiets mogelijk is. Het enige wat je hoeft te onthouden is dat je het op software-innovators kunt terugvinden. Heel helder geformuleerd!

    Geplaatst op 14 februari 2008 om 23:46 Permalink