PHP - CSV to MySQL

Hello I have the following code (attached) that did work about a 2 weeks ago to insert a .csv file into my table.

I came to use it today and the script just seems to timeout, I don't get any error message from mysql or anything. Checked my error log and nothing's being error'd out.

Can anyone help me? I'm not sure if my scripts changed (it shouldn't of).

Cheers


$databasehost = "localhost";
$databasename = "database";
$databasetable = "table";
$databaseusername ="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "CSVtoInsert.csv";
$addauto = 0;

$save = 1;
$outputfile = "output.sql";


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

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

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

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\n";
	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," \t");
	
	$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 = "insert into $databasetable (id, SID, SSID, SSTID, SubTypeName, PostCode, DistrictLetters, town, County, SPPostcodeSortNo, perLift, haulage, tonnage, onDelivery, onCollection) 
 values('','$linemysql');";
	else
		$query = "insert into $databasetable (id, SID, SSID, SSTID, SubTypeName, PostCode, DistrictLetters, town, County, SPPostcodeSortNo, perLift, haulage, tonnage, onDelivery, onCollection) values('$linemysql');";
	
	$queries .= $query . "\n";
 		
	@mysql_query($query);
}
die(mysql_error());
@mysql_close($con);

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

Open in new window

LiquidChromeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Avinash ZalaWeb ExpertCommented:
Try this attached code:

Hope this helps,
Addy
<?php 
	$file_location = "mydata.csv";
	if(file_exists($file_location))
	{
		$csv_fp = fopen ($file_location, "r");
		fGetS ($csv_fp, 4096);
		
		$rows = 0;
	
		$i = 0;
		set_time_limit(500);
		mysql_connect('hostname','username','password');
		mysql_select_db('dbname');

		while ($data = fgetcsv($csv_fp, 100000, ","))
		{
			
			//you will get CSV data in $data array try print that array;
						
			$csv = array();
			$rows++;
		
			
			$insertData = 'insert query here';
						//exit;
			//echo $insertusers;
			mysql_query($insertData) or die(mysql_error());
			
			//exit;
			
		//	mysql_query($insertquery) or die(mysql_error());
			
		
			//$csv[0]="'".''."'";
			//$csv[1]="'".'11'."'";
			//$csv[2]="'".addslashes($data[0])."'";
			echo '<pre>';
			//print_r($data);
			echo "</pre>";		
		}//while Over
		echo $rows;	
		fclose ($csv_fp);
	}
	else
	{
		echo "Not Exists";
	}


?>

Open in new window

0
ronan1979Commented:
a) I don't know how look like your CSV file. but be aware of multi-line in a cell

"line 1","cell on one line","experts-exchange" ,
"line 2","cell on more
than on line","experts-exchange" ,
"line 1","cell on one line","experts-exchange" ,

b) you are not in mode auto, it means you need to make sure that IDs are unique. or maybe be you want to REPLACE rather than INSERT new lines ?
0
SwafnilCommented:
As far as I can see there is no error in your posted code so the script should work. Could you clarify what you mean with "the script times out"? Do you get a PHP error message telling that the time limit was exceeded or just a blank page? Your script normally doesn't give any verbose output of what is currently being processed so it would be a good start to add some print() instructions to show how far the script really got.
Could you have a look at your output.sql file and see if any queries have been added to it after a script run?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

LiquidChromeAuthor Commented:
Thank's for all the responses so far.

I've done a little test, the .csv originally held around 40,000 rows. When I removed all but 10 rows the .csv went in perfectly fine.


So I'm guessing my PHP is timing the script out,
my servers settings
max_execution_time      300
max_input_time      600


What would you suggest I change these to,to allow for a huge amount of data to be inserted?

Cheers
0
SwafnilCommented:
I would increase the time limit before executing the inserts so that your script won't time out prematurely.

[CODE]
set_time_limit(1200);
[/CODE]

Adding all rows in one single statement (seperate each insert statement with ";") could also increase performance but the disadvantage of this would be that a single failing insert statement (because of whatever ...) would interrupt the entire insert operation.
0
ronan1979Commented:
1) load the csv file in a tmp directory
2) script cut your CSV File in 10 pages.
3) launch php file import.php?page=1. this script will insert the data then create thr HTML page answer with a refresh tag in it.
 



generated [import.php?page=1] HTML code
<html>
  <head>
<meta http-equiv="refresh" content="0;url=./import.php?page=2">
</head>
<body>
<p>This pasge will automatically redirect you.</p>

<p>
If nothing happen after 5 seconds then click on the following link: <a href="./import.php?page=2">./import.php?page=2</a>
  </body>
</html>

Open in new window

0
LiquidChromeAuthor Commented:
This is the error I'm getting from my soap execution by the way:

"Fri Apr 16 10:41:39 BST 2010:ERROR:Exception in request: java.net.SocketTimeoutException: Read timed out
Fri Apr 16 10:41:39 BST 2010:ERROR:An error occured [Read timed out], see error log for details
Fri Apr 16 10:41:39 BST 2010:INFO:Error getting response for [function0Binding.insertSupplierPricesCSV:Request 1]; java.net.SocketTimeoutException: Read timed out
"

So yeah It seems to be timing out. I'll get my server host to increase the timeout and see if this fixes it. Thanks for the help so far
0
LiquidChromeAuthor Commented:
how would I go about inserting data into one single statement instead of line by line? From the code I already have?

0
SwafnilCommented:
Just found a bug in your original code, it's in line 73: the die() call will interrupt script execution at this line, so nothing will ever be written to your output.sql file.

Please try the attached code and see if that fixes your problem.
set_time_limit(2400); // increase timeout to 20 minutes

$databasehost = "localhost";
$databasename = "database";
$databasetable = "table";
$databaseusername ="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "CSVtoInsert.csv";
$addauto = 0;

$save = 1;
$outputfile = "output.sql";


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

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

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

$size = filesize($csvfile);

if(!$size) {
        echo "File is empty.\n";
        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," \t");
        
        $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 = "insert into $databasetable (id, SID, SSID, SSTID, SubTypeName, PostCode, DistrictLetters, town, County, SPPostcodeSortNo, perLift, haulage, tonnage, onDelivery, onCollection) 
 values('','$linemysql');";
        else
                $query = "insert into $databasetable (id, SID, SSID, SSTID, SubTypeName, PostCode, DistrictLetters, town, County, SPPostcodeSortNo, perLift, haulage, tonnage, onDelivery, onCollection) values('$linemysql');";
        
        $queries .= $query . "\n";
                
        // we'll skip this line because all lines will be inserted in one statement:
        // @mysql_query($query);
}
//die(mysql_error()); as mentioned above, this line interrupts your script execution
if (mysql_query($queries)){
  print "Successfully inserted all lines";
} else {
  print "Failed with message: ".mysql_error()."!";
}
@mysql_close($con);

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.