Importing CSV file into SQL using PHP not working

Question

I am trying to import a CSV file into my SQL database. This is what I have:

if ($_FILES[csvFile][size] > 0)
{ 
$file = $_FILES[csvFile][tmp_name]; 
$handle = fopen($file,"r"); 

do { 
    if ($data[0]) 
    { 

    $insert_query = "REPLACE INTO `teacherNames` SET 
    `schoolName` = '".addslashes($schoolname)."',
    `teacherName` = '".addslashes($data[0])."'
    ;";  

        $result = mysql_query($insert_query);
        echo $insert_query; -- SEE RESULTING QUERY BELOW
        echo $data[0]." added\n<br />";
    } 
} 
while ($data = fgetcsv($handle,1000,",","'"));

The CSV file has 3 records and it looks correct. The procedure works to an extent but for some reason it is not reading the CSV file correctly and the resulting query is like this:

REPLACE INTO `teacherNames` SET `schoolName` = 'Brooks', `teacherName` = 'RMG JMC PMC';

When I would expect to get 3 separate queries - one for each record. It does not seem to be reading the CSV file as 3 separate records but as 1. Can anyone see why?

UPDATE:

The CSV contents are:

RMG
JMC
PMC

$data will be null the first time through your loop because you're using a do/while - the while part of the expression isn't evaluated until one full time through the loop. — WWW
– WWW, Commented Aug 11, 2014 at 13:54
the csv is really separated by commas?? You can open the file and then explode ";" — Julio Marins
– Julio Marins, Commented Aug 11, 2014 at 14:03
@JulioMarins there does appear to be a CSV issue. I created it in Excel (for the Mac) and then copied it. The new copy does not work but it looks the same in Excel and a text editor. Strange. — RGriffiths
– RGriffiths, Commented Aug 11, 2014 at 14:09
Why do you want to use REPLACE at all? Is (schoolName, teachername) primary key? — VMai
– VMai, Commented Aug 11, 2014 at 14:13

fbiazi · Accepted Answer · 2014-08-11 14:26:09Z

2

The anwer of Julio Martins is better if you have the file on the same computer as the MySQL server.

But if you need to read the file from inside the PHP, there is a note from PHP.NET at http://php.net/manual/en/function.fgetcsv.php :

Note: If PHP is not properly recognizing the line endings when reading files either on or created by a Macintosh computer, enabling the auto_detect_line_endings run-time configuration option may help resolve the problem.

How is the line endings on your file? As all lines are being read as one, it can be your case i guess.

To turn auto_detect_line_endings on, use ini_set("auto_detect_line_endings", true); as said Pistachio at http://php.net/manual/en/filesystem.configuration.php#107333

answered Aug 11, 2014 at 14:26

fbiazi

3872 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RGriffiths Over a year ago

That is superb - just the job. Thanks so much. Added that line and all is good.

Alexander Kononenko · Accepted Answer · 2014-08-11 13:56:56Z

1

Use while instead do-while:

while ($data = fgetcsv($handle,1000,",","'")) {
    //...
}

answered Aug 11, 2014 at 13:56

Alexander Kononenko

2,1321 gold badge11 silver badges18 bronze badges

5 Comments

RGriffiths Over a year ago

Thanks but this does the same.

RGriffiths Over a year ago

It appears to have something to do with the CSV file itself. If I create it again it seems to work. Maybe a Mac Excel thing.

Alexander Kononenko Over a year ago

Look toward line breaks of your CSV file.

RGriffiths Over a year ago

Is there any way of telling?

Alexander Kononenko Over a year ago

Look at this: nicercode.github.io/blog/2013-04-30-excel-and-line-endings "On a Mac, Excel produces csv files with the wrong line endings, which causes problems for git (amongst other things)." It is possible, that fgetcsv() can't detect line endings properly from your CSV.

Julio Marins · Accepted Answer · 2014-08-11 14:37:16Z

1

Try load data:

LOAD DATA INFILE '{$filepath}'
INTO TABLE '{$table}'
FIELDS TERMINATED BY ','

It is cleaner

edited Aug 11, 2014 at 14:37

answered Aug 11, 2014 at 13:59

Julio Marins

10.7k8 gold badges52 silver badges54 bronze badges

1 Comment

fbiazi Over a year ago

If he can use this query it is indeed better. But he is using , as field separator on the while line (your code uses ;) and all lines must be read (your code ignores the first one). Trivial, but fixing it to the question needs won't hurt.

Collectives™ on Stack Overflow

Importing CSV file into SQL using PHP not working

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related