3
Bulk insert fehlerhafte Datensätze ignorieren
Hallo,
wir müssen per bulk insert Daten aus einer csv Datei importieren.
bulk INSERT [dbo].[meineTabelle]
FROM 'E:\Daten\dw5rech00f.csv'
WITH
(
FORMAT='CSV',
FIRSTROW=1,
ERRORFILE = 'E:\Daten\Fehler',
MAXERRORS = 10
)
Dies funktioniert auch gut, sofern die Felder die richtigen Daten enthalten.
Heute hatte wir einen Datensatz mit einem " in einem Textfeld wodurch der Import unterbrochen wurde.
Die Fehlerhafte Zeile wurde zwar in die Fehlerdatei geschrieben, der Insert aber doch mit einem Fehler Unterbrochen.
Inzwischen habe ich einfach FIELDQUOTE = '^' eingetragen, in der Hoffnung, dass dieses Zeichen nie verwendet wird.
Aus Oracle Zeiten erinnere ich mich, dass dort ein Bulk insert fehlerhafte Datensätze in eine Fehlertabelle schrieb und alle anderen importiert wurden.
Gibt es sowas im sql server nicht?
Grüße
Andreas
wir müssen per bulk insert Daten aus einer csv Datei importieren.
bulk INSERT [dbo].[meineTabelle]
FROM 'E:\Daten\dw5rech00f.csv'
WITH
(
FORMAT='CSV',
FIRSTROW=1,
ERRORFILE = 'E:\Daten\Fehler',
MAXERRORS = 10
)
Dies funktioniert auch gut, sofern die Felder die richtigen Daten enthalten.
Heute hatte wir einen Datensatz mit einem " in einem Textfeld wodurch der Import unterbrochen wurde.
Die Fehlerhafte Zeile wurde zwar in die Fehlerdatei geschrieben, der Insert aber doch mit einem Fehler Unterbrochen.
Inzwischen habe ich einfach FIELDQUOTE = '^' eingetragen, in der Hoffnung, dass dieses Zeichen nie verwendet wird.
Aus Oracle Zeiten erinnere ich mich, dass dort ein Bulk insert fehlerhafte Datensätze in eine Fehlertabelle schrieb und alle anderen importiert wurden.
Gibt es sowas im sql server nicht?
Grüße
Andreas
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 6487054540
Url: https://administrator.de/contentid/6487054540
Printed on: April 27, 2024 at 09:04 o'clock
3 Comments
Latest comment
Ich glaube das geht in MSSQL nicht, ich mag mich aber irren, dann würde mich die Lösung auch interessieren.
Ich habe mir bisher unterschiedlich beholfen:
a) Zum einen kannst du die CSV Datei vorab mit Excel öffnen (bitte nie mit Excel speichern!). Excel zeigt dann keine Fieldquotes mit an, was das händische Suchen nach eben diesen ermöglicht. Wenn du dann weist in welcher Zeile die stehen, kannst du mit Notepad++ vorab die Zeichen entfernen oder ersetzen (eventuell auch escapen, da bin ich mir jetzt nicht sicher ob BULK INSERT damit umgehen kann).
b) In einem Projekt, bei dem ich automatisch importieren möchte, schreibe ich tatsächlich alles erst in eine Spalte. Im Anschluss prüfe ich die Anzahl der Fieldquotes und zerlege die Daten in SQL (mit XML Funktionen). Bombensicher, aber viel zu Fuß in SQL geschrieben.
c) Das beste ist die Sache an der Quelle anzugehen. Einige wenige Programme bieten beim Export die Möglichkeit, dieses Verhalten zu unterbinden.
Ich habe mir bisher unterschiedlich beholfen:
a) Zum einen kannst du die CSV Datei vorab mit Excel öffnen (bitte nie mit Excel speichern!). Excel zeigt dann keine Fieldquotes mit an, was das händische Suchen nach eben diesen ermöglicht. Wenn du dann weist in welcher Zeile die stehen, kannst du mit Notepad++ vorab die Zeichen entfernen oder ersetzen (eventuell auch escapen, da bin ich mir jetzt nicht sicher ob BULK INSERT damit umgehen kann).
b) In einem Projekt, bei dem ich automatisch importieren möchte, schreibe ich tatsächlich alles erst in eine Spalte. Im Anschluss prüfe ich die Anzahl der Fieldquotes und zerlege die Daten in SQL (mit XML Funktionen). Bombensicher, aber viel zu Fuß in SQL geschrieben.
c) Das beste ist die Sache an der Quelle anzugehen. Einige wenige Programme bieten beim Export die Möglichkeit, dieses Verhalten zu unterbinden.
Der Bulk Import ist nur brauchbar, solange die Quelldatei keine Konsistenzfehler hat. Man kann zwar theoretisch den DAtenimport-Assistenten nehmen... aber selbst der Datenimport-Assistent (flat file source) bricht ab, wenn in einer CSV Datei Inkonsistenzen enthält.
Wenn sowas vorkommt - kommt man nicht umhin, in Powershell oder VBA die Datei zeilenweise zu verarbeiten und einzeln einzufügen und fehlerhafte Zeilen zu überspringen. Oder sie zeilenweise zu kopieren und die fehlerhaften Zeilen zu ignorieren bzw eine Fehlerbehandlung dafür zu bauen. Das ist performancemäßig zwar ein GAU, aber anders gehts nicht. Schrottige Daten sollten garnicht erst entstehen...
Wenn sowas vorkommt - kommt man nicht umhin, in Powershell oder VBA die Datei zeilenweise zu verarbeiten und einzeln einzufügen und fehlerhafte Zeilen zu überspringen. Oder sie zeilenweise zu kopieren und die fehlerhaften Zeilen zu ignorieren bzw eine Fehlerbehandlung dafür zu bauen. Das ist performancemäßig zwar ein GAU, aber anders gehts nicht. Schrottige Daten sollten garnicht erst entstehen...
Die Frage ist eigentlich, unter welchen Umständen du BULK einsetzen willst. Kannst du die Quelle beeinflussen? Muss das ganze regelmäßig importiert werden? Drei Lösungen habe ich genannt, mehr scheint es nicht zu geben.