Import von ASCII Daten

[H4r4ld]

Hallo zusammen,

ich steh vor folgendem Problem. Ich bekomme aus einer Applikation viele SQL Files, welche mir in MS SQL 2012 eine Datenbank mit ca. 800 Tabellen anlegen. Das funktioniert ja auch über sqlcmd ziemlich einfach und schön.
Nun bekomme ich aus der selben Applikation für jede dieser Tabellen die Daten in einer ASCII Datei, welche wie folgt aufgebaut ist:

wert1;wert2;wert3;...

Ich hab also keine Kopfzeile und ; als Trennzeichen. Leider ist es nicht möglich aus der Applikation die Daten auch als SQL File auszulesen. Nun hab ich mir schon die Finger wund gegooglt und bin natürlich auch auf bcp, bulk insert und openrowset gestoßen und mich schon etwas daran versucht. Soweit ich das nun verstehe muss ich aber bei jedem Import eine Formatfile angeben, was natürlich bei über 800 Tabellen nicht wirklich zweckmäßig ist, zum einen wegen der vielen Arbeit und zum anderen weil dieser Import häufig gemacht werden muss und es sein kann das Felder ergänzt werden, sich ändern oder wegfallen.
Also hier mal die Frage an euch Profis, gibt es einen schönen Weg solche Daten schnell und automatisiert zu importieren?

Wäre super wenn jemand nen Denkanstoß geben könnte.

Grüße

 

[ukulele]

Also die von dir bereits genannten Wege mit bcp etc. sind sicherlich üblich und nützlich. Leider bedarf es oder sollte zumindest immer eine klare Definition vorhanden sein welche Spalten in welcher Reihenfolge in welche Tabelle importiert werden. Wenn sich jetzt die Anzahl oder Reihenfolge der Spalten verändert wird es in jedem Fall sehr schwer. Auch das ganze ohne Einrichtung identischer Tabellen in der DB zu kopieren ist mir bisher nicht gelungen.

Hinzu kommt, das das Datenformat der Spalten in der Textdatei zunächst mal unbekannt ist.

Ich habe selbst einen Fall wo ich per OPENROWSET auf Access Files zugreife mit mehreren Tabellen und Spalten und mir wünsche ich müsste nicht für jede Spalte SQL Code schreiben aber ich habe keinen Weg gefunden aus Access Tabellenname, Spaltenname oder Spaltenformat mit TSQL auszulesen. (Wenn einer weiss wie möge er es mir bitte sagen!).

Wenn die Spaltenüberschriften in den Textdateien stünden könnte man sicherlich mit dynamischem SQL etwas machen. Auch ginge das bei dir, da du die Tabellen und Spalten ja bereits per CREATE Statement erstellt hast und sie, denke ich mal, in der selben Reihenfolge und mit der selben Namensgebung exportiert werden. Man könnte also ein Script basteln welches die Tabellennamen und Spalten aus der DB ausliest

SELECT    sysobjects.name AS table_name,
        syscolumns.name AS column_name,
        systypes.name AS datatype,
        syscolumns.[length] AS [length]
FROM    sysobjects
JOIN    syscolumns ON sysobjects.id = syscolumns.id
JOIN    systypes ON syscolumns.xtype = systypes.xtype
WHERE    sysobjects.xtype = 'U'
AND        sysobjects.name != 'index'
AND        systypes.name != 'sysname'

und nach passenden Dateien sucht um diese zu importieren. Ich weis aber aus eigener Erfahrung das das sehr sehr viel Arbeit wird

Eine Andere Möglichkeit bestünde darin es mit einer externen Software zu machen. Befasst habe ich mich damit noch nicht aber es gab von MS ein Tool names DTS das gewisse Import Möglichkeiten bietet und mitlerweile wohl weiterentwickelt wurde. http://de.wikipedia.org/wiki/Data_Transformation_Services Der ganze Import Vorgang kann hier automatisiert werden. Leider ist es kein Bestandteil von MSSQL Express.

 

