[Erledigt] Große CSV Dateien einlesen

cetalian · 27.06.2009, 11:06

Hallo liebe Community

Ich habe ein (hoffentlich) kleineres Problem. Und zwar möchte ich CSV Dateien per PHP in eine Datenbank einlesen. Ein Script läd die Dateien hoch, man kann also davon ausgehen, dass sie sich auf dem Server befinden.

Mein naiver Ansatz per PHP Array ein riesiges INSERT Query zu erstellen versagt leider ab ca 10000 Einträgen. Ich würde nun gerne meinen Lösungsansatz hier vorstellen und ein wenig Feedback einsammeln.

Per "LOAD DATA INFILE" würde ich die komplette Datei vom Server in die Datenbank einfügen. Am liebsten wäre mir noch wenn ich Spalte 1 in Spalte 1 einfügen und alle anderen Spalten in Spalte 2, finde dazu aber (relativ kurzfristig, gebe ich zu) nichts im MySQL Handbuch.

Beispiel:
Spalte1;Spalte2;Spalte3
Wert1;Wert2;Wert3

soll zu

Spalte 1 | Spalte 2
Wert1 | Wert2;Wert3

werden.

Ist das mit der per SET schon im Query möglich?

Code:

LOAD DATA INFILE 'filename'
INTO TABLE 'tablename'
(column1, column2)
SET column2 = jede spalte der csv datei nach spalte 1(von mir aus als string einfach)

Ist das möglich/macht es Sinn? Gehen wir von ca 250.000 Datensätzen aus, gibt es performantere Lösungen?

Gruß

Wolla · 27.06.2009, 11:56

PHP-Code:

  $filename = 'datei.csv';

$z = 0; // Zähler

$fp = fopen( $filename, "r" );

while ($teile = fgetcsv($fp, 1024,";")) {

    $z++;

 
    if ( $z == 1 ) continue; // Feldnamen-Zeile

 
    foreach($teile as $key=>$value) {

        $teile[$key] = trim($value);

    }

 
    // Input-Daten aufbereiten

    $feld2 = $teile[2] . $teile[3];

    $feld1 = $teile[1];

 
    $sql_ins = "INSERT INTO tbl

                    SET 

                        feld1 = '" . mysql_real_escape_string($feld1) . "',

                        feld2 = '" . mysql_real_escape_string($feld2) . "'";

 
    if ($z % 3000 == 0) echo $z . '<br />';

    mysql_query($sql_ins) or die(mysql_error());

 
}

Bei 250k Zeilen in der CSV bekommst du ohne geeignete Gegenmaßnahmen einen Timeout. Ich verarbeite große CSV in einer Schleife (s.o.) und lasse mir alle X Datensätze, hier alle 3000, etwas ausgeben, was einen timeout verhindert.

cetalian · 27.06.2009, 14:07

Das ist unglücklicherweise sehr änhlich meinem ersten Ansatz, irre viele einzelne INSERT Querys. Ganz abgesehen davon, dass das MySQL Manual und diverse andere Seiten soweit ich informiert bin davon aus Performance Gründen abraten soll LOAD DATA INFILE bis zu 20 mal schneller als der Ansatz sein.

Als ich das so implementiert hatte habe ich maximal 15 Zeilen die Sekunde geschafft was bei 250.000 Zeilen nicht tragbar ist.

Da ich im Moment leider nicht auf der Arbeit bin und hier keinen Zugriff habe... weißt du in welcher Zeit dein Skript arbeiten würde? Wie gesagt, das Ziel ist leider sagen wir mal 200k Einträge in 30 Sekunden oder weniger, sonst hätte ich keine Probleme

nikosch · 27.06.2009, 14:50

- kleiner Ansatz: Erweiterte Inserts benutzen.
- grosser Ansatz: LOAD DATA IN FILE benutzen und das CSV File vorher so aufbereiten, wie Du es in die Tabelle prügeln willst. Mal ganz davon abgesehen, dass die Vereinigung von Daten in eine Spalte seltenst sinnvoll ist.

