parsing large csv into db

minorgod

I have a script which decompresses a gz archive to a large text file (178,000 lines) and then attempts to feed it into the DB line by line using fgets($fp). My script gets to about line 35,000 of the input file and then stops (the number is different every time). It's not a parsing problem because it happens at a different place every time, but usually after 30,000 lines have made it into the db. There is no error thrown and I'm assuming I'm either running out of memory (unlikely as I'm reading the file 1 line at a time) or the script or browser is timing out. I've tried adding things like...

ini_set('memory_limit','1000M');
ini_set('max_execution_time','600');
ini_set('max_input_time','600');
set_time_limit(600);

But nothing seems to help. I've also tried something like:

header("Connection: Keep-Alive");
header("Keep-Alive: timeout=600");

but no luck. Anyone have any ideas? I'm hoping I can do this without having to split my input file into smaller pieces.

Shrike

Memory should not be a problem if you are running line by line. If you run the PHP script from a command line then max execution time is also disabled, plus you can output debugging in real time more easily than in a browser. You might also want to check your error reporting level, and if the display_errors ini setting is on.

BuzzPHP

you could try this idea. Process the file over multiple HTTP requests of say, 5,000 records each.

This code only does one record at a time, but just add a loop to what ever size batch you want to handle.

A session variable is used to store the current file position and there is even a nice progress bar using a DIV element.

Im using a META page refresh, but you could try a SCRIPT refresh if you have problems.

<?php
session_start();
$filename = "data.txt";
$fp = fopen($filename, "r");
if ($fp)
{
  fseek($fp, $_SESSION["offset"]);
  if ($s = fgets($fp))
  {
    // Parse CSV and insert code to insert record into database.
    $_SESSION["offset"] = ftell($fp);
    echo "<HEAD><meta http-equiv='REFRESH' content='2'></HEAD></HEAD>";
    $progress = (int)(ftell($fp) * 100 / filesize($filename));
    echo "<h2>Processing Records. Please Wait...</h2>";
    echo "<div style=background:#9999FF;width:$progress%>$progress%</div>";
  }
  else
  {
    echo "<h2>Processing Finished</h2>";
    unset($_SESSION["offset"]); 
  }
}
?>

Its just an idea. Sing out it it works and report your views on the idea.