csv-Import von Daten per SQL über BASE

Deutsch
,
#4

@Villeroy Schön, dass Du einen funktionierenden csv-Import hast. Lass mich an Deinem Wissen teilhaben.
Werner

#5

Vermutlich ist schon der Backslash in der Angabe falsch. Siehe dazu

… vor allem in den Beispielen, die weiter unten von Problemen berichten.

#6

[HSQL, Base, Basic] Macro Driven CSV Import

Alles, wirklich alles, hängt an den konkreten csv-Daten und an der tatsächlichen Datenbank, die verwendet wird. Nein, Base ist keine Datenbank. Base ist ein Werkzeug, um Datenbanken im Kontext von LibreOffice zu verwenden.
– EDIT –
Das ist die Funktion, die im zweiten Beispiel vom 6. Januar alles zusammenführt.

Function ImportCSV(sURL$, sDBPath$, sTextFile$, sTextTable$, sView$,  sDataTable$) As Long
  • Rückgabewert ist die Anzahl der angefügten Zeilen.
  • sURL is die URL der zu importierenden CSV-Datei. Im Beispiel wird die URL per Datei-Öffnen-Dialog ermittelt
  • sDBPath ist die Pfad-URL zur Datenbank (bei eingebetteter Datenbank der Dokumentenpfad, bei externer HSQL das Datenbankverzeichnis)
  • sTextFile ist der Name der Textdatei, mit der die HSQL Text-Tabelle verknüpft ist (“import.csv” im Beispiel)
  • sTextTable ist der Name dieser HSQL Text-Tabelle. Die Text-Tabelle importiert alle nicht SQL-kompatiblen Werte als Text (VARCHAR) (“import_csv” im Beispiel)
  • sView ist der Name der Ansicht, die die Text-Tabelle so umformt, dass das Ergebnis mit der Zieltabelle kompatibel ist (“import_View” im Beispiel).
  • sDataTable ist der Name der Zieltabelle (“Data” im Beispiel)

Die Funktion trennt die Textdatei von der Text-Tabelle, kopiert die Textdatei unter dem richtigen Namen in das Datenbankverzeichnis, stellt die Verbindung der Text-Tabelle zur neuen Textdatei wieder her und macht dann INSERT INTO “Data” (SELECT * FROM “ImportView”)

Die Ansicht “ImportView” ist eine SELECT-Abfrage, welche die zu importierenden Spalten der verknüpften Texttabelle in die richtige Reihenfolge bringt und dabei inkompatible Strings (z.B. Kommadezimalzahlen und dt Datumswerte) in kompatible Spaltentypen umwandelt. Optional kann man auch noch Duplikate herausfiltern, die bereits in der Zieltabelle vorhanden sind.
Da man in Base einmal erstellte Ansichten nicht mehr bearbeiten kann, habe ich die Ansicht noch als Abfrage “ViewSource” hinterlegt. Will man die Ansicht ändern, löscht man sie erstmal, ändert dann die Abfrage und schließlich Rechts-click>Abfrage als Ansicht … und gibt den Namen der Ansicht ein, die dann bei den Tabellen erscheint. HSQL weiß nichts über Base-Abfragen, aber es kann Ansichten sehen weil die in der Datenbank selbst gespeichert sind und nicht im Datenbank-Dokument.

#8

@RobertG und @Villeroy euch beiden meinen Dank, aber beide Beiträge bringen mich leider nicht weiter. Mein Anliegen ist der direkte csv-Import in die MariaDB, wobei ich Base nur als Tool (ja @Villeroy das ist mir auch bekannt und wird auch als solches verwendet) zum Import einsetzen möchte. Die Datenbank und das csv-Format sind vorgegeben.
@Villeroy Deine Hinweise auf den Beitrag im openoffice-Forum sind mir leider etwas zu unverständlich, weil ich noch nicht im “großen Orchester” spielen und ich mit meiner “Trillerpfeife” etwa dumm dastehe.
Ich werde jetzt auf dem Vorschlag von @RobertG meine Versuche weiterbetreiben.

