Inserting a csv array into mysql

[asaschool]

Hey Everyone,

I have a situation where I need a delimited file inserted into an existing mysql table. I think my issue is with blank fields. All my columns are set to be null just incase a specific item doesn't have all its values.  Here is what I am doing:

 

<?php require_once('DatabaseConnection.php'); ?>
<?php
//open file read only 
$file=fopen("../DelimitedData.txt","r"); 
while (!feof($file)) 
{ 
  $row=fgetcsv($file,40000, "|");
  
if (!$row[1]) {$row[1] = "0"; }
if (!$row[2]) {$row[2] = "0"; }
if (!$row[3]) {$row[3] = "0"; }
if (!$row[4]) {$row[4] = "0"; }
if (!$row[5]) {$row[5] = "0"; }

$insertSQL = sprintf("INSERT INTO table (Col1, Col2, Col3, Col4, Col5) VALUES ('%s','%s','%s','%s','%s')",$row[0], $row[1], $row[2],$row[3], $row[4]);

  mysql_select_db($database_DatabaseData, $DatabaseData);
  $Result1 = mysql_query($insertSQL, $DatabaseData) or die(mysql_error());
  
}
  

//close file 
fclose($file);

?>

 

Any suggestions on how I can do this more effeicently? This is my first crack at trying to do something like this, so please let me know if I am going in the wrong direction.

 

Thanks in advance

 

 

[discomatt]

having a maximum line length of 40,000 characters in your fgetcsv call could possibly be slowing the script down.

 

Other than that it look fine. The only reason I would change anything would be if you were parsing multiple files at a time

[asaschool]

The csv file I am using is very large and contains 62 columns of data and usually has around 1000 records. When I execute the script I get the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'data,data','data,data','data,data,data' at line 1

 

This error is occuring right after a series of 3 blank columns (data|data||||data|.....). That is why I am taking the approach to assigning a '0' to every null value in the array for each record, but I dont know if I am doing this properly.

 

Thanks again for your input and time.