phpBuddy

CSV Datei in eine MySQL Tabelle importieren

Hin und wieder steht man vor der Aufgabe eine CSV Datei in eine Datenbank übertragen zu müssen. Dies kann man zwar auf verschiedene Arten angehen, allerdings muss man abwägen, welche Methode am geeignetsten ist. Während es bei kleinen Dateien, mit nur wenigen Datensätzen (weniger als 1000) relativ beliebig ist, spielt die Performance und die allgemeine Serverlast, sowie die PHP Speicherlimitierung bei zunehmendem Volumen eine große Rolle.
Bei kleineren oder fun Projekten wird man diesem Problem eher selten begegnen, aber bei Business Projekten kommt es häufiger mal vor, dass einem der Auftraggeber eine Excel oder CSV Datei mit z.B. Kundendaten vor die Füße wirft, die schon mal locker 50.000 Datensätze übersteigen und die unbedingt in die Applikation eingepflegt werden müssen. Würde man hier die üblichen Verdächtigen, sprich gängigen Methoden, anwenden, die man so im Web findet um die Daten in die MySQL zu bekommen, stände man vermutlich vor einem großen Problem.

Ich möchte mal einen Blick auf die beiden typischen Methoden werfen, wie es sicher die meisten mit PHP lösen würden und dann möchte ich noch eine Methode vorstellen, die in, nennen wir es mal Amateurkreisen, wenig bekannt sein dürfte.

Gehen wir mal von der Situation aus, dass wir eine stark vereinfachte CSV Datei haben, in der Datensätze mit Name und Beruf stehen. Diese soll in eine bestehende MySQL Tabelle eingefügt werden, die die Struktur hat: id, name, beruf. Eine Datenbankverbindung besteht bereits und wir sind nun an dem Punkt, an dem wir die CSV verarbeiten und in die DB bringen. Hier mal unsere Beispiel CSV Datei:

Andreas;Webentwickler
Michael;Schreiner
Matthias;KFZ-Mechaniker
Yvonne;Einzelhandelskauffrau
Peter;Architekt
Sabrina;Journalistin

Nichts spektakuläres, nur einige Zeilen Text. Jede Zeile Text entspricht einem Datensatz. Die Felder sind mit einem Semikolon separiert. Schauen wir uns mal an, welche Methoden es gibt die Daten in die Datenbank zu befördern.

1. Die schlechteste Methode
und leider zugleich auch die Methode, die am häufigsten angewendet wird. Das Listing:

// Jeden Datensatz einzeln in die DB schreiben
$benutzer_liste = file($csv_datei);
foreach ($benutzer_liste as $benutzer)
{
    list($name, $beruf) = explode(';', $benutzer);
    $db->query("INSERT INTO `{$tabelle}` (`name`, `beruf`) VALUES ('{$name}', '{$beruf}')");
}

Zunächst wird mit der file-Funktion die CSV als Array eingelesen. In der anschließenden foreach-Schleife lesen wird Datensatz für Datensatz aus, zerlegen die Zeilen am Separator, wodurch wir die getrennten Werte Name und Beruf erhalten. Diese schicken wir dann direkt mittels query() sofort an die Datenbank, die den neuen Datensatz dann in die Tabelle schreibt. Wir schreiben also jeden Datensatz einzeln in die Datenbank. In unserer Beispiel CSV Datei befinden sich 6 Datensätze, also kontaktieren wir auch "nur" 6 mal die Datenbank. Mit dieser Methode würden wir bei einer CSV mit 10.000 Datensätzen auch "nur" 10.000 mal unsere Datenbank matern.

Diese Methode verschwendet massiv Zeit und Ressourcen, deswegen ist sie ein absolutes no-go!
Werfen wir lieber mal einen Blick auf die nächste Methode, vielleicht ist die ja besser geeignet.

2. Die Notlösung
Um die Schreibvorgänge zu beschleunigen und um Ressourcen zu sparen müssen wir dafür sorgen, dass wie im Vergleich zur letzten Methode die Anzahl der Queries stark reduzieren. Das Listing:

// Datensätze zusammenfassen
$benutzer_liste = file($csv_datei);
$value_array = array();
foreach ($benutzer_liste as $benutzer)
{
    list($name, $beruf) = explode(';', $benutzer);
    $value_array[] = "('{$name}', '{$beruf}')";
}
$values = join(',', $value_array);
$db->query("INSERT INTO `{$tabelle}` (`name`, `beruf`) VALUES {$values}");

Wir starten zunächst wie im vorherigen Beispiel, indem wir die Datei als Array einlesen und in einer Schleife durchlaufen. Bei dieser Methode schicken wir allerdings nichts an die Datenbank, sondern bilden Wertepaare, die wir dann en bloc an die Datenbank schicken. Dies geschieht in dieser Zeile:

$value_array[] = "('{$name}', '{$beruf}')";

Im Array legen wir also Zeichenketten ab, die z.B. so aussehen: ('Andreas','Webentwickler')
Das entspricht also einem Wertepaar, wie wir es mittels VALUE an die DB schicken. Nachdem alle Wertepaare gebildet wurden, werden diese mit join, das ist ein Alias von implode, und dem Klebstoff Komma (,) zu einer Zeichenkette zusammengefügt und an unser VALUE übergeben. Dadurch erhalten wir eine Anweisung die in etwas so aussieht:

INSERT INTO `{$tabelle}` (`name`, `beruf`) VALUES ('Andreas','Webentwickler'),('Michael','Schreiner'),('Matthias','KFZ-Mechaniker') ... usw.