#7

Damit wissen wir schonmal welche Software wir hier überhaupt diskutieren. Wie kann man so eine Information weglassen?

#9

Habe ich doch am 21.02 um 09:25 Uhr nachgetragen. Sei doch gelassen!

#10

Und Base zeigt jetzt eine Tabelle namens ‘importliste’ an mit dem Inhalt der aktuellen Textdatei d:\Ordner\filename.csv?
Die Daten dieser importliste sollen in eine Zieltabelle übertragen werden?
Ich vermute mal, dass meine Basic-Funktion das auch mit MariaDB leisten kann.
Was passiert wenn man die Textdatei im laufenden Betrieb auswechselt? Zeigt ‘importliste’ sofort die neuen Daten an? Falls nicht (was ich mal vermute), dann müsstest Du einen Befehl ermitteln, der die Verbindung zw. ‘importliste’ und ‘d:\Ordner\filename.csv’ trennt und nach dem Auswechseln der Datei wiederherstellt. Bei meinem HSQL-Beispiel ist das SET “import_csv” SOURCE OFF/ON.

Das müsste im Basic code entsprechend geändert werden. Hart kodierte Windows Pfade kann man wie folgt verwenden: Statt “file:///C:/Pfad/Datei.abc” schreibe convertToURL(“C:\Pfad\Datei.abc”)

MariaDB kennt Views. Erstelle eine Base-Abfrage mit der korrekten Anzahl an Spalten in der richtigen Reihenfolge und konvertiere in die richtigen Spaltentypen der Zieltabelle. Setze auto-Werte oder Leerstellen auf NULL. Teste diese Abfrage gründlich im “direkten” SQL-Modus (menu:Edit>SQL direkt) damit das in MariaDB-Syntax richtig fuktioniert. Speichere sie als Ansicht (View).

Bevor ich diesen Makro-Kram verwendete, habe ich jahrelang csv wie folgt importiert. Office schließen, Datei herunterladen und im Datenbankverzeichnis ablegen, Datenbankdokument öffnen und das View-Icon auf das Icon der Zieltabelle kopieren. Ich glaube aber, das funktioniert so nicht bei einer Server-Verbinudung weil die Datenbank beim Beenden der Office-Verbindung einfach weiterläuft und die ausgewechselte Textdatei nicht einbindet.

#11

Das mit dem Rüberziehen müsste funktionieren, weil nach dem Code im Ursprungspost ja die Tabelle “importliste” betreits besteht.

Ich gehe davon aus, dass lediglich an dem Code irgendetwas faul ist. Da würde ich auf

load data infile 'D:/Ordner/filename.csv'
into table `importliste`
fields terminated by ';'
Lines starting by '"'
terminated by '\n'
ignore 1 lines
;

Vielleicht wäre hier eine Rückmeldung über das, was das an Fehler in Extras → SQL auswirft, sinnvoll.

#12

Nachdem ich mich zwischenzeitlich mit ganz anderen Aufgaben auseinandersetzen musste, jetzt die Ergebnisse meiner Bemühungen.
LOAD DATA LOCAL INFILE ‘D://ordner//file.csv’
INTO TABLE tabelle.liste
FIELDS
TERMINATED BY ‘"’
ENCLOSED BY ‘;’
LINES
TERMINATED BY ‘\r\n’
IGNORE 1 LINES
;
Also genau das was @RobertG im vorherigen Post aufgeführt hat.
Die gute Nachricht ist: der Import hat geklappt, die schlechte: leider sind noch einige “Ungereimheiten” in der Tabelle. Die werde ich jetzt mit entsprechenden SQL-Befehlen eliminieren. Dann gibt es noch ein Update.
An die obigen User: mein herzliches Dankeschön. Es ist ein beruhigendes Gefühl, das es in der Community selbstlose Helfer gibt.

