csv-Import von Daten per SQL über BASE

Deutsch
,
#1

Den csv-Import in Base über CALC kenne ich, jedoch ist dieses Verfahren nicht automatisierbar. Meine ersten zaghaften Versuch die Daten über SQL zu importieren schlugen fehl. Kann BASE den csv-Import mit SQL-Befehle überhaupt leisten? Die Beschreibung im Handbuch Tabelle 7.5 ist da wenig aufschlußreich.
Werner

Edit 1:
Bei der Datenbank handelt es sich um eine MariaDB auf OS Windows, bzw. auf der Synology.
Die Datenbank in Windows kann über LO mit JDBC geladen werden und arbeitet einwandfrei.
Die csv-Daten im ISO-8859-3-Format werden mir zugesendet und umfassen 30 Felder bei 1200 Zeilen.

Edit2:
Es sollte nicht Handbuch Tabelle 7.5 heissen sondern “Kapitel 3 Tabellen” heissen.
Fehlgriff meinerseits.

Edit 3: folgende SQL-Befehle gebe ich ein:
load data infile ‘d:\Ordner\filename.csv’
into table importliste
fields terminated by ‘;’
Lines starting by ‘"’
terminated by ‘\n’
ignore 1 lines
;

Windows 10
Version: 7.3.6.2 (x64) / LibreOffice Community
Build ID: c28ca90fd6e1a19e189fc16c05f8f8924961e12e
CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded

#2

Das geht nur über Makros.
Am einfachsten ist wohl die Variante, eine Datenbankdatei als Texttabellendatei zu erstellen und in LibreOffice zu registrieren. Dann kannst Du per Makro die Ursprungstabelle auslesen und in die gewünschte Tabelle Deiner internen Datenbank einfügen.

Wass Du mit

meinst ist mir nicht klar.

Im Base Handbuch 7.5 steht einmal der Zugriff direkt auf Texttabellen mit der internen HSQLDB (S. 94ff.) und der Zugriff auf Texttabelle einer eigenständigen Datenbankdatei (S. 97ff.)
Der Datenimport per Makro steht dann auf S. 550.

#3
  1. csv ist kein Dateiformat. Es ist reine in Text gegossene Konvention. Ohne jeden Hinweis auf den Inhalt der Textdateien ist es unmöglich, irgendwelche Lösungen vorzuschlagen.
  2. Von welcher Art Datenbank reden wir überhaupt? HSQL, Firebird, MySQL, SQLite, irgendwas von Microsoft?


Ich importiere csv von verschiedenen Bankkonten seit Jahren ohne Calc in eine lokale HSQL-Datenbank (version 2.4, nicht eingebettet).
Die Dateien verwenden deutsche Datumsformate und Dezimalzahlen, die mti Anführungszeichen maskiert sind. Eine typische Zeile ist:

26.10.2022;26.10.2022;"Überweisung Online; ; XXXXX; 12345 BIC: WLAHDE44; IBAN: DE76987654321 Kunden-Referenz: 00001 XXXXX Info: 22120099";EUR;;"160,33";"6.468,15"

Du siehst, es ist gar nicht so schwer, konkrete Angaben zum Problem zu machen.

#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

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