Vorhandene CSV-Datei in eine MySQL-Datenbank einfügen

3. April 2022 elsenorweb

Ich habe mir am Freitag eine CSV-Datei von der Inselregierung „El Hierro“ heruntergeladen, also von der Kanareninsel El Hierro, die aber die gesamten Kanaren betrifft, also die CSV-Datei. Sie hat die ganzen Arbeitsämter der Kanaren aufgelistet. Es gibt sie auch als JSON-Datei, die ich ziemlich schnell in meine MongoDB-Datenbank einpflegen konnte. Aber MySQL war hartnäckig nicht den gewünschten Dienst auszuführen, oder ich habe es mir wahrscheinlich zu einfach vorgestellt.

Als erstes habe ich mir natürlich die CSV-Datei in LibreOffice bearbeitet, denn die Angaben waren in vielen Spalten verrutscht.

Mit meinen beiden Python-Scripts zur Erstellung der MySQL Datenbank und der Tabelle als Grundgerüst habe ich mich nicht schwer gemacht. Das ging alles sehr flott.

import mysql.connector

mydb = mysql.connector.connect(
host=“localhost“,
user=“sven“,
password=“oiuz5″,
)
mycursor = mydb.cursor()

mycursor.execute(„CREATE DATABASE Canarias“)

und

import mysql.connector

mydb = mysql.connector.connect(
host=“localhost“,
user=“sven“,
password=“jjjjz“,
database=“Canarias“
)
mycursor = mydb.cursor()

mycursor.execute(„CREATE TABLE oficinas_empleo (isla_id INT AUTO_INCREMENT PRIMARY KEY, isla INT, islaTxt VARCHAR(255), provincia INT, provinciaTxt VARCHAR(255), municipo INT, municipioTxt VARCHAR(255), nombre VARCHAR(255), oficina VARCHAR(255), direccion VARCHAR(255), municipoPostal INT, ciudad VARCHAR(255), telefono INT, fax INT, theGeomX VARCHAR(255), theGeomY VARCHAR(255))“)

Es ist natürlich hier nicht das verwendete Passwort genannt.

Das Schwierige war die Datei von LibreOffice nach MySQL zu transportieren, denn abschreiben wollte ich sie nicht. Klar von der .ods-Datei musste ich es in eine CSV-Datei speichern.

Es ist immer ratsam in 99% der Fälle eine auch für Python Pandas heruntergeladene CSV-Datei einmal in eine Tabellenkalkulation zwischenzuspeichern, denn auch Python Pandas (data = pd.read_csv(‚daten.csv‘) akzeptiert irgendwie nicht die erstellten im Netz herunterladbaren CSV-Dateien.

In dieser CSV-Datei gab es auch nur Zahlen und ich habe die MySQL-Tabelle so erstellt, dass besonders Telefonummern auch als INT genommen werden sollen.

Für die Kopieraktion muss die CSV mit root-Rechten einmal in das Verzeichnis /var/lib/mysql/Datenbankname rein kopiert werden.

Mit

mysql> LOAD DATA INFILE ‚el_Hierro_oficinas-empleo-22.csv‘ INTO TABLE oficinas_empleo;
ERROR 1265 (01000): Data truncated for column ‚isla_id‘ at row 1

wollte ich die Daten einfügen. Unten besagte Fehlermeldung sagte mir aus der Index wäre zu groß. Dabei besteht der Index nur aus einer Zahl und wurde mit INT definiert. So habe ich die Tabelle definiert.

Mein nächster Versuch war:

mysql> LOAD DATA INFILE ‚el_Hierro_oficinas-empleo-22.csv‘ INTO TABLE oficinas_empleo IGNORE 1 ROWS;
ERROR 1265 (01000): Data truncated for column ‚isla_id‘ at row 1

Also habe ich den Index vorerst gelöscht.

mysql> ALTER TABLE oficinas_empleo DROP COLUMN isla_id;
Query OK, 0 rows affected (1,91 sec)
Records: 0 Duplicates: 0 Warnings: 0

Danach versuchte ich es weiter:

mysql> LOAD DATA INFILE ‚el_Hierro_oficinas-empleo-22.csv‘ INTO TABLE oficinas_empleo IGNORE 1 ROWS;
ERROR 1265 (01000): Data truncated for column ‚isla‘ at row 1

Also kam mir der Verdacht, dass alle INT Felder nicht funktionieren würden. Ich habe dann alle INT nach VARCHAR umgewandelt.

mysql> ALTER TABLE oficinas_empleo MODIFY isla VARCHAR(255), MODIFY provincia VARCHAR(255),MODIFY municipo VARCHAR(255), MODIFY municipoPostal VARCHAR(255), MODIFY telefono VARCHAR(255), MODIFY fax VARCHAR(255);
Query OK, 0 rows affected (1,82 sec)
Records: 0 Duplicates: 0 Warnings: 0

Wieder die Tabelle überprüft:

Ok, soweit so gut.

Danach wollte ich die Daten einfügen:

mysql> LOAD DATA INFILE ‚el_Hierro_oficinas-empleo-22.csv‘ INTO TABLE oficinas_empleo IGNORE 1 ROWS;
ERROR 1261 (01000): Row 1 doesn’t contain data for all columns

Mir schwante, dass ich wohl noch was einfügen musste, in dem Befehl.

mysql> LOAD DATA INFILE ‚el_Hierro_oficinas-empleo-22.csv‘ INTO TABLE oficinas_empleo FIELDS TERMINATED BY ‚,‘;
Query OK, 35 rows affected (0,24 sec)
Records: 35 Deleted: 0 Skipped: 0 Warnings: 0

Jetzt hatte es geklappt.

mysql> SELECT * FROM oficinas_empleo
-> ;

Alles wurde eingefügt. Jetzt besaß aber die CSV-Datei eine Überschrift, die jetzt mit importiert worden ist. Also musste diese Überschrift entfernt werden. Ich habe den Index nun eingefügt. Das habe ich bei anderen Tabellen schon mehrmals gemacht. Ich mache mir von allen Befehlen immer Screenshots, die ich dann bei mir auf der Festplatte speichere.

mysql> ALTER TABLE oficinas_empleo ADD isla_id INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (1,73 sec)
Records: 0 Duplicates: 0 Warnings: 0

Da die Überschrift in der ersten Reihe war und das ist der Index 1 ist, habe ich den nun gelöscht:

mysql> DELETE FROM oficinas_empleo WHERE isla_id = 1;
Query OK, 1 row affected (0,42 sec)

Bei MongoDB ging es mit diesem Befehl bedeutend schneller. Ein Vorteil einer NoSQL-Datenbank.

mongoimport –db=Canarias –collection=elhierro –file=el_Hierro_empleo.json –jsonArray

Herzlich Willkommen

Linux, Datenbank, Weltraum, zivile Luftfahrt und einwenig ÖPNV

Vorhandene CSV-Datei in eine MySQL-Datenbank einfügen

3. April 2022 elsenorweb