#13

@Werner_RJP : Müssen da tatsächlich doppelte Frontslashes rein? Ich kann das hier ja für Windows nicht testen …

#14

@RobertG Nein, da müssen keine doppelten Frontslashes rein, hatte ich im Rahmen meiner Versuche übernommen und vergessen zu entfernen.
Aktuelle habe ich mehrere kleinere Probleme:

  1. es wird “nur” jede zweite Zeile importiert,
  2. in den DB-Zellen werden die Inhalte mit “Anführungszeichen” und ohne angezeigt, das gilt sowohl bei Ziffern als auch bei Texte,
  3. der Zeichensatz ist noch falsch und meine Änderungsversuche waren bisher erfolglos.

Ansonsten funktioniert der “reine” Import bis auf die obigen Probleme.

Werner

#15

Ich habe das einmal mit einer kleinen Testdatei probiert, die ich hier über Calc erstellt habe:

LOAD DATA LOCAL INFILE '/home/robby/export.csv'
INTO TABEL `test`.`csvimport`
CHARACTER SET UTF8
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
IGNORE 1 LINES

Zuerst einmal von den ganzen Code so viel wie möglich weg gelassen. Das Einzige, was sicher neben der Datei und dem Ziel in der MariaDB rein muss, ist FIELDS TERMINATED BY.
Ohne IGNORE 1 LINES wird die erste Zeile mit den Überschriften versucht auch zu importieren.
Lasse ich OPTIONALLY ENCLOSED BY weg, dann habe ich bei Texten ein Problem, die ein Komma enthalten.
Lasse ich den CHARACTER SET weg, dann gibt es mit Umlauten Probleme.
Datumsangaben müssen nach dem Format YYYY-MM-DD erfolgen.
Dezimaltrenner muss der . (Punkt) sein.

Ich habe keine Endung für die Zeile mit drin. Vielleicht verursacht das bei Dir den Ausfall jeder 2. Zeile.

#16

Derzeit kann ich nur spontan hier berichten, bzw. reinschauen, hab ein bischen viel “um die Ohren”.
Das bisherige beste Ergebnis erhalte ich mit folgender sql-sequenz:

LOAD DATA LOCAL INFILE 'D:/Ordner/file.csv'
    INTO TABLE importliste
    CHARACTER SET utf8
    FIELDS 
        OPTIONALLY ENCLOSED BY '"'
        TERMINATED BY ';'
    LINES
        TERMINATED BY '\r'
    IGNORE 1 LINES
;

Der Import funktioniert einwandfrei, lediglich beim CHARACTER SET habe ich noch keine Lösung gefunden aber auch dass die Zahlen in der ersten Tabellenspalte in Anführungszeichen geladen werden sind ungelöst.
Der Import wird von einem Windows-Rechner durchgeführt, die MariaDB läuft auch auf einem Windows-System. Die csv-Datei hat das Format <“zahl1”;“zahl2”,“text1”;“text2”>.

Werner

#17

Nach dem, was Du noch schreibst, kannst Du Dir OPTIONALLY sparen. Bei Dir scheint ja alles in doppelte Anführungszeichen gesetzt zu sein.
Handelt es sich bei den Zahlen um Zahlen mit Nachkommastellen, so benötigst Du als Trenner einen Punkt. Wenn bei Dir das System stattdessen Zahlen in Anführungszeichen liefert, dann ist die Tabelle falsch definiert, die die Daten aufnehmen soll. Anführungszeichen können nicht in einem Zahlenfeld stehen.

#18

Falsch definiert? Upps.
Ich erhalten die csv-Dateien so wie in meinem letzten Posting, einen Einfluss auf das Datenformat habe ich nicht, ist extern voreingestellt.
Alle Daten im csv-File sind in Anführungszeichen, auch die Zahlen und die Nachkommastellen durch Komma getrennt.
Heisst das im Umkehrschluss, dass ich die DB-Tabellen anders definieren muss, mit dem Zeichensatz cp1250 von Windows? Ich bin davon ausgegangen, dass dies bereits bei der Installation berücksichtigt wurde? Gibt es eine Prüfsequenz?