cetalian · 27.06.2009, 15:21

Was sind erweiterte INSERTS? Ich denke mal damit ist ein ähnlicher Ansatz gemeint wie ich ihn im Moment habe, nämlich per PhP einen INSERT Query für alle Daten der Datei aufzubauen und dann nur diesen an den mySQL Server zu senden?

Das alle Variablen nach der ersten in eine Spalte geschrieben werden stört mich ehrlich gesagt auch extrem. Das Problem ist nur, dass diese Variablen immer wieder in unterschiedlichen Anzahlen vorkommen können.

Am liebsten würde ich pro Upload eine Variablentabelle erzeugen die durch die 1. Spalte die Haupttabelle referenziert und eben die Variablen dieser Einträge aus der CSV Datei enthält. Aber ich denke sehr viele unterschiedliche Tabellen in einer Datenbank dürfte nicht grade eine elegante Lösung sein.

Ich bin für jede Idee dankbar. Wohlgemerkt Idee, Wollas Beitrag war ja nett gemeint, aber weder habt ihr Lust mir umsonst Code zu schreiben, noch ich Lust fremden Code unmotiviert zu kopieren

Wolla · 27.06.2009, 16:05

Kann sein, dass du eine andere Aufgabenstellung hast, aber bei den Daten, die ich importieren muss sind 10-12 Datenmanipulationen pro Zeile vorzunehmen. So bekomme ich zB Datumsfelder in höchst abenteuerlichen Formaten und darf nur solche Zeilen importieren, bei denen Kombinationen von Feldern bestimmten Richtlinien entsprechen.

Insofern ist die zeilenweise Verarbeitung für mich optimal, und in den 20 Sekunden, in denen der Job läuft, geh ich Kaffee holen. Das muss nicht in 16 Sek fertig sein

cetalian · 27.06.2009, 16:11

Hehe, mir wäre das ehrlich gesagt egal. Der Datei Upload ist aber nicht für mich sondern für Kunden. Und auf dem Server auf dem ich es getestet habe wären die 250k Zeilen sicherlich nicht in 20 Sekunden abgearbeitet. Im Moment braucht er wie gesagt für 10k Zeilen ca. 3 Minuten wenn ich jede Zeile bearbeite, in einen Array stecke und dann in einem großen Query einfüge.

Mit einem Insert pro Zeile... ohgott, ich mag garnicht mehr dran denken

nikosch · 27.06.2009, 16:24

Zitat:

Das alle Variablen nach der ersten in eine Spalte geschrieben werden stört mich ehrlich gesagt auch extrem. Das Problem ist nur, dass diese Variablen immer wieder in unterschiedlichen Anzahlen vorkommen können.

Tja, normal würde man das dann normalisieren und auf 2 Tabellen verteilen

Code:

Tabelle 1
ID | CSV Zeile 1, Spalte 1 
ID | CSV Zeile 2, Spalte 1 

Tabelle 2
ReferenceID | CSV Zeile 1, Spalte 2
ReferenceID | CSV Zeile 1, Spalte 3
ReferenceID | CSV Zeile 1, Spalte 4 ...
ReferenceID | CSV Zeile 2, Spalte 2 ...

Je nach Herkunft des CSV dürfte man da mit einer clientseitgen Aufarbeitung (Batchscript, Exe-Programm, Excel Makro/VB) besser beraten sein.

cetalian · 27.06.2009, 17:04

In 2 Tabellen würde ich es auch gerne machen, finde aber keinen Ansatz wenn CSV Datei 1 null Variablen, CSV Datei 2 vier und CSV Datei 3 zwei Variablen enthält...