[H4r4ld]

Erstmal danke für die Antwort.
Ich fange vielleicht erstmal ganz vorne an, vielleicht hab ich mich ja auch in etwas verrannt was man eleganter lösen könnte?
Ich hab eine große Oracle DB mit vielen NoSQL Fields, welche bedingt durch eine Migration in einen MS SQL Server verschoben werden soll. Es handelt sich wie schon gesagt um ca 800 Tabellen, mit bis zu 10 Mio Datensätzen.
Da mir kein Weg bekannt ist die NoSQL Fields auf andere Art und Weise auszulesen hab ich den Weg über den Export über die Applikation gewählt. Die gibt da wie gesagt leider nicht mehr her.
Ich hab nun von einem Kollegen den Tipp bekommen mir doch mal das Programm Flowheater anzuschauen, wobei ich da soweit ich das bisher überblicken kann auch die Mappings selbst machen muss, was natürlich wieder enormen Aufwand bedeuten würde.
Ich werde mir aber nun auch nochmal DTS zur Brust nehmen, vielleicht bekomme ich es ja auch damit hin.

Falls ich mich verrannt hab und es besser Wege für die Migration gibt, immer her damit.

 

[akretschmer]

Ich fange vielleicht erstmal ganz vorne an, vielleicht hab ich mich ja auch in etwas verrannt was man eleganter lösen könnte?
Ich hab eine große Oracle DB mit vielen NoSQL Fields,

Wie sieht denn so ein NoSQL - Feld aus?

*neugierig*

 

[H4r4ld]

Wie sieht denn so ein NoSQL - Feld aus?

*neugierig*

Was genau meinst du mit aussehen?
Wenn man es mal ganz einfach betrachtet sind das viele "normale" Datenbankfelder die in einem Feld zusammengefasst werden und über einen Offset vom Programm selbst rausgerechnet und wieder getrennt werden. In SQL kommt dann nur Murks raus, ich geh aber davon aus das die Offsetaufsplittung innerhalb der Applikation hinterlegt ist und bin deswegen auch den Weg über die Applikation selbst gegangen. Wenn das nicht so ist wäre natürlich auch ein anderer Weg denkbar...?

 

[akretschmer]

Was genau meinst du mit aussehen?
Wenn man es mal ganz einfach betrachtet sind das viele "normale" Datenbankfelder die in einem Feld zusammengefasst werden und über einen Offset vom Programm selbst rausgerechnet und wieder getrennt werden. In SQL kommt dann nur Murks raus, ich geh aber davon aus das die Offsetaufsplittung innerhalb der Applikation hinterlegt ist und bin deswegen auch den Weg über die Applikation selbst gegangen. Wenn das nicht so ist wäre natürlich auch ein anderer Weg denkbar...?

Ahh, okay. Ich konnt mit dem Begriff nix anfangen. Ist das als normaler Text oder so gespeichert, oder gab es da in Oraggle einen speziellen (binären?) Datentyp?

Ich kenne (unter PostgreSQL) HSTORE, ist ein Key-Value-Store. Das ist ja auch sowas wie NoSQL. Aber die einzelnen Felder per Offset zu nutzen erscheint mir - ähm, als ob man Normalisierung nicht vollständig verstanden hat...

 

[ukulele]

Unter den Umständen würde ich versuchen direkt aus Oracle nach MSSQL zu migrieren. Aber ich habe weder Oracle Erfarung noch Erfahrung mit solchen NoSQL Feldern. Für die Migration gibt es sicher einige Tools insbesondere von Drittanbietern, die guten vermutlich kostenpflichtig. http://stackoverflow.com/questions/6874027/best-tool-to-move-from-oracle-to-ms-sql-server

Ich denke mal bei allen Tools muss man einen gewissen mapping-Aufwand betreiben aber nichts ist schlimmer als alle Spaltennamen in TSQL-Statements packen zu müssen. Insofern ist deine Ausgangssituation mit Textdateien ohne Spaltenbeschriftung ziemlich mies

 