Werner

#19

Entscheidend ist doch, was Du nachher mit den Daten machen möchtest. Nur: Eine Zahl mit Nachkommastellen erhältst Du über den SQL-Befehl nur, wenn es sich um eine Zahl mit einem Punkt als Trenner handelt. Die Zahl wird dann auch nicht in doppelte Anführungszeichen gesetzt. Das ist nur bei Text übrig - und eigentlich nur dann, wenn das Trennzeichen Deines Textes darin vorkommt.

Also: Brauchst Du die zahlen als Zahlen, damit Du damit rechnen kannst? Dann lies die *.csv-Tabelle in Calc ein, ändere die Spalten für die Zahlen auf Englisch(USA) für den Dezimaltrenner und speichere das Ganze wieder als *.csv ab. Wenn Du das über Calc machst, dann siehst Du auch, mit welcher Codierung das Ganze abgespeichert wird.

#20

Das Ziel dieses Projektes soll der Rechnungschreibung, zur Kontrolle des Zahlungseinganges, der Mahnung der Zahlungssäumigen und der Vorbereitung von Teilnehmerausweise dienen. Bisher wurde es in verschiedenen Calc-Tabellen und Writer-Vorlagen nach vorgegebenen Regeln mit hohem Zeitaufwand vorgenommen.
Das liest sich recht banal, ist aber einmal im Jahr innerhalb recht kurzer Zeit durchzuführen.
Nach Ende dieser Frist haben die Daten keine weitere Bedeutung, denn die Verwaltung wird von einem externen Dienstleister durchgeführt. Der gesamte Vorgang wird so in keinem anderen Verein gemacht, sodass die Kosten einer Zusatzsoftware recht hoch sind und auch nicht den gesamten Vorgang abdecken. (Hat was mit der Satzung des Vereins zu tun!)
Zur ersten Spalte: das die Zahlen in Anführungsstriche sind, ist jetzt bedauerlich, aber da sie eher einen statischen Wert haben und frei von math. Operation sind, werde ich mit dem Textformat leben.

Werner

#21

Ich habe viel zu wenig Erfahrung mit MySQL/MariaDB aber die Dokumentation scheint recht ordentlich zu sein und es scheint viele Optionen und Schlüsselwörter ( SET ) zu geben, um Text in eine Tabelle zu quetschen. Das wäre Thema in einem MySQL-Forum und ist dort sicher schon tausendfach ausdiskutiert.
Wie ich schon am Anfang sagte, hängt wirklich alles an der verwendeten Datenbank-Engine und an den kleinsten Details der CSV-Daten. Base bietet hier gar nichts an weil der Umgeng mit csv anscheinend genauso wenig standardisiert ist wie csv selbst.

Wenn alle Stricke reißen, dann erstelle doch - analog zu meinem HSQL-Beispiel - eine separate Import-Tabelle, in die Du die Daten so semiperfekt in VARCHAR-Spalten einliest wie Du es halt hinbekommst.
Danach lässt Du mein Makro sowas machen wie:

DELETE FROM `import_csv`
LOAD DATA IN FILE ... INTO `import_csv`
INSERT INTO `eigentliche tabelle` (SELECT * FROM `my_view`)

Die Zeichenkodierung sollte aber beim Einlesen richtig gesetzt werden. In Deutschland scheint Windows 1252 (Latin 1) weit verbreitet zu sein. Calc kann Dir helfen, den richtigen Zeichensatz zu ermitteln.
WIndows 1252 wäre dann “latin1” nach https://mariadb.com/kb/en/supported-character-sets-and-collations/
Auch hier läge die Herausforderung in dem SQL-Code in my_view zwecks Umwandlung von deutschem Text mit Anführungszeichen, Komma-Dezimalzahlen und dt. Datumsformaten in valide SQL Datentypen.

