Split csv file with php

Does anyone know of a reliable way to split a csv file using a php script rather than an actual software program or at least an idea of where to start to acheive this in php. I’ve searched for a tutorial or guide and couldn’t find any. I have used csv splitter and it does the job well but I need way of splitting using php only.

Cheers

Split? Could you elaborate?

I think I should add a little info too. I want to use this to split a csv which has a number of rows above the csv limit, 65,536 and import the split files into my mysql database. Hope this helps describe my requirements.

There is no CSV limit, CSV is plain text, the file can be as big as your system can handle. 65536 is the limit of 16 bits, perhaps you can explain exactly what you’re trying to do. Or if not, google “php file functions” for some methods to split your file up.

It looks more like a Excel sheet row limit :slight_smile:

Thanks for the feedback guys. Here what I am doing at the moment.

I am using a php script called ‘simplecsvimport’ to insert a csv file for a product data feed into a mysql database. This script works fine with csv files with about 3500 rows of data. However if I attempt to upload a csv with about 25000 rows I get an error:

’ Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 684 bytes)’

The script is as follows:

<?php

/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "localhost";
$databasename = "XXXX";
$databasetable = "XXXX";
$databaseusername ="XXXX";
$databasepassword = "XXXX";
$fieldseparator = ",";
$lineseparator = "\
";
$csvfile = "product.csv";
/********************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "output.sql";
/********************************/


if(!file_exists($csvfile)) {
	echo "File not found. Make sure you specified the correct path.\
";
	exit;
}

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

if(!$file) {
	echo "Error opening data file.\
";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\
";
	exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

	$lines++;

	$line = trim($line," \	");
	
	$line = str_replace("\\r","",$line);
	
	/************************************
	This line escapes the special character. remove it if entries are already escaped in the csv file
	************************************/
	$line = str_replace("'","\\'",$line);
	/*************************************/
	
	$linearray = explode($fieldseparator,$line);
	
	$linemysql = implode("','",$linearray);
	
	if($addauto)
		$query = "replace into $databasetable values('','$linemysql');";
	else
		$query = "replace into $databasetable values('$linemysql');";
	
	$queries .= $query . "\
";

	@mysql_query($query);
}

@mysql_close($con);

if($save) {
	
	if(!is_writable($outputfile)) {
		echo "File is not writable, check permissions.\
";
	}
	
	else {
		$file2 = fopen($outputfile,"w");
		
		if(!$file2) {
			echo "Error writing to the output file.\
";
		}
		else {
			fwrite($file2,$queries);
			fclose($file2);
		}
	}
	
}

echo "Found a total of $lines records in this csv file.\
";


?>

I was wondering if I could split the csv files into smaller chunks like csv splitter and then insert the data into my table.

The memory issue is due to you read the entire CSV into memory.

Instead of reading the full file, read a line at a time, create the insert segment of the query, append then move on.

Streaming the file if you will.


$query = 'INSERT INTO table (field, field, field)VALUES';

if(false !== ($handle = fopen('file.csv', 'r'))){
    while(false !== ($line = fgetcsv($handle, 0, ',', '"'))){
        $query .= sprintf(
            "('&#37;s', '%s', '%s'),",
            mysql_real_escape_string($line[0]),
            mysql_real_escape_string($line[1]),
            mysql_real_escape_string($line[2])
        );
    }
}

$query = rtrim($query, ',');

Thanks mate but not quite sure how this works, could you explain a little about how this code works so I can understand it and incorporate it into my script?

Cheers

Sure.

#base sql string
$query = 'INSERT INTO table (field, field, field)VALUES';

#open file
if(false !== ($handle = fopen('file.csv', 'r'))){
    #for every line in the csv file
    while(false !== ($line = fgetcsv($handle, 0, ',', '"'))){
        #append to the base sql string the record in sql format
        $query .= sprintf(
            "('&#37;s', '%s', '%s'),",
            mysql_real_escape_string($line[0]),
            mysql_real_escape_string($line[1]),
            mysql_real_escape_string($line[2])
        );
    }
}
#remove trailing ','
$query = rtrim($query, ',');

#which should generate something like...

/*
    INSERT INTO table (field, field, field)VALUES
    ('foo', 'bar', '23'),
    ('foo', 'bar', '23'),
    ('foo', 'bar', '23'),
    ('foo', 'bar', '23'),
    ('foo', 'bar', '23')
*/

Ah I see. So does this mean that I could in theory import any size csv file into my database?

I will now adjust my script to include this. Thanks again.

No problem. :slight_smile:

Any size? I don’t know about that, remember, you still have to store the composed SQL string…

A word of warning though, see http://twitter.com/AnthonySterling/status/11688738273 and http://twitter.com/AnthonySterling/status/11690890112 .

Will be careful. Cheers. Just adapting code now. Can I ask for advise if I get stuck?

No. :stuck_out_tongue:

Lol…ok.

Not sure if this is right but the script runs but doesn’t put anything into the database. Checked the connection and that is ok. Any ideas? No errors show so I’m guessing it’s running through the code.

