Altes Problem: Import einer TXT-Datei mit Kommata in den Spalten

Mephisto

Aktiver Benutzer
Beiträge
37
Hallo,

gerne würde ich eine Ortschaften-Tabelle mit 1,8 Mio internationalen Einträgen (19 Spalten) in einer SQLite verfügbar machen. Leider liegt mir die entsprechende Import-Datei nur im TXT-Format vor und einige Spalten enthalten auch Kommata,

Da SQLite nur CSV-Dateien importieren kann, stehe ich jetzt auf dem Schlauch, wie ich das hin bekommen könnte.

Grüße
Mephisto
 
Werbung:
... Sorry, noch vergessen: Diese DB gibt es auch in einer PostgreSQL-DB. Könnte man die PG-DB nicht vielleicht mit einem Tool in die SQLite-DB bekommen.
 
Ich benutze meistens den SQLite Manager für Firefox. Das empfinde ich als recht komfortabel bei der Arbeit an Webanwendungen. Der kann auch die Text-Dateien von geonames.org einlesen. Spaß ist aber bei so großen Datenmengen anders.

Ein Nachteil der Erweiterung ist, dass man auf die SQLite-Version angewiesen ist die im Browser integriert ist. Leider kann man da (unter Windows) die DLL nicht einfach tauschen. Daher sind die neuen CTE derzeit nicht möglich.

Auf der Konsole lässt sich der Import mit Hilfe von .separator und .import erledigen. Eine vorher angelegte Tabelle vorausgesetzt.

Code:
Enter SQL statements terminated with a ";"
sqlite> .separator "\t"
sqlite> .import de.txt geonames

Circa 180k Zeilen in wenigen Sekunden importiert. Problematisch ist unter Umständen nur, dass leere Strings auch als leere Strings importiert werden. Abhilfe schafft hier z.B.:
Code:
UPDATE geonames
   SET alternatenames = null
   WHERE alternatenames = '';

Nachtrag:
Fehlerhafte Datensätze werden beim Import einfach übersprungen. Leider gibt es auch keine Fehlermeldung oder Logeinträge.
 
Zuletzt bearbeitet:
PostgreSQL hat die Datei klaglos in ein paar Minuten eingelesen. Es gab irgendwann einfach nur ein "18????? Datensätze erfolgreich eingelesen".
Bei SQLite geht schon den ganzen Tag irgendwie gar nichts mit der Datei. SQLiteMan leiert mit 5GB Hauptspeicherbenutzung und permanenten Festplatten-Zugriffen (mehrere Stunden, dann abgebrochen...) auf der Datei herumrum, legt den Rechner lahm und hat wohl, nachdem ich die Spalten manuell eingerichtet habe, auch eine 1.3GB grosse DB-Datei generiert. Nur weiß ich nicht, ob da auch alles drin ist. Versuche gerade per Kommandozeile, die Datei einzulesen und dabei zu loggen. Ergebnis bis jetzt:
Code:
sqlite> .import /home/manni/Entwicklung/resources/Cities/allCountries.txt villages
CREATE TABLE test(...) failed: duplicate column name: Costa de Xurius
Es ist zum Mäusemelken !
 
Es ist alles drin ! Mit einem Python-Script konnte ich herausbekommen, wie viele DS drin sind. :)

Aber die "Management"-Tools für SQLite sind ja echt nicht das Wahre. SQLiteMan hat mich Stunden gekostet und mir nicht mal mitgeteilt, dass nun alles eingelesen ist und wie viel Zeilen gelesen wurden. Stattdessen hat das Tool den ganzen Rechner für Stunden nur blödsinnig blockiert. SQLiteStudio ist auch nicht sehr hilfreich ! :eek:

Hoffentlich macht SQLite selbst nicht so einen Ärger, wenn man da programmatisch dran geht ! :rolleyes:
 
Aber die "Management"-Tools für SQLite sind ja echt nicht das Wahre.
Sind sie leider auch nicht. Aber das spricht ja erst mal nicht grundsätzlich gegen die Datenbank.

Zumindest kennst du jetzt einen Weg wie man es nicht machen sollte. ;)

Hoffentlich macht SQLite selbst nicht so einen Ärger, wenn man da programmatisch dran geht ! :rolleyes:
Mit dem Teil hatte ich bisher noch nie Probleme.

Über die Konsole sollte es doch auch gehen:

Um aus PG ein Dump zu ziehen bietet sich pg_dump an.
Code:
pg_dump -a --inserts -t [Tabelle] [Datenbank]  | grep INSERT > [Datei.sql]
Jetzt muss das natürlich noch in die Datenbank.
Code:
sqlite> BEGIN TRANSACTION;
sqlite> .read geonames.sql
sqlite> END TRANSACTION;
sqlite> SELECT COUNT(*) FROM geonames;
8627980
Kontrolle PostgreSQL:
Code:
Ortsdatenbank=# SELECT count(*) FROM geonames;
  count
---------
8621225
Wo die 6755 zusätzlichen(!?) Datensätze herkommen werde ich später herausfinden...

Es ist alles drin ! Mit einem Python-Script konnte ich herausbekommen, wie viele DS drin sind. :)

Wie viele sind es den bei dir? :D

Code:
sqlite> .import /home/manni/Entwicklung/resources/Cities/allCountries.txt villages
CREATE TABLE test(...) failed: duplicate column name: Costa de Xurius
CREATE TABLE bei .import?
 
Sind sie leider auch nicht. Aber das spricht ja erst mal nicht grundsätzlich gegen die Datenbank.
Hab ich auch nicht gesagt, meinte nur die Tools !

Wie viele sind es den bei dir? :D

8621150 in PostgreSQL
8621150 in SQLite

8621225 bei Dir ? Wir sprechen hier wirklich von der gleichen Datenbasis ?

CREATE TABLE bei .import?
...meinte SQLite !!! Hatte nicht erst die Tabellen angelegt... :oops:

Aber jetzt ist alles fertig ! Alles in beiden DBs drin! Mit ein paar kleineren Tabellen gab es dann keinen Stress mehr, auch nicht aus der Shell.

Mit Python kommt man übrigens echt locker an die Daten dran (wie alles in Python mit einer etwas entspannteren Syntax, als in anderen Sprachen ;)).

Bei so grossen Imports gibt es aber auch bei Oracle manchmal Stress. So etwas gehörte manchmal in einem Job als SysInf und Admin zu meinen Aufgaben.

Gerade beam ich noch meine Semantic Scuttle - Favoriten-DB auf die PG um. Nur noch PHP umbeamen. Dann kann die MariaDB von der Platte ! :D

Danke Euch noch mal... :)
 
Wo die 6755 zusätzlichen(!?) Datensätze herkommen werde ich später herausfinden...
Datenbank neu angelegt. Daten noch einmal eingelesen.
Code:
sqlite> select count(*) from geonames;
8621225
Passt also. Vermutlich war meine Tabelle vorher nicht leer...

8621225 bei Dir ? Wir sprechen hier wirklich von der gleichen Datenbasis ?
Ich habe 75 Tupel mehr. Ich denke meine Text-Datei enthält einige Einträge die dir fehlen. Die Downloads werden ja regelmäßig aktualisiert.

Mit Python kommt man übrigens echt locker an die Daten dran (wie alles in Python mit einer etwas entspannteren Syntax, als in anderen Sprachen ;)).
Wenn ich etwas von der Datenbank wissen will frage ich möglichst direkt nach. Wenn ich eine Programmiersprache brauche um an die von mir gewünschten Infos zu kommen läuft für mich etwas schief.

Bei so grossen Imports gibt es aber auch bei Oracle manchmal Stress. So etwas gehörte manchmal in einem Job als SysInf und Admin zu meinen Aufgaben.
Ich war noch nie in der Verlegenheit solche großen Text-Dateien einzulesen. Geschweige den damit »umzuziehen«. Aber gerade bei solchen Datenmengen lernt man die Konsole zu schätzen.

Gerade beam ich noch meine Semantic Scuttle - Favoriten-DB auf die PG um. Nur noch PHP umbeamen. Dann kann die MariaDB von der Platte ! :D

Danke Euch noch mal... :)
Weg damit!

Kein Thema. Wenn noch Fragen auftauchen bekommen wir die bestimmt gelöst. :)
 
Ich habe 75 Tupel mehr. Ich denke meine Text-Datei enthält einige Einträge die dir fehlen. Die Downloads werden ja regelmäßig aktualisiert.
Habe ich vor 5 Tagen gezogen. Aber die genaue Datenbasis (Download-Link) hast Du bislang ja auch noch nicht verraten. Deshalb kann man dazu sowieso nichts sagen.

