[RESOLVED] Time out during BIG csv import

3x_NiHiLiSt

Hey guys,

Could one of you please cast your experienced eyes over this little issue for me please. I run an IP-2-City database on a few of my PHP-Nuke sites and I'm trying to develop a bit of code which will reliably import the huge csv file (updated monthly).

The .csv has around 1.5 million rows and for a few months, I've been splitting it down into 5 or 6 smaller files (>250,000 rows) because my script has 'time out' issues otherwise. I created the file from a couple of similar-intentioned scripts and some of my own work. Here is what I'm using....

<?php

include("mainfile.php");

if( !get_cfg_var('safe_mode') )
{
   @set_time_limit( 0 );
   @ignore_user_abort( 1 );
}

$db->sql_query( "DROP TABLE IF EXISTS _iplookup" );
$db->sql_query( "CREATE TABLE _iplookup (
             ip_from int(10) unsigned NOT NULL default '0',
             ip_to int(10) unsigned NOT NULL default '0',
             c2code char(2) NOT NULL default '',
             country varchar(64) NOT NULL default '',
             region varchar(64) NOT NULL default '',
             city varchar(64) NOT NULL default '',
             KEY code (ip_from, ip_to, c2code) ) ");


$file = "city-1.csv";
$opened = fopen( $file, 'r' );
$counter = 0;
while( !feof( $opened ) ) {

   $row = fgets( $opened, 4096 );
   $data = explode("\"", $row );

   $ip1      = $data[ 1 ];
   $ip2      = $data[ 3 ];
   $c2code   = $data[ 5 ];
   $c2code   = strtolower( $c2code );
   $country  = $data[ 7 ];
   $country  = strtolower( $country );
   $country  = ucwords( $country );
   $country  = addslashes( $country );
   $region  = $data[ 9 ];
   $region  = strtolower( $region );
   $region  = ucwords( $region );
   $region  = addslashes( $region );
   $city  = $data[ 11 ];
   $city  = strtolower( $city );
   $city  = ucwords( $city );
   $city  = addslashes( $city );

   $db->sql_query( "INSERT INTO _iplookup VALUES ( '$ip1', '$ip2', '$c2code', '$country', '$region', '$city' ) ");
   $counter++;
}

fclose( $opened );
echo $counter." lines written from $file";


?>

The call to include mainfile.php is because its a nuke based install and the mainfile is only used to render the $db and query calls (I'll happily paste if you think it's important).

Occasionally, it will get to the end of .php file and then I'll try to run the next one (as i said, its 5 or 6 - the only differnce is that subsequent scripts don't delete the table if present, just append). One of the subsequent files will generally time out and so I have to start from scratch again.

I'm pretty sure it would work if I just keep the files nice and small but in terms of 'quick' monthly updating, I'd like to just ftp the entire csv (one piece) and let the script run for as long as it needs to (I do it at very off-peak times, for traffic purposes).

Any help will be greatly appreciated. Let me know if you require any more info.

Many thanks, peace + out,

NiHiLiSt 🆒

HalfaBee

Why not make the script slipt the file into smaller chunks first and let the browser auto refresh at a suitable time period until they are all done.

You could also change the execution period in php.ini to a longer value, if your host allows it.

bradgrafelman

How is this being run? IIRC, Apache will still kill the connection after so long even if you turn off PHP's time limit.

At any rate - how is this being run? Via the CLI (i.e. a system call to '/path/to/php -f /path/to/myFile.php &') or not? My suggestion would be to use the CLI.

3x_NiHiLiSt

Hi,

The script is being called from the public http root. Just a .php file that i type in the browser. I don't think I have CLI access for most of my hosts (Im considering getting a reseller package - is it common policy to give CLI access?).

If there is no way of getting round the time out on apache/php is there a way to give it absolute prioirity? Hoping that it could complete task within time out limit if given full prioirty.....

Thanks for your help.

HalfaBee

I would try and break it up on the server and do it in minute batches, it will make your host happier than having a script running for a long time.
It also gives you a progress counter as well. 🙂

3x_NiHiLiSt

