Comma Delimited file

[Eljay]

I am trying to insert the values from a csv file into a database. However, the description field in the file have commas in them and they are messing up the file as it is being inserted to the DB.

How can I prevent this? Is there a more efficient way to write the sql statement without having to write out the entire array?

	$contents = file ('./FILE.csv');
for($i=1; $i<sizeof($contents); $i++)
{
$line = trim($contents[$i],'",');
$arr1 = explode(',', $line);
$arr = str_replace('"', '', $arr1);
$sql = mysql_query("insert into datatable(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."','".$arr[3]."','".$arr[4]."','".$arr[5]."','".$arr[6]."','".$arr[7]."','".$arr[8]."','".$arr[9]."','".$arr[10]."','".$arr[11]."','".$arr[12]."','".$arr[13]."','".$arr[14]."','".$arr[15]."','".$arr[16]."','".$arr[17]."','".$arr[18]."')");
}

[PFMaBiSmAd]

You should use fgetcsv() so that the strings, which are apparently enclosed in quotes, are not split on the commas within.

 

You can also use the mysql LOAD DATA LOCAL INFILE 'file_name' query.

 

If you have php 5.3, you can use str_getcsv and you can probably find existing user written functions that do the same.

 

[ballhogjoni]

$qry = "LOAD DATA LOCAL INFILE '" . $_POST['file_path'] . "'

    INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

    (".$_POST['value_1'].", ".$_POST['value_2'].", ".$_POST['value_3'].", ".$_POST['value_4'].", ".$_POST['value_5'].",

    ".$_POST['value_6'].", ".$_POST['value_7'].", ".$_POST['value_8'].",".$_POST['value_9'].", ".$_POST['value_10'].",

    ".$_POST['value_11'].",".$_POST['value_12'].",".$_POST['value_13'].",".$_POST['value_14'];

[Eljay]

Thanks PF and bal for your replies. I got the following code to work but I have 865 rows in my file and it only inserts 776 rows. Kinda strange. How do I omit the first row from being inserted?

 

$handle = fopen("./FILE.csv", "r");
while (($arr = fgetcsv($handle, 100000, ",", "\"")) !== FALSE) {

$sql = mysql_query("insert into datatable(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,
COL15,COL16,COL17,COL18,COL19) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."','".$arr[3]."','".$arr[4]."','".$arr[5]."',
'".$arr[6]."','".$arr[7]."','".$arr[8]."','".$arr[9]."','".$arr[10]."','".$arr[11]."','".$arr[12]."','".$arr[13]."',
'".$arr[14]."','".$arr[15]."','".$arr[16]."','".$arr[17]."','".$arr[18]."')");
}
fclose($handle);

[PFMaBiSmAd]

The rows that are not being inserted are either producing a query error or they are duplicates. You need to have error checking and error reporting/error logging logic in your code so that you know what is happening with the query. The most likely reason for a query error is some of your data contains single or double quotes (or other SQL special characters) that are breaking the query. You should be using mysql_real_escape_string() on each piece of string data.

 

To skip the first line, just execute a fgetcsv(....) statement before the while() loop.

[Eljay]

PF, it seems to be working now. I basically applied the mysql_real_escape_string() to all the items in the array before inserting into the DB. Is there a more efficient way to do the clean up of the array?

 

$p = $arr[15];
   $a = $arr[16];
   $b = $arr[17];
   $c = $arr[18];
.
.
.
$aclean = mysql_real_escape_string($a);
$bclean = mysql_real_escape_string($b);
$cclean = mysql_real_escape_string($c);
.
.
.

I do not understand how I can do a fgetcsv() before the while() loop.  I currently have it

while (($arr = fgetcsv($handle, 100000, ",", "\"")) !== FALSE) {

 

Do I need a for loop or something? how would I do that to omit the first line?

Thanks