[H4r4ld]

Entschuldigt die Verspätung... Schulung und so...
Also, NoSQL Felder sind meins Wissens nach eigentlich Historisch bedingt und wurden in der Entstehungszeit von Datenbanken eingesetzt um Performancevorteile zu haben. Wenn ich NoSQL Felder versuche über z.B. nen SQL Developer auszulesen, bekomme ich in diesen Feldern nur Chinesisch angezeigt, also keine verwertbare Daten, daher gehe ich stark davon aus, dass dies irgendein spezieller Datentyp ist.

@ukulele
Genau diese Migration ist ja unser Ziel. Wir wollen die komplette Datenbank in MSSQL darstellen. Leider sind die Offsetdefinitionen innerhalb der Applikation festgelegt, so das man mit Tools von Drittanbietern diese nicht auslesen kann, da hier die Definitionen nicht bekannt sind.
Im Moment prüfen wir die Daten aus der Applikation als SQL Script zu exportieren, mal sehen ob es da nicht doch irgendeinen Weg gibt.

 

[H4r4ld]

So, mal nen kleines Update und eine Frage zusätzlich...

Wir haben es nun mit viel hin und her hinbekommen den Export per SQL File zu machen. Das funktioniert auch soweit ganz gut.
Problem das wir nun haben ist, dass wir im Oracle Datumsfelder mit 00.00.0000 00:00:00 haben, was im SQL so ja nicht abgebildet werden kann, da der Datumsbereich 01.01.0001 00:00:00 bei DateTime2 ist.
Gibt es eine Möglichkeit einen Inputwert zu überprüfen, also wenn Datum = 00.00.0000 mach daraus 01.01.0001 und mach erst dann den Insert (alles innerhalb einer SQL File)?
Oder geht nur der Weg über ein Stringfeld?

 

[ukulele]

Also wenn ich das richtig verstehe habt ihr als SQL-File eine INSERT Anweisung füf jeden Datensatz. Der INSERT scheitert aber weil ihr einen String habt (Oracle Datumsformat) der von MSSQL nicht interpretiert werden kann und die Spalte bereits ein DATETIME Format hat?

Kannst du mal die Fehlermeldung posten?

Denn ich habe mal '01.01.2013 00:00:00' im Insert angegeben und das hat er von sich aus erkannt und geschluckt.

 

[H4r4ld]

Also, wir haben nen SQL File mit einem INSERT über 1000 Zeilen (Maximum), da steht in jedem Datensatz das Createdate, was auch problemlos per CONVERT(DATETIME2, '161298085305', 6) importiert wird. Nun haben wir aber neben dem Createdate auch ein Modifydate, welches, wenn der betreffende Datensatz noch nie verändert wurde, in Oracle mit 00000000000000 angegeben ist. Wenn ich nun im INSERT versuch per (CONVERT(DATETIME2, '00000000000000, 6) diesen zu konvertieren läuft er naturgemäß auf einen Fehler. DateTime2 in SQL hat ja einen Datenbereich von 01.01.0001 00:00:00 bis 31.12.9999 23:59:59 (die Nachkommastellen mal außen vor), was natürlich 00.00.0000 00:00:00 nicht mit einfasst.

Da wir leider in der Applikation nicht die Möglichkeit haben einen String zu zerlegen, bleibt die Frage ob ich das per SQL bereinigen kann oder ob nur die Möglichkeit bleibt das ganze in einem Varchar Datentyp abzulegen?

 

[ukulele]

Hm, mein Ziel wäre es das durch NULL zu ersetzen. Wenn ihr Funktionen mit in das SQL Statement bekommt wie z.B. (CONVERT(DATETIME2, '00000000000000, 6) dann wäre es sicher auch möglich eine CASE Anweisung in die Zeile zu bekommen. In etwa so: (CASE WHEN '00000000000000' = '00000000000000' THEN NULL ELSE (CONVERT(DATETIME2, '00000000000000',6)) END)