&lt;?php
#base sql string
	$query = 'REPLACE INTO $databasetable (`merchant_id`, `merchant_name`, `aw_thumb_url`, `aw_product_id`, `merchant_product_id`, `brand_name`, `product_name`, `description`, `category_id`, `category_name`, `merchant_category`, `aw_deep_link`, `aw_image_url`, `search_price`, `delivery_cost`, `merchant_deep_link`, `ean`, `in_stock`, `model_number`, `promotional_text`, `upc`, `specifications`, `mpn`, `delivery_time`, `pre_order`, `rrp_price`, `store_price`, `product_type`, `parent_product_id`, `valid_to`, `valid_from`, `web_offer`)VALUES';

	#open file
	if(false !== ($handle = fopen('datafeed.csv', 'r'))){
    	#for every line in the csv file
    	while(false !== ($line = fgetcsv($handle, 0, ',', '"'))){
        	#append to the base sql string the record in sql format
        	$query .= sprintf(
            	"('&#37;s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'),",
            	mysql_real_escape_string($line[0]),
            	mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
				mysql_real_escape_string($line[0]),
            	mysql_real_escape_string($line[0])
        	);
    	}
	}
	#remove trailing ','
	$query = rtrim($query, ',');
?&gt;

The script I posted was merely an example, therefore lacked the full functionality of the one you posted.

There was nothing in mine that connected to a database or executed the composed query.

Try something more like this, however you’ll need to edit some parts, I’ll let you figure out which. :stuck_out_tongue:


&lt;?php
#create connection
$con = mysqli_connect('server', 'username', 'password', 'database', 3306);

#check connection
if(false === is_resource($con)){
    echo 'Cannot connect: ', mysqli_connect_error();
    exit;
}

#base sql string
$query = 'REPLACE INTO table (`merchant_id`, `merchant_name`, `aw_thumb_url`, `aw_product_id`, `merchant_product_id`, `brand_name`, `product_name`, `description`, `category_id`, `category_name`, `merchant_category`, `aw_deep_link`, `aw_image_url`, `search_price`, `delivery_cost`, `merchant_deep_link`, `ean`, `in_stock`, `model_number`, `promotional_text`, `upc`, `specifications`, `mpn`, `delivery_time`, `pre_order`, `rrp_price`, `store_price`, `product_type`, `parent_product_id`, `valid_to`, `valid_from`, `web_offer`)VALUES';

#open file
if(false !== ($handle = fopen('datafeed.csv', 'r'))){
    #for every line in the csv file
    while(false !== ($line = fgetcsv($handle, 0, ',', '"'))){
        #append to the base sql string the record in sql format
        $query .= sprintf(
            "\\r\
('&#37;s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'),",
            mysqli_real_escape_string($con, $line[0]),
            mysqli_real_escape_string($con, $line[1]),
            mysqli_real_escape_string($con, $line[2]),
            mysqli_real_escape_string($con, $line[3]),
            mysqli_real_escape_string($con, $line[4]),
            mysqli_real_escape_string($con, $line[5]),
            mysqli_real_escape_string($con, $line[6]),
            mysqli_real_escape_string($con, $line[7]),
            mysqli_real_escape_string($con, $line[8]),
            mysqli_real_escape_string($con, $line[9]),
            mysqli_real_escape_string($con, $line[10]),
            mysqli_real_escape_string($con, $line[11]),
            mysqli_real_escape_string($con, $line[12]),
            mysqli_real_escape_string($con, $line[13]),
            mysqli_real_escape_string($con, $line[14]),
            mysqli_real_escape_string($con, $line[15]),
            mysqli_real_escape_string($con, $line[16]),
            mysqli_real_escape_string($con, $line[17]),
            mysqli_real_escape_string($con, $line[18]),
            mysqli_real_escape_string($con, $line[19]),
            mysqli_real_escape_string($con, $line[20]),
            mysqli_real_escape_string($con, $line[21]),
            mysqli_real_escape_string($con, $line[22]),
            mysqli_real_escape_string($con, $line[23]),
            mysqli_real_escape_string($con, $line[24]),
            mysqli_real_escape_string($con, $line[25]),
            mysqli_real_escape_string($con, $line[26]),
            mysqli_real_escape_string($con, $line[27]),
            mysqli_real_escape_string($con, $line[28]),
            mysqli_real_escape_string($con, $line[29]),
            mysqli_real_escape_string($con, $line[30]),
            mysqli_real_escape_string($con, $line[31])
        );
    }
}

#remove trailing ','
$query = rtrim($query, ',');

#execute query
echo 'Query ... ', mysqli_query($con, $query) ? 'Failed (' . mysqli_error() . ')' : 'Succeeded' ;
?&gt;

DOH, just realised why. Thanks

Bugger…an error.

‘Warning: mysqli_real_escape_string() expects parameter 1 to be string’

Also why mysqli not mysql?

It does? Not according to the manual:confused: