CSV-File direkt in MySQL importieren

Heute möchte ich euch gerne ein cooles Feature von MySQL, welches ich gerade entdeckt habe, vorstellen.

Es geht dabei darum, wie man ein CSV-File direkt in eine Tabelle von MySQL importieren kann. Mit dieser Technik benötigt man dazu weder ein Script, noch sonst irgendwas um die Daten in die Tabelle zu kriegen.

Ausgangslage

Nehmen wir an, wir hätten folgendes CSV-File:

Die ID;Trash;Ein wichtiger String;Zahl zum rechnen
1;unwichtig;Test String;45
2;crap;Dies ist ein richtig wichtiger String!;32
32133;asdf;Und noch ein String zum Testen...;3
1337;habadabadaba;H4x0r-String;42

Dazu folgende MySQL-Tabelle:

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| field1 | int(11)      | NO   |     | NULL    |       |
| field2 | varchar(200) | NO   |     | NULL    |       |
| field3 | int(11)      | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

Das SQL

Irgenwie müssen wir das File nun in die Tabelle kriegen. Dabei gilt: “field1″ aus der Tabelle ist die erste Zahl des CSV-Files, “field2″ ist der String an Position 3 und “field3″ ist das zehnfache der letzten Zahl. Den String an Position 2 wird weggelassen, genau wie die erste Zeile (die Titel).

Das SQL sieht für dieses Beispiel also so aus:

LOAD DATA LOCAL INFILE 'csvTest.csv'
INTO TABLE csvImport
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(field1, @dummy, field2, @rechenVar)
SET field3 = @rechenVar*10

In der ersten Zeile sehen wir “csvTest.csv”. Dies ist der Name meines CSV-Files.

Meine MySQL-Tabelle, in die ich Daten gerne hätte, hat den Namen “csvImport”. Damit diese verwendet wird, gebe ich sie in der zweiten Zeile an.

Die dritte Zeile definiert das Zeichen, mit dem die einzelnen Felder im CSV-File getrennt werden.

“Enclosed by” in Zeile vier kann verwendet werden, wenn einzelne Felder noch mit einem Zeichen umschlossen sind. Dies ist oft der Fall, wenn man die Daten aus einem Excel-File in ein CSV-File exportiert. In diesem Fall unnötig. Für Demonstrationszwecke habe ich es trotzdem mal stehen lassen.

Linie fünf bestimmt, wie eine Zeile endet. Normalerweise ist die “\n” oder “\r\n” auf manchen Windows-Systemen.

In Zeile sechs habe ich angegeben, dass die erste Zeile übersprungen wird. Dies ist vor Allem in Fällen mit einer Titel-Zeile, wie in diesem Beispiel, wichtig.

Die siebte Zeile ist wichtig. Hier wird angegeben, in welche Felder der Datenbank die Daten aus dem CSV-File geschrieben werden. Hier habe ich zum Beispiel bestummen, dass das erste Feld aus dem CSV-File in das Feld “field1″ in der Datenbank gespeichert wird. Das zweite CSV-Feld wird ignoriert und nicht benötigt. Es wird einfach in eine “Dummy-Variable” geschrieben. Mit dem vierten Feld wollen wir ja rechnen. Damit dies möglich ist, speichere ich die Zahl in einer Variable ab.

Diese Variable verwende ich nun auf der achten Zeile, und multipliziere den Wert mit zehn. Das Ergebnis wird in das Feld “field3″ in der Datenbank abgespeichert.

Ergebnis

Als Ergebnis erhalte ich nun folgende Einträge in der Tabelle:

+--------+----------------------------------------+--------+
| field1 | field2                                 | field3 |
+--------+----------------------------------------+--------+
|      1 | Test String                            |    450 |
|      2 | Dies ist ein richtig wichtiger String! |    320 |
|  32133 | Und noch ein String zum Testen...      |     30 |
|   1337 | H4x0r-String                           |    420 |
+--------+----------------------------------------+--------+

Irgendwie cool…

Wie du dies ausprobierst

Am einfachsten geht es, wenn du auf deinem Linux-System in einem Ordner zwei Files erstellst. In das erste schreibst du die CSV-Daten. In dem zweiten schreibst die SQL-Anweisungen.

Mit “mysql -u root -p” gehst du in die MySQL-Umgebung (anstatt “root” verwendest du evt. dein eigenen MySQL-Benutzer). Hier kannst du nun mit “use DATENBANK” eine Datenbank wählen (“DATENBANK” mit dem Namen der Datenbank ersetzen ).

Um das SQL-File auszuführen, musst du nur noch folgenden Befehl ausführen “\. FileName.sql”.

Schlusswort

Wenn ich diese Methode früher schon gekannt hätte, hätte mir das einige Mühe und Zeit erspart. Auch muss man bedenken, das MySQL in der Verarbeitung viel schneller ist, als wenn du z.B. ein PHP-Script schreibst, dass dir die Daten importiert.

Und diese Methode ist cooler

Doku

Natürlich darf ein Link auf die MySQL-Dokumentation nicht fehlen: http://dev.mysql.com/doc/refman/5.1/en/load-data.html