fgetcsv is not parsing the line properly

Question

This is the first line of my CSV (answer.csv):

Respondent Name,name,1

I am using the following code to parse and insert it into a db

if (($handle = fopen("answer.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo $num;
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;

    $query_add="INSERT INTO  answer (`AnswerValue`,`AnswerKey` ,`Question`)
    VALUES ($data[0],$data[1],$data[2])";
    echo $query_add;
    echo mysql_query($query_add) or die(mysql_error());
    #for ($c=0; $c < $num; $c++) {
    #    echo $data[$c] . "<br />\n";
    #}
  }
  fclose($handle);
  }

However, I get the following error because the line is not being parsed by ",":

Ouput :

1 fields in line 1: 

Respondent Name name    1

    INSERT INTO answer (`AnswerValue`,`AnswerKey` ,`Question`) 
       VALUES (Respondent Name  name    1,,)

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 'Name    name 1,,)' at line 2

For some reason, it shows there is only one field in line 1 whereas there are 3 fields if we look at the csv file...

Anyone knows why?

To avoid problems later, in case your data contains [something unexpected](http://xkcd.com/327/), please read [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and follow the advice given in the answers. — Ilmari Karonen, Jan 23 '14 at 07:51
Learn to use MySQLi or PDO with prepared statements, and bind variables — Mark Baker, Jan 23 '14 at 08:28

score 1 · Answer 1 · answered Jan 23 '14 at 07:41

1

Maybe your CSV file charset is invalid. Make sure it is encoded in UTF8.

answered Jan 23 '14 at 07:41

robi

113
8

im actually importing the file – gazubi Jan 23 '14 at 08:02

Alireza Fallah · Accepted Answer · 2014-01-23T07:39:02.793

0

you must put the values in the quotes.

try this :

$query_add="INSERT INTO  answer (`AnswerValue`,`AnswerKey` ,`Question`)
    VALUES ('".$data[0]."','".$data[1]."','".$data[2]."')";

and when I try your code, with my changes above,this is the result :

3

3 fields in line :
INSERT INTO answer (`AnswerValue`,`AnswerKey` ,`Question`) 
VALUES ('Respondent Name','name','1')

edited Jan 23 '14 at 07:39

answered Jan 23 '14 at 07:32

Alireza Fallah

4,609
3
31
57

doesn't work: I get the following: INSERT INTO answer (`AnswerValue`,`AnswerKey` ,`Question`) VALUES ('Respondent Name name 1','','')11 – gazubi Jan 23 '14 at 07:34
as you can see, the entire row is getting inserted in column 1 – gazubi Jan 23 '14 at 07:35
2

try creating the file again, maybe its corrupted . I dont know. your code is all right. – Alireza Fallah Jan 23 '14 at 07:43
1

you're right..i uploaded to file via ftp on my server and it wasn't being read properly. It works fine when I run it on a local file on my computer – gazubi Jan 23 '14 at 08:57

fgetcsv is not parsing the line properly

Ouput :

2 Answers2