Wobei, nehmen wir mal deine Idee. Tabelle 1 enthält nur die erste Spalte der CSV Datei + andere Informationen die nicht aus der CSV Datei stammen, Tabelle 2 referenziert diese erste Spalte und enthält einen Eintrag pro Variable in der CSV Datei mit Variablenbezeichnung aus dem CSV Head sowie dem Variablenwert. Kann ich ein file in 2 Tabellen einlesen nach dem Muster:

Code:

LOAD DATA INFILE 'xyz.csv'
INTO 'tabelle1', 'tabelle2'
(tabelle1.column1, tabelle1.column2, tabelle2.column1)
SET tabelle2.column1 = irgendwas

der Knackpunkt bei der Frage sollte einlesen in 2 Tabellen in der gleichen Datenbank sein, da bin ich mir nicht sicher, der Rest wirkt möglich.

nikosch · 27.06.2009, 19:40

Nö, der Knackpunkt liegt in den verschiedenen Strukturen (horizontal vs. vertikal)

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Dateien mit PHP Hochladen	h3ir	PHP-Fortgeschrittene	9	29.05.2009 18:46
[Erledigt] timeout bei Dateien einlesen	Hape42	PHP Tipps 2008	10	24.12.2008 11:20
Dateien einlesen & Variablen	Kein Genie	PHP-Fortgeschrittene	6	20.09.2008 14:20
[Erledigt] Alle Dateien eines Ordner und aller Unterordner aufrufen und die Größe ber	BartTheDevil89	PHP Tipps 2008	6	12.08.2008 19:17
Dateien einlesen	silver_surfer	PHP Tipps 2008	14	26.06.2008 19:19
PHP Dateien verschlüsseln	GSJLink	PHP-Fortgeschrittene	2	26.04.2008 12:29
[Logik] Mehrere Dateien als Anhang per Formular versenden	PsychoEagle	PHP Tipps 2008	2	27.08.2007 08:58
dateidownload mit limit für große dateien	imported_kremser	PHP Tipps 2007	6	25.04.2007 16:27
iCal Dateien mit php einlesen	rehmaster	PHP-Fortgeschrittene	3	11.01.2007 16:36
Dateien aus einem Ordner einlesen ???		PHP Tipps 2005-2	4	07.08.2005 18:21
Editor für große Dateien		Beitragsarchiv	8	21.04.2005 14:16
[Erledigt] Nur bestimmte Dateien anzeigen, wie mache ich das HILFE		PHP Tipps 2005	4	02.04.2005 22:38
[Erledigt] sehr große Dateien mit PHP verarbeiten		PHP-Fortgeschrittene	6	16.03.2005 22:06
Bilder und generell Dateien in DB einlesen		Datenbanken	1	02.02.2005 12:42
[Erledigt] Dateien aus Verzeichnissen vom Webserver auslesen und linken		PHP Tipps 2004	3	08.09.2004 10:07

27.06.2009, 11:06
cetalian Erfahrener Benutzer Registriert seit: 27.06.2009 Beiträge: 498 PHP-Kenntnisse: Anfänger	[Erledigt] Große CSV Dateien einlesen Hallo liebe Community Ich habe ein (hoffentlich) kleineres Problem. Und zwar möchte ich CSV Dateien per PHP in eine Datenbank einlesen. Ein Script läd die Dateien hoch, man kann also davon ausgehen, dass sie sich auf dem Server befinden. Mein naiver Ansatz per PHP Array ein riesiges INSERT Query zu erstellen versagt leider ab ca 10000 Einträgen. Ich würde nun gerne meinen Lösungsansatz hier vorstellen und ein wenig Feedback einsammeln. Per "LOAD DATA INFILE" würde ich die komplette Datei vom Server in die Datenbank einfügen. Am liebsten wäre mir noch wenn ich Spalte 1 in Spalte 1 einfügen und alle anderen Spalten in Spalte 2, finde dazu aber (relativ kurzfristig, gebe ich zu) nichts im MySQL Handbuch. Beispiel: Spalte1;Spalte2;Spalte3 Wert1;Wert2;Wert3 soll zu Spalte 1 \| Spalte 2 Wert1 \| Wert2;Wert3 werden. Ist das mit der per SET schon im Query möglich? Code: LOAD DATA INFILE 'filename' INTO TABLE 'tablename' (column1, column2) SET column2 = jede spalte der csv datei nach spalte 1(von mir aus als string einfach) Ist das möglich/macht es Sinn? Gehen wir von ca 250.000 Datensätzen aus, gibt es performantere Lösungen? Gruß


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