Wenn ich etwas von der Datenbank wissen will frage ich möglichst direkt nach. Wenn ich eine Programmiersprache brauche um an die von mir gewünschten Infos zu kommen läuft für mich etwas schief.
Wenn man das nicht sofort kann und permanent wegen irgendwelcher blödsinnigen Syntax-Fehler z.B. in Form von falschen, fehlenden oder zu vielen Zeichen (in Programmen und auch in der Kommandozeile) vor die Pumpe läuft und man es dann mit Hilfe einer Programmiersprache sofort hin bekommt ?

Wenn noch Fragen auftauchen bekommen wir die bestimmt gelöst. :)
Naja, gestern hätte es mir schon sehr geholfen. Aber ich habe es ja dann auch alleine hinbekommen. Hat zwar einen Tag gedauert, aber dafür habe ich wieder mal viel dabei gelernt, nicht nur über Datenbanken. Auch, dass es Programme gibt, die sich locker mal an die 100% Hauptspeicher, alle Prozessorkerne und fast alle Haupthreads nehmen und dabei mein Linux zwar fast nicht mehr bedienbar ist, aber dabei auch nicht abstürzt.
 
Habe ich vor 5 Tagen gezogen. Aber die genaue Datenbasis (Download-Link) hast Du bislang ja auch noch nicht verraten. Deshalb kann man dazu sowieso nichts sagen.
http://download.geonames.org/export/dump/allCountries.zip (2014-02-21 glaub ich)

Die Änderungen seit gestern:
http://download.geonames.org/export/dump/deletes-2014-02-22.txt
http://download.geonames.org/export/dump/modifications-2014-02-22.txt

Wenn man das nicht sofort kann und permanent wegen irgendwelcher blödsinnigen Syntax-Fehler z.B. in Form von falschen, fehlenden oder zu vielen Zeichen (in Programmen und auch in der Kommandozeile) vor die Pumpe läuft und man es dann mit Hilfe einer Programmiersprache sofort hin bekommt ?
Lerne dich in Geduld zu üben junger Padawan. Datenbanken können einem so viel Arbeit abnehmen, dass es sich lohnt hinter die Kulissen zu sehen. Für mich ist das eben ein Hinweis, dass ich mich damit näher beschäftigen muss. Unter Zeitdruck würde ich auch die schnelle Methode nehmen. Aber bei einem privaten non-profit Projekt?

Naja, gestern hätte es mir schon sehr geholfen.
Du hast doch immer recht schnell eine Antwort bekommen?

Ich benutze meistens den SQLite Manager für Firefox. Das empfinde ich als recht komfortabel bei der Arbeit an Webanwendungen. Der kann auch die Text-Dateien von geonames.org einlesen. Spaß ist aber bei so großen Datenmengen anders.
Meine Warnung war wohl etwas undeutlich. ;)
 
Die Änderungen seit gestern...
Wie oft gibt es denn da Änderungen ? Wenn das dauernd passiert, ist ja eine statische Datenbank irgendwann nutzlos ! Ich denke mal, das es aber ein eigenes Projekt wäre, regelmässige Updates zu programmieren.

Lerne dich in Geduld zu üben junger Padawan.
Komme vielleicht jung daher, bin ich aber nicht ! Vielleicht nur jung geblieben...

Datenbanken können einem so viel Arbeit abnehmen, dass es sich lohnt hinter die Kulissen zu sehen.
Das stelle ich ja auch gerade fest. Daten in einer DB lassen sich etwas einfacher finden als in Form von Files auf der Festplatte... Was mir jetzt nur noch fehlt, ist ein gutes Expertensystem, in dem ich Informationen jeglicher Art unterbringen kann.

... dass ich mich damit näher beschäftigen muss.
Das tu ich ja gerade. Bin werde dabei aber vielleicht etwas zu schnell ungeduldig, vor allem, wenn was nicht läuft.

Unter Zeitdruck würde ich auch die schnelle Methode nehmen. Aber bei einem privaten non-profit Projekt?
Nee, schnell, schnell, mag ich nicht ! Kaufe mein Essen ja auch nicht bei Mc Doof.

Du hast doch immer recht schnell eine Antwort bekommen?
Ja, das ist ja richtig ! Entschuldigt ! Aber gestern hatte ich echt die Nerven blank wegen diesem dämlichen SQLiteMan-Programm.

