In meinem gestrigen SQL-Kurs fragte mich ein Teilnehmer, wie man Daten aus einer CSV- oder XLS-Datei in den Microsoft SQL-Server laden kann.
Dazu gibt es ein ganzes Bündel an Möglichkeiten. Wenn man selber Administrator ist und für den eigenen Bedarf Daten importieren will, dann würde ich den Einsatz von OpenRowSet bevorzugen.
Das erkläre ich im folgenden im Rahmen einer Work-Bench: Man kann den kompletten Code in das Management-Studio laden und schrittweise ausführen. Dazu markiert man die Statements,
die zwischen zwei "go" stehen und drückt auf "Ausführen". Dadurch werden nur die markierten Befehle ausgeführt.
Die benötigten Dateien kann man auf einen Schwupps speichern.
Eigentlich wollte ich es so machen, dass man den ganzen Text einfach per Cut&Paste ins Management-Studio ziehen kann, aber WordPress wandelt die einfachen geraden Anführungszeichen immer in schräge um. Damit kommt SQL aber nicht zurecht. Daher werden die Dateien dennoch benötigt.
Vorarbeiten: OpenRowSet erlauben
Zuerst den Zugriff auf externe Quellen mittels OpenRowSet erlauben
Achtung: bitte nur für den internen Bedarf oder privat einsetzen.
exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override
XLS lesen
Hier ein Beispiel-Zugriff auf ein Blatt in einer XLS-Datei. Die XLS-Datei ist auf dem Server unter "I:\Openrowset\testing.xls" gespeichert:
select * from OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=I:\Openrowset\testing.xls','SELECT * FROM [Tabelle1$]')
Hätte das Blatt einen anständigen Namen, dann würde man anstelle von "Tabelle1$" diesen Namen schreiben.
In XLS schreiben
Man kann auch in ein bereits existierendes Excel-Document schreiben:
insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=I:\Openrowset\testing.xls','SELECT * FROM [Tabelle1$]')
(Nachname, Vorname, Geburtstag, Wohnort)
values ('Bond', 'James', '4.4.1930', 'London')
– Kontrolle, ob es geklappt hat:
select * from OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=I:\Openrowset\testing.xls','SELECT * FROM [Tabelle1$]')
Es ist mir nicht gelungen ein neues Dokument anzulegen.
Aus XLS löschen
Das Löschen geht leider nicht:
delete from OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=I:\Openrowset\testing.xls','SELECT * FROM [Tabelle1$]')
where nachname = 'Bond'
Es kommt ein Fehler, weil der Provider das Löschen nicht unterstützt:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "ISAM unterstützt das Löschen von Daten in einer verknüpften Tabelle nicht.".
Msg 7345, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" could not delete from table "SELECT * FROM [Tabelle1$]". There was a recoverable, provider-specific error, such as an RPC failure.
Zeilen aus XLS ändern
Das Ändern von Datensätzen geht hingegen:
Update OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=I:\Openrowset\testing.xls','SELECT * FROM [Tabelle1$]')
set nachname = 'Blond'
where nachname = 'Bond'
– Kontrolle, ob es geklappt hat:
select * from OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=I:\Openrowset\testing.xls','SELECT * FROM [Tabelle1$]')
CSV lesen
Das Lesen der CSV geht auch mit dem Jet-Treiber:
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Text;Database=I:\Openrowset\','SELECT * FROM [testing.csv]')
Hier gibt man als "Database" nur das Verzeichnis an, die Datei folgt im Select.
Man kann auch über ODBC lesen:
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=I:\Openrowset\;Extensions=CSV;',
'SELECT * FROM [testing.csv]')
Wenn man "komplizierte" Formate über ODBC lesen will, dann muss man in der Datei "schema.ini" die Formate definieren.
Als Beispiel habe ich die Datei mit den Losungen 2007 aus http://www.brueder-unitaet.de/download/Losung_2007_CSV.zip verwendet.
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=I:\Openrowset\;Extensions=CSV;',
'SELECT * FROM [Losungen Free 2007.csv]')
/* Schema.ini in "I:\Openrowset\":
[losungen free 2006.csv]
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI
Format=Delimited(;)
*/
Nacharbeiten: OpenRowSet wieder abschalten
exec sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override
exec sp_configure 'show advanced options', 0
reconfigure with override