27.06.2009, 11:56
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.945 PHP-Kenntnisse: Fortgeschritten	PHP-Code: $filename = 'datei.csv'; $z = 0; // Zähler $fp = fopen( $filename, "r" ); while ($teile = fgetcsv($fp, 1024,";")) { $z++; if ( $z == 1 ) continue; // Feldnamen-Zeile foreach($teile as $key=>$value) { $teile[$key] = trim($value); } // Input-Daten aufbereiten $feld2 = $teile[2] . $teile[3]; $feld1 = $teile[1]; $sql_ins = "INSERT INTO tbl SET feld1 = '" . mysql_real_escape_string($feld1) . "', feld2 = '" . mysql_real_escape_string($feld2) . "'"; if ($z % 3000 == 0) echo $z . '<br />'; mysql_query($sql_ins) or die(mysql_error()); } Bei 250k Zeilen in der CSV bekommst du ohne geeignete Gegenmaßnahmen einen Timeout. Ich verarbeite große CSV in einer Schleife (s.o.) und lasse mir alle X Datensätze, hier alle 3000, etwas ausgeben, was einen timeout verhindert. Geändert von Wolla (27.06.2009 um 12:00 Uhr).

27.06.2009, 14:07
cetalian Erfahrener Benutzer Registriert seit: 27.06.2009 Beiträge: 498 PHP-Kenntnisse: Anfänger	Das ist unglücklicherweise sehr änhlich meinem ersten Ansatz, irre viele einzelne INSERT Querys. Ganz abgesehen davon, dass das MySQL Manual und diverse andere Seiten soweit ich informiert bin davon aus Performance Gründen abraten soll LOAD DATA INFILE bis zu 20 mal schneller als der Ansatz sein. Als ich das so implementiert hatte habe ich maximal 15 Zeilen die Sekunde geschafft was bei 250.000 Zeilen nicht tragbar ist. Da ich im Moment leider nicht auf der Arbeit bin und hier keinen Zugriff habe... weißt du in welcher Zeit dein Skript arbeiten würde? Wie gesagt, das Ziel ist leider sagen wir mal 200k Einträge in 30 Sekunden oder weniger, sonst hätte ich keine Probleme

27.06.2009, 14:50
nikosch moderatives Dielektrikum Registriert seit: 21.05.2008 Beiträge: 35.987 PHP-Kenntnisse: Fortgeschritten	- kleiner Ansatz: Erweiterte Inserts benutzen. - grosser Ansatz: LOAD DATA IN FILE benutzen und das CSV File vorher so aufbereiten, wie Du es in die Tabelle prügeln willst. Mal ganz davon abgesehen, dass die Vereinigung von Daten in eine Spalte seltenst sinnvoll ist. __________________ -- One pixel is still too big. Please make it smaller. ASAP. Initiative Mittelstand. Die wichtigste Gestaltungsregel im Screendesign ist Pi mal Daumen des Arbeitgebers. --

