Große CSV-Datei in Datenbank

Dublay · 09.03.2012, 16:42

Hallo,

ich habe eine riesige CSV-Datei (>2GB) und stelle mir gerade die Frage, wie man einen Zugriff auf dieses Monstrum am Besten bewerkstelligt.

Sollte man die Datei in eine Datenbank geben? Oder direkt über fopen und fgetcsv die Anfragen abwickeln? Welche Methode ist ressourcenschonender?

Und wie bekomme ich so eine große Datei ohne TimeOuts in die Datenbank geschrieben?

Danke für Eure Tipps und Hilfen!

fab · 09.03.2012, 16:50

Kommt drauf an, was du mit

Zitat:

Zugriff

meinst. Was hast du mit den Daten vor?

Um CSV-Daten in eine MySQL-Datenbank zu bekommen bietet sich z.B. LOAD DATA an.

Wolla · 09.03.2012, 17:10

PHP-Code:

  <?php
set_time_limit(0);
$filename = 'datei.csv';
$z = 0;
$fp = fopen( $filename, "r" );
while ($teile = fgetcsv($fp, 2048,",")) { // Kommagetrennt, hier ggf. ";" einsetzen

  if ( $z == 0) { // Überschriftszeile nicht importieren
    continue;
  }

  $sql_ins = "INSERT INTO tabelle (...) VALUES (...) "; // mysql_real_escape_string nicht vergessen
  mysql_query($sql_ins) or die(mysql_error());
  $z++;
}
fclose($fp);
echo 'Verarbeitet ' . $z;

Bei extrem vielen Zeilen scheitert der Import per PMA immer. Ich bevorzuge solch ein Script hier auch deshalb, weil man zugleich Prüfungen, Berechnungen und weitere Ausgaben in die Schleife setzen kann. Das läuft ggf. langsamer als LOAD DATA, ist aber flexibler, und es juckt mich nicht, ob ein einmalscript jetzt 3 min oder 10 läuft.

mquadrat · 12.03.2012, 11:02

Auf jeden Fall in eine Datenbank packen. Im Worst-Case musst du ja die ganze 2GB Datei durchnudeln bis du den Datensatz gefunden hast, den du gerade bearbeiten / abfragen willst.

Dublay · 14.03.2012, 10:47

Super, vielen Dank für die Antworten und auch für das Script. Dann werde ich das jetzt mal so umsetzen!

tr0y · 15.03.2012, 09:14

PHP-Code:

  <?php
set_time_limit(0);
$filename = 'datei.csv';
$z = 0;
$fp = fopen( $filename, "r" );
while ($teile = fgetcsv($fp, 2048,",")) { // Kommagetrennt, hier ggf. ";" einsetzen

  if ( $z == 0) { // Überschriftszeile nicht importieren
    continue;
  }

  $sql_ins = "INSERT INTO tabelle (...) VALUES (...) "; // mysql_real_escape_string nicht vergessen
  mysql_query($sql_ins) or die(mysql_error());
  $z++;
}
fclose($fp);
echo 'Verarbeitet ' . $z;

PHP-Code:

  <?php

set_time_limit(0);

$file = 'datei.csv'; $zeile = 0;

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

/* pdo verbindungsaufbau von wo anders */

$stmt = $db->prepare("INSERT INTO dahinten ( a, b, c, d, e ) VALUES ( ?, ?, ?, ?, ? )");

echo "Knochenarbeit gestartet :".date_create()->format('d.m.Y H:i:s');

while( $row = fgetcsv($fp, 2048,",") ) {
   if ( $zeile = 0 ) continue;
   $stmt->execute($row);
   $stmt->closeCursor();
   $zeile++;
}

echo "Knochenarbeit endete :".date_create()->format('d.m.Y H:i:s').". ".(string)$zeile." Lemminge verstorben.";

Dublay · 20.03.2012, 12:30

Noch eine kurze Anfängerfrage: Wie greife ich auf einzelnen Values aus dem CSV zu, sprich wie nenne ich diese, damit das funktioniert?

Also bei INSERT INTO tabelle (spalte1, spalte2, spalte3) VALUES (csv-zeile2-spalte-1, csv-zeile2-spalte-2, csv-zeile2-spalte-3)?

Dankeschön!

tr0y · 20.03.2012, 12:40

hol halt eine Zeile ab und schau dir das array an, mit var_dump

Wolla · 20.03.2012, 17:35

PHP-Code:

  $stmt->execute($row);

Ich habe in $row[] nur strings. Funktioniert die Übergabe per Array auch dann, wenn ich INT-Spalten in der Tabelle habe? Ich weise das immer einzeln zu:

PHP-Code:

  $stmt = $db->prepare($sql);
$stmt->bindValue(':spalte1',    (int) $row[0],  PDO::PARAM_INT);
$stmt->bindValue(':spalte2',    $row[1],        PDO::PARAM_STR);