#22

Ich fang mal hinten beim Thema Prüfsequenz an: Bei csv gibt es nichts. Man arbeitet ohne Sicherung und muss selbst herausfinden (Analysieren, Dokumentation lesen, prüfen ob dokumentation stimmt) welche Details gelten. Du kannst natürlich z.B. ein Mitglied Äoü Öẞ Üäö in Deine Datenbank aufnehmen und kontrollieren, ob es nach dem Import vorkommt. In Deutschland sind damit die meisten Problemfälle abgedeckt und man bekommt hoffentlich einen Hinweis, wenn Probleme mit Ångström, El Niño etc. zu erwarten sind…
.
Mit Deinem CHARACTER SET utf8 gibst Du an, dass die csv (Deiner Meinung nach) utf8-kodiert ist. Davon kann bei der Installation nichts berücksichtigt werden, da das eben oft an dem System liegt, das die csv erzeugt. Alte DOS-Programme liefern gelegentlich immer noch IBM-437 und ich habe es auch schon erlebt, dass Eingabeaufforderung jnd Windows-Programme auf demselben Rechner mit unterschiedlichen Codepages arbeiten.
.
Konsolenbasierte Programme übernehmen normalerweise die Einstellung des Terminals von der Sie gestartet werden (zumindestens, wenn fer Programmierer das berücksichtigt hat) und man kann auch den Zeichensatz in der Eingabeaufforderung ändern, was aber natürlich keine csv-Datei ändert.
.
Was Dein konkretes Problem angeht, würde ich versuchen erstmal in eine Import-Tabelle einzulesen, in der Du z.B. die erste Stalte als Text definierst. Dann eine Konvertierungs-Abfrage bauen, die die notwendigen Konvertierungen in ein Zahlenformat vornimmt. Diese Abfrage als View/Ansicht speichern (hat @Villeroy oben auch schon erwähnt) und danach nur noch diese Ansicht als Quelle verwenden.

#23

Ich bin mir schon bekannt, dass das csv-Format beliebig, aber dennoch nach bestimmten Regeln (RFC 4180 siehe Wikipedia) aufgebaut ist. Das wiederum bedeutet, das vieles möglich ist, sofern bestimmte Konventionen eingehalten werden, mit der Folge, dass das Importformat z.T. erst nach einigen Versuchen bestimmt und angewendet werden kann. Wo mir das Wissen jedoch fehlt, war, wie stelle ich dies beim Datenimport ein. In Manuals habe ich dann (nach langem Suchen) die Hinweise erhalten:
a. der Zeichensatz kann im ini-file vorgeben werden
im my.ini habe ich 4 Zeilen ergänzt:

[mysqld]
...
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
[client]
...
default-character-set=utf8mb4

b. ebenso beim Daten-Import
Nach Load data … folgt die Zeile

CHARACTER SET CP1250

Und damit funktionierte der csv-Import wie vorgesehen.
Nochmal zur Information: csv-Export erfolgt aus einem externen fremdgewartetem System
und der Import in einem Windows 10-System.
Die vollständige Routine sieht jetzt folgendermassen aus:

LOAD DATA LOCAL INFILE 'D:/Ordner/filename.csv'
    INTO TABLE import
/** Windows Zeichensatz **/
    CHARACTER SET CP1250
    FIELDS 
        ENCLOSED BY '"'
        TERMINATED BY ';'
    LINES
        TERMINATED BY '\r'
    IGNORE 1 LINES
;

Bis auf den “Fehler” der ersten Spalte wurden alle Daten im erwartetem Sinne eingetragen.
Da eine weitere Verarbeitung der ersten Spalte nicht vorgesehen ist, ist diese Lösung für mich akzeptabel.
Meinen herzlichen Dank allen hier im Thread mit Informationen beigetragenen Usern.

Werner

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.