27.06.2009, 15:21
cetalian Erfahrener Benutzer Registriert seit: 27.06.2009 Beiträge: 498 PHP-Kenntnisse: Anfänger	Was sind erweiterte INSERTS? Ich denke mal damit ist ein ähnlicher Ansatz gemeint wie ich ihn im Moment habe, nämlich per PhP einen INSERT Query für alle Daten der Datei aufzubauen und dann nur diesen an den mySQL Server zu senden? Das alle Variablen nach der ersten in eine Spalte geschrieben werden stört mich ehrlich gesagt auch extrem. Das Problem ist nur, dass diese Variablen immer wieder in unterschiedlichen Anzahlen vorkommen können. Am liebsten würde ich pro Upload eine Variablentabelle erzeugen die durch die 1. Spalte die Haupttabelle referenziert und eben die Variablen dieser Einträge aus der CSV Datei enthält. Aber ich denke sehr viele unterschiedliche Tabellen in einer Datenbank dürfte nicht grade eine elegante Lösung sein. Ich bin für jede Idee dankbar. Wohlgemerkt Idee, Wollas Beitrag war ja nett gemeint, aber weder habt ihr Lust mir umsonst Code zu schreiben, noch ich Lust fremden Code unmotiviert zu kopieren

27.06.2009, 16:05
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.945 PHP-Kenntnisse: Fortgeschritten	Kann sein, dass du eine andere Aufgabenstellung hast, aber bei den Daten, die ich importieren muss sind 10-12 Datenmanipulationen pro Zeile vorzunehmen. So bekomme ich zB Datumsfelder in höchst abenteuerlichen Formaten und darf nur solche Zeilen importieren, bei denen Kombinationen von Feldern bestimmten Richtlinien entsprechen. Insofern ist die zeilenweise Verarbeitung für mich optimal, und in den 20 Sekunden, in denen der Job läuft, geh ich Kaffee holen. Das muss nicht in 16 Sek fertig sein

27.06.2009, 16:11
cetalian Erfahrener Benutzer Registriert seit: 27.06.2009 Beiträge: 498 PHP-Kenntnisse: Anfänger	Hehe, mir wäre das ehrlich gesagt egal. Der Datei Upload ist aber nicht für mich sondern für Kunden. Und auf dem Server auf dem ich es getestet habe wären die 250k Zeilen sicherlich nicht in 20 Sekunden abgearbeitet. Im Moment braucht er wie gesagt für 10k Zeilen ca. 3 Minuten wenn ich jede Zeile bearbeite, in einen Array stecke und dann in einem großen Query einfüge. Mit einem Insert pro Zeile... ohgott, ich mag garnicht mehr dran denken

27.06.2009, 17:04
cetalian Erfahrener Benutzer Registriert seit: 27.06.2009 Beiträge: 498 PHP-Kenntnisse: Anfänger	In 2 Tabellen würde ich es auch gerne machen, finde aber keinen Ansatz wenn CSV Datei 1 null Variablen, CSV Datei 2 vier und CSV Datei 3 zwei Variablen enthält... Wobei, nehmen wir mal deine Idee. Tabelle 1 enthält nur die erste Spalte der CSV Datei + andere Informationen die nicht aus der CSV Datei stammen, Tabelle 2 referenziert diese erste Spalte und enthält einen Eintrag pro Variable in der CSV Datei mit Variablenbezeichnung aus dem CSV Head sowie dem Variablenwert. Kann ich ein file in 2 Tabellen einlesen nach dem Muster: Code: LOAD DATA INFILE 'xyz.csv' INTO 'tabelle1', 'tabelle2' (tabelle1.column1, tabelle1.column2, tabelle2.column1) SET tabelle2.column1 = irgendwas der Knackpunkt bei der Frage sollte einlesen in 2 Tabellen in der gleichen Datenbank sein, da bin ich mir nicht sicher, der Rest wirkt möglich.

27.06.2009, 19:40
nikosch moderatives Dielektrikum Registriert seit: 21.05.2008 Beiträge: 35.987 PHP-Kenntnisse: Fortgeschritten	Nö, der Knackpunkt liegt in den verschiedenen Strukturen (horizontal vs. vertikal) __________________ -- One pixel is still too big. Please make it smaller. ASAP. Initiative Mittelstand. Die wichtigste Gestaltungsregel im Screendesign ist Pi mal Daumen des Arbeitgebers. --