tr0y · 20.03.2012, 23:45

@Wolla:

Aber sicher, die Datenbank casted den String zum Feldtyp, entsprechend wär ein

PHP-Code:

  $stmt->execute(array(':spalte1' => $row[0], ':spalte2' => $row[1]));

genauso effektiv.

Code:

INSERT INTO theTable ( spalte1, spalte2 ) VALUE ( :spalte1, :spalte2 )

wäre die Grundlage als Beispiel, und...

Code:

INSERT INTO theTable ( spalte1, spalte2 ) VALUE ( 'row[0]-value', 'row[1]-value' )

die eskalation des executes

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
php und mysql Datenbank schreiben und Datei downloaden	Nakedchef	PHP Einsteiger	11	22.08.2011 17:06
Mehrere Querys klappen in PhpMyAdmin aber nicht in PHP	djscaleo	Datenbanken	36	15.05.2011 02:00
Daten als xml datei zwischen speichern oder xml text in datenbank ablegen?	skelle	PHP Einsteiger	7	03.02.2011 10:02
Scriptsuche Daten aus MySQl Datenbank und CSV Datei vergleichen u. ggf übertragen oder löschen	headbannger83	Scriptbörse	0	07.01.2011 01:00
Große remote Datei herunterladen	Capricorn	PHP Tipps 2009	1	06.01.2010 19:40
Wert in externer Datei speichern	Abakay	PHP Tipps 2008	6	04.12.2008 18:56
[Erledigt] Alle Dateien eines Ordner und aller Unterordner aufrufen und die Größe ber	BartTheDevil89	PHP Tipps 2008	6	12.08.2008 19:17
Config Datei oder Datenbank	snatch-ic	PHP Tipps 2008	11	17.03.2008 06:13
Großer Baum -> Große Datei -> Hohe Ladezeit :(	PsychoEagle	PHP Tipps 2008	7	22.09.2007 12:44
XML Datei in Datenbank importieren	Plague	PHP Tipps 2007	13	06.08.2007 10:26
[Erledigt] SQL Datei per PHP in die Datenbank eintragen		PHP Tipps 2005-2	3	06.10.2005 12:57
20mb große datei auf server laden	bratwurstschorsch	PHP Tipps 2005-2	27	29.08.2005 15:18
PHP Class Datei Basirende Datenbank mit MySQL Syntax	atom-dragon	Beitragsarchiv	4	01.08.2005 06:57
[Erledigt] Große .gz Datei entpacken		PHP-Fortgeschrittene	7	03.06.2005 11:17
Größe einer Datenbank und Datentypen anzeigen	Klaus	Datenbanken	2	18.12.2004 16:21

09.03.2012, 16:42
Dublay Benutzer Registriert seit: 11.02.2011 Beiträge: 35 PHP-Kenntnisse: Fortgeschritten	Große CSV-Datei in Datenbank Hallo, ich habe eine riesige CSV-Datei (>2GB) und stelle mir gerade die Frage, wie man einen Zugriff auf dieses Monstrum am Besten bewerkstelligt. Sollte man die Datei in eine Datenbank geben? Oder direkt über fopen und fgetcsv die Anfragen abwickeln? Welche Methode ist ressourcenschonender? Und wie bekomme ich so eine große Datei ohne TimeOuts in die Datenbank geschrieben? Danke für Eure Tipps und Hilfen!


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

09.03.2012, 17:10
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 5.189 PHP-Kenntnisse: Fortgeschritten	PHP-Code: <?php set_time_limit(0); $filename = 'datei.csv'; $z = 0; $fp = fopen( $filename, "r" ); while ($teile = fgetcsv($fp, 2048,",")) { // Kommagetrennt, hier ggf. ";" einsetzen if ( $z == 0) { // Überschriftszeile nicht importieren continue; } $sql_ins = "INSERT INTO tabelle (...) VALUES (...) "; // mysql_real_escape_string nicht vergessen mysql_query($sql_ins) or die(mysql_error()); $z++; } fclose($fp); echo 'Verarbeitet ' . $z; Bei extrem vielen Zeilen scheitert der Import per PMA immer. Ich bevorzuge solch ein Script hier auch deshalb, weil man zugleich Prüfungen, Berechnungen und weitere Ausgaben in die Schleife setzen kann. Das läuft ggf. langsamer als LOAD DATA, ist aber flexibler, und es juckt mich nicht, ob ein einmalscript jetzt 3 min oder 10 läuft. __________________ Warum denkt mein Hund eigentlich immer dann, wenn es an der Tür klingelt, es sei für ihn?

12.03.2012, 11:02
mquadrat Erfahrener Benutzer Registriert seit: 02.09.2009 Beiträge: 1.095 PHP-Kenntnisse: Fortgeschritten	Auf jeden Fall in eine Datenbank packen. Im Worst-Case musst du ja die ganze 2GB Datei durchnudeln bis du den Datensatz gefunden hast, den du gerade bearbeiten / abfragen willst. __________________ Wir suchen PHP Entwickler (Vollzeit) im Raum Darmstadt / Rhein-Main. Infos via E-Mail mueller@new-frontiers.de

14.03.2012, 10:47
Dublay Benutzer Registriert seit: 11.02.2011 Beiträge: 35 PHP-Kenntnisse: Fortgeschritten	Super, vielen Dank für die Antworten und auch für das Script. Dann werde ich das jetzt mal so umsetzen!

15.03.2012, 09:14
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 7.231 PHP-Kenntnisse: Fortgeschritten	PHP-Code: <?php set_time_limit(0); $filename = 'datei.csv'; $z = 0; $fp = fopen( $filename, "r" ); while ($teile = fgetcsv($fp, 2048,",")) { // Kommagetrennt, hier ggf. ";" einsetzen if ( $z == 0) { // Überschriftszeile nicht importieren continue; } $sql_ins = "INSERT INTO tabelle (...) VALUES (...) "; // mysql_real_escape_string nicht vergessen mysql_query($sql_ins) or die(mysql_error()); $z++; } fclose($fp); echo 'Verarbeitet ' . $z; PHP-Code: <?php set_time_limit(0); $file = 'datei.csv'; $zeile = 0; $fp = fopen( $file, "r" ); /* pdo verbindungsaufbau von wo anders */ $stmt = $db->prepare("INSERT INTO dahinten ( a, b, c, d, e ) VALUES ( ?, ?, ?, ?, ? )"); echo "Knochenarbeit gestartet :".date_create()->format('d.m.Y H:i:s'); while( $row = fgetcsv($fp, 2048,",") ) { if ( $zeile = 0 ) continue; $stmt->execute($row); $stmt->closeCursor(); $zeile++; } echo "Knochenarbeit endete :".date_create()->format('d.m.Y H:i:s').". ".(string)$zeile." Lemminge verstorben."; __________________ - tr0y veredelt Kacknoobs zu PHPlayboys ~ Macht tr0y glücklich ~ pgSQL, leider Geil ~ Das kommt dir auf die Platte nach der Deinstallation von WAMP

20.03.2012, 12:30
Dublay Benutzer Registriert seit: 11.02.2011 Beiträge: 35 PHP-Kenntnisse: Fortgeschritten	Noch eine kurze Anfängerfrage: Wie greife ich auf einzelnen Values aus dem CSV zu, sprich wie nenne ich diese, damit das funktioniert? Also bei INSERT INTO tabelle (spalte1, spalte2, spalte3) VALUES (csv-zeile2-spalte-1, csv-zeile2-spalte-2, csv-zeile2-spalte-3)? Dankeschön!

20.03.2012, 12:40
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 7.231 PHP-Kenntnisse: Fortgeschritten	hol halt eine Zeile ab und schau dir das array an, mit var_dump __________________ - tr0y veredelt Kacknoobs zu PHPlayboys ~ Macht tr0y glücklich ~ pgSQL, leider Geil ~ Das kommt dir auf die Platte nach der Deinstallation von WAMP

20.03.2012, 17:35
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 5.189 PHP-Kenntnisse: Fortgeschritten	PHP-Code: `$stmt->execute($row);` Ich habe in $row[] nur strings. Funktioniert die Übergabe per Array auch dann, wenn ich INT-Spalten in der Tabelle habe? Ich weise das immer einzeln zu: PHP-Code: `$stmt = $db->prepare($sql); $stmt->bindValue(':spalte1', (int) $row[0], PDO::PARAM_INT); $stmt->bindValue(':spalte2', $row[1], PDO::PARAM_STR);` __________________ Warum denkt mein Hund eigentlich immer dann, wenn es an der Tür klingelt, es sei für ihn?

20.03.2012, 23:45
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 7.231 PHP-Kenntnisse: Fortgeschritten	@Wolla: Aber sicher, die Datenbank casted den String zum Feldtyp, entsprechend wär ein PHP-Code: `$stmt->execute(array(':spalte1' => $row[0], ':spalte2' => $row[1]));` genauso effektiv. Code: INSERT INTO theTable ( spalte1, spalte2 ) VALUE ( :spalte1, :spalte2 ) wäre die Grundlage als Beispiel, und... Code: INSERT INTO theTable ( spalte1, spalte2 ) VALUE ( 'row[0]-value', 'row[1]-value' ) die eskalation des executes __________________ - tr0y veredelt Kacknoobs zu PHPlayboys ~ Macht tr0y glücklich ~ pgSQL, leider Geil ~ Das kommt dir auf die Platte nach der Deinstallation von WAMP