LiquidChrome
asked on
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
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);
}
}
}
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 ?
"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 ?
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?
Could you have a look at your output.sql file and see if any queries have been added to it after a script run?
ASKER
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
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
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.
[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.
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.
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>
ASKER
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.SocketTimeoutExce ption: 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.insertSu pplierPric esCSV:Requ est 1]; java.net.SocketTimeoutExce ption: 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
"Fri Apr 16 10:41:39 BST 2010:ERROR:Exception in request: java.net.SocketTimeoutExce
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.insertSu
"
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
ASKER
how would I go about inserting data into one single statement instead of line by line? From the code I already have?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hope this helps,
Addy
Open in new window