Meine Warnung war wohl etwas undeutlich. ;)
Das das bei der Mega-Datei Schwierigkeiten geben könnte, war mir schon klar. Bin auch eigentlich gar nicht davon ausgegangen, das das überhaupt klappt, vor allem nicht mit SQLite. Damit hat es dann aber auch gleich zweimal problemlos geklappt, einmal aus der Shell, aber weil da dann doch was noch nicht gestimmt hatte, dann nochmal mit dem SQLIteStudio. Das ist übrigens das Schöne an Manjaro: Man kann bei Allem erst mal alle verfügbaren Programme ausprobieren, bevor man sich für eins entscheidet. Und die laufen dann sogar problemlos.
 
Wie oft gibt es denn da Änderungen ? Wenn das dauernd passiert, ist ja eine statische Datenbank irgendwann nutzlos ! Ich denke mal, das es aber ein eigenes Projekt wäre, regelmässige Updates zu programmieren.
So wie ich das gesehen habe publizieren die die Daten täglich neu. Dazu gehört dann auch täglich ein Diff zum Vortag. Wie aktuell die Daten sein müssen kommt ja auch auf die Anwendung an. Die geografische Lage von z.B. Berlin ändert sich ja nicht so häufig.

Das stelle ich ja auch gerade fest. Daten in einer DB lassen sich etwas einfacher finden als in Form von Files auf der Festplatte...
Das meine ich nicht. Ich kann die Daten für z.B. Berlin auch aus dem Textfile ziehen. Grep, findstr und Regexp sind da sehr hilfreich. Aber ein Dateisystem erlaubt es eben nicht die Daten bequem zu vergleichen oder die Konsistenz sicherzustellen. Redundanzen lassen sich in einer Datenbank vermeiden. In einem Dateisystem halte ich das für unmöglich.

Das das bei der Mega-Datei Schwierigkeiten geben könnte, war mir schon klar. Bin auch eigentlich gar nicht davon ausgegangen, das das überhaupt klappt, vor allem nicht mit SQLite.
SQLite ist meiner Meinung nach ein solides Stück Software. Natürlich darf man nicht vergessen, dass das Designziel auf Portierbarkeit und geringer Größe liegt. Auf der Dev-Mailingliste findest du regelmäßig Diskussionen ob eine funktionierende Erweiterung übernommen werden soll. Die Entwickler betonen ja auch, dass SQLite als Ersatz für fopen() gedacht ist. Und dafür kann die kleine Datenbank verdammt viel.
 
... SQLite ist meiner Meinung nach ein solides Stück Software. Natürlich darf man nicht vergessen, dass das Designziel auf Portierbarkeit und geringer Größe liegt. Auf der Dev-Mailingliste findest du regelmäßig Diskussionen ob eine funktionierende Erweiterung übernommen werden soll. Die Entwickler betonen ja auch, dass SQLite als Ersatz für fopen() gedacht ist. Und dafür kann die kleine Datenbank verdammt viel.
Das ist mir auch schon aufgefallen, nicht, dass sie viel kann, sondern eher, dass sie sauber läuft. Was mir aber an der DB am meisten gefällt, ist der wohl pfiffige Umgang mit Datentypen.
 
Hallo zusammen!

Stehe vor dem gleichem Problem die folgende Geonames Text-Datei in SQLite zu importieren:
http://download.geonames.org/export/dump/allCountries.zip

Da ich absoluter Anfänger bin, habe ich noch nicht ganz verstanden, über welchen Weg das jetzt bei euch funktioniert hat:

allCountries.txt > SQLite (über den direkten Weg?)

allCountries.txt > PostgreSQL > SQLite (oder den Umweg über PostgreSQL?)

Es wäre wirklich eine große Hilfe, wenn mir einer von euch den einfachsten Weg noch einmal kurz beschreiben würde und welche Programme / Tools mit welchen Befehlen zum Ziel führen.

Danke und Grüße,
Datenbeisser
 
Werbung:
Hi datenbeisser.

Da ich absoluter Anfänger bin, habe ich noch nicht ganz verstanden, über welchen Weg das jetzt bei euch funktioniert hat:
Der direkte Import nach SQLite mit Hilfe von Bordmitteln ging bei mir zwar relativ schnell aber leider nicht problemlos. Die robustere Variante ist der SQLite-Manager für den Firefox. Da musst du aber mit ein paar Stunden rechnen.

Die für mich sicherste und stabilste Variante ist daher Import über PostgreSQL. Von dort ein Export in SQL und dieses dann in SQLite wieder einlesen.

Hinweise zum Import in PG findest du im Post von @akretschmer.

Das weiter Vorgehen habe ich hier in diesem Thread beschrieben.

Wenn du dabei auf Probleme stößt kannst du die hier gerne ansprechen.

Gruß
Hony
 
Zurück
Oben