Anschließend wird diese Anweisung nur ein Mal an die DB geschickt und alle Datensätze werden hinzugefügt. Wir kontaktieren also nicht so häufig die Datenbank, verrichten dafür aber mehr PHP-Arbeit. Wie sich das auf die Performance auswirkt wird weiter unten gezeigt. Kommen wir zur eingangs erwähnten Methode, die weniger bekannt ist.

3. Die optimale Methode
Die schnellste und für den Server schonenste Möglichkeit die CSV Daten in die Datenbank zu bekommen ist, wenn wir das die Datenbank selbst erledigen lassen. Datenbankaufgaben sind immer um ein vielfaches schneller, als gekünstelte Lösungen mit PHP. Zunächst der Code:

// Datenbankanweisung
$sql = "LOAD DATA LOCAL INFILE '{$csv_datei}'
        INTO TABLE `{$tabelle}`
        FIELDS TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        (`name`, `beruf`)";
 
// Query senden
$db->query($sql);

Wie man sieht senden wir nur eine mehr oder weniger einfache Anweisung an die Datenbank. Wir wursteln da nicht mit PHP herum, jagen keine Arrays durch Schleifen, oder ähnliches.
Wir teilen MySQL mit, dass es eine lokale CSV Datein laden soll:

LOAD DATA LOCAL INFILE '{$csv_datei}'

Der Pfad zu der Datei muss ein absoluter Serverpfad sein. Es reicht also nicht aus nur den Dateiname zu übergeben, selbst wenn diese im gleichen Verzeichnis wie das PHP Script liegt. Dann sagen wir MySQL, wohin die geladene Datei eingefügt werden soll:

INTO TABLE `{$tabelle}`

Jetzt folgen Anweisungen an MySQL die der DB mitteilen, wie die Daten in der CSV vorliegen:

FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'

Die erste Zeile bestimmt, dass die Werte in unserer CSV durch Semikola getrennt sind (Andreas;Webentwickler). Die Zweite Zeile sagt, dass unsere Werte auch durch Double-Quotes eingeschlossen sein dürfen. Dadurch zerbricht die Struktur nicht, falls ein Separator im Wert vorkommen würde, z.B.: ("Andreas;phpBuddy";Webentwickler). Ohne die Double-Quotes würde MySQL denken, dass Andreas der Name ist und phpBuddy der Beruf. Mit Webentwickler könnte es nichts anfangen und da man auch keine 3 Werte in 2 Spalten einfügen kann, käme es zu einem Fehler.
Die dritte Zeile mit LINES TERMINATED BY legt fest, wo ein Datensatz endet und ein neuer anfängt. Bei einer CSV ist das der Zeilenumbruch, der durch das Steuerzeichen \n repräsentiert wird.

Bleibt noch die letzte Zeile, die eigentlich selbsterklärend sein sollte.

(`name`, `beruf`)

Das sind schlicht die Felder in der Tabelle, in die die Werte aus der CSV eingefügt werden sollen. Das ist vergleichbar mit:

INSERT INTO `tabelle` (`name`, `beruf`) VALUES (…)

Auf den ersten Blick sieht die Anweisung etwas heftig aus, erklärt sich dann aber sehr schnell von allein. Fragt sich jetzt aber, was das effektiv bringt im Vergleich zu den anderen Methoden.

Der Methodenvergleich
Ich habe mal einen kleinen Test gemacht mit einer CSV Datei, die 37.000 Datensätze enthielt und stark abgespeckt war, da sie lediglich aus Vorname und Nachname bestand, ähnlich unserer Beispieldatei weiter oben. Die CSV Datei war insgesamt knapp 540 KB groß.
Ich habe diese Datei mit den 3 Methoden in die Datenbank geschrieben und dabei die Scriptlaufzeit, sowie den Arbeitsspeicherverbrauch ermittelt. Das kam dabei heraus:

• Methode 1: Scriptlaufzeit 6.426 Sekunden - Memory Peak 6 429 KB
• Methode 2: Scriptlaufzeit 0.908 Sekunden - Memory Peak 14 191 KB
• Methode 3: Scriptlaufzeit 0.241 Sekunden - Memory Peak 338 KB

Wie zu erkennen ist stinkt die 1. Methode total ab. Sie dauert sehr lange und mit 6 MB RAM Verbrauch ist sie auch nicht gerade sparsam. Bedenkt man, dass man auf Shared Hosting Server, also normaler Mietwebspace, häufig nur 8 MB RAM für PHP zur Verfügung hat, wird das schon richtig eng, wenn noch andere Aktivitäten auf dem Server stattfinden.
Methode 2 ist schon um ein vielfaches schneller, aber dadurch das von den Wertpaaren ein ewiglanger String erzeugt wird, damit wir nur ein Mal die Datenbank kontaktieren müssen, wächst auch der RAM Verbrauch entsprechend an. 14 MB, nur für eine kleine Datenbank Operation ist schon heftig!
Methode 3 braucht nur einen Bruchteil an Scriptlaufzeit und mit gerade einmal 338 KB hält sich der RAM Verbrauch auch in Grenzen. ABER … so toll diese Methode auch ist, hat sie auch einen Haken. Auf Mietwebspace, besonders bei billigen Paketen, hat man in der Regel keine ausreichenden Rechte eine INFILE Operation auszuführen. Hier muss man sich dann mit einer der anderen Methoden begnügen. Im Zweifelfall kann man sehr einfach herausfinden ob man diese Methode wählen kann, indem man es einfach ausprobiert. ;-)

Ich hoffe dieser Tipp war dem Ein oder Anderen eine Hilfe. Im Download befinden sich alle 3 Methoden in einer Datei und dazu auch eine kleine CSV Testdatei.

Viele Grüße
phpBuddy

CSV in MySQL importieren (1 KB)