Thanks Halfabee,

I've considered trying that....as in cron jobs right? Does anyone know of an efficient php method of splitting a large (200meg plus) text/csv/whatever file?

The only function i have come across is the ftruncate() command but this seems to be by physical size and idealling I'd need to split the files accuratly by 'end of line'.

Or am I just asking too much (resource-wise)?

Thanks

HalfaBee

If you are on a *NIX host you can use the split command.

from memory split -n1000 filename will split the file into 1000 line parts.

or do it in php $chunks = array_chunk( $data , 1000 )

then just save the bits to files.

3x_NiHiLiSt

HalfaBee wrote:
or do it in php $chunks = array_chunk( $data , 1000 )

then just save the bits to files.

Thanks halfabee, I couldnt actually work out how to get the entire csv file as an array....but I have managed to split the file. I'm using the following code to break in down into smaller files ready for the installation routine (as you pointed out, I can get a progress counter this way).

<?php

$limit=10000;

if( !get_cfg_var('safe_mode') ) 
{ 
   @set_time_limit( 0 ); 
   @ignore_user_abort( 1 ); 
} 

$csv = @fopen("ip.csv", "r");
if ($csv) {
	$i=0;
	$d=0;
	$t=0;
   while (!feof($csv)) {
       $lines .= fgets($csv, 4096);
	   $i++;
	   	if($i==$limit) {
			$t=$t+$i;
			$i=0;
			$d++;
			$store = "ipdb/file$d.csv";
			$dump = fopen($store, "w");
  			fwrite($dump, "$lines");
			$lines="";
			fclose($dump);
			}
   }
   $t=$t+$i;
   fclose($csv);
   echo "Success, $t lines were split into $d sections.";
}
?>

Thanks for all your help, my code may not be pretty but it gets the job done 😃.

3x_NiHiLiSt

I've noticed a problem in my solution....

It only stores the lines once they get to it's 'limit'. i.e. if we had 1024 lines split up by 100, we'd have 10 lines of 100 and teh last 24 would be lost.

I got round it by adding another dump/store routine after the while () loop to scoop up remaining lines....

<?
   while (!feof($csv)) { 
       $lines .= fgets($csv, 4096); 
       $i++; 
           if($i==$limit) { 
            $t=$t+$i; 
            $i=0; 
            $d++; 
            $store = "ipdb/file$d.csv"; 
            $dump = fopen($store, "w"); 
              fwrite($dump, "$lines"); 
            $lines=""; 
            fclose($dump); 
            } 
   } 
            $d++; 
            $store = "ipdb/file$d.csv"; 
            $dump = fopen($store, "w"); 
              fwrite($dump, "$lines"); 
            $lines=""; 
            fclose($dump); 
   $t=$t+$i; 
   fclose($csv); 
   echo "Success, $t lines were split into $d sections."; 
} 
?>

Just FYI, its all sorted - thanks again.

HalfaBee

You load the file into an array using
www.php.net/file

One problem fixed. 🙂

Weedpacket

You could speed things up a little (and save a bit of memory into the bargain) if you didn't write things like

"$lines"

. That creates a new string, converts the value of $lines into a string, and inserts it into the new string. $lines is already a string - and even if it wasn't it would be converted to one as and when needed - so all you need to write here is

$lines

And I was wondering: does your db have any sort of bulk CSV import facility? Most of 'em do, and maybe you can find a way to use that (inserting records into a table one by one is really slow, because it has to do all its index updating and transaction logging and suchlike for every single record, instead of just once at the end).

3x_NiHiLiSt

Thanks Weedpacket, that makes sense. The quotes thing is something I've got all over my codeprojects, I'll have to go through it all now lol 😉 .

I'm not sure what you mean by if my db has a bulk import thing.... I have phpMyAdmin which can import a csv file but is limited to about 8meg or something (my file is over 200meg). I don't have physical access to the box if that's what you mean.

And yes, line by line is slow but it's working a charm now thanks to the help I've gotten. It stores over 2.5 million rows in just over 10 minutes. I'm a happy chappy 😃.

Thanks guys...y'all rock.