Uploading a CSV file and importing to mysql giving an error

Question

I am using the following script to import data into my mysql database from CSV files. The CSV is setup like this :

Name  Postcode
fred  hd435hg
bob   dh345fj

Above is what it looks like in excel, in raw csv format viewed in notepad it looks like this :

name,postcode
frank,ng435tj

The problem I am having is for some reason the postcode column isnt getting imported at all, also the header row is getting imported as a record too, is it possible to make it skip the first row ?. I have been through the code and cant see why the postcode is not being pulled in, it is very odd.

        <?php
    //database connect info here

    //check for file upload
    if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])){

        //upload directory
        $upload_dir = "./csv";

        //create file name
        $file_path = $upload_dir . $_FILES['csv_file']['name'];

        //move uploaded file to upload dir
        if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {

            //error moving upload file
            echo "Error moving file upload";

        }

        //open the csv file for reading
        $handle = fopen($file_path, 'r');


        while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {

            //Access field data in $data array ex.
            $name = $data[0];
            $postcode = $data[1];

            //Use data to insert into db
            $sql = sprintf("INSERT INTO test (name, postcode) VALUES ('%s',%d)",
                        mysql_real_escape_string($name),
                        $postcode
                        );
            mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql"));
        }


        //delete csv file
        unlink($file_path);
    }
    ?>

score 2 · Accepted Answer · answered Jan 15 '12 at 00:15

2

Your CSV file seems to be a TSV file actually. It doesn't use commas, but tabulators for separating the fields.

Therefore you need to change the fgetcsv call. Instead of ',' use the tab:

 while (($data = fgetcsv($handle, 1000, "\t") ...

And to also skip the header row, add another faux fgetcsv before the while block:

 fgetcsv($handle);
 while (...) {

That will skip the first line. (A simple fgets would also do.)

Oh, just noticed: The postcode might also get dropped because you concat it into the string as decimal with the sprintf placeholder %d for $postcode. Should that field actually contain lettery, like in your example, then that wouldn't work. -- Though I presume that's just a wrong example printout.

answered Jan 15 '12 at 00:15

mario

144,265
20
237
291

Hi, sorry thats because I was opening it in excel, if I open it in notepad it looks like this – user1148760 Jan 15 '12 at 00:19
Uh oh, Okay. Then please ignore all my theories up to this point. – mario Jan 15 '12 at 00:20
But does the postcode actually contain letters? Or is it numeric as one would assume for postcodes? – mario Jan 15 '12 at 00:21
by the way uk postcodes have numbers and letters, so what would the correct sprintf placeholder be ? – user1148760 Jan 15 '12 at 00:22
You do need to use `%s` and the mysql escape function then, just like for the first field. (If you want to save some time in the future, this might be an good opportunity to research [PDO and bound parameters](http://stackoverflow.com/questions/1457131/php-pdo-prepared-statements). ***So much*** simpler than sprintf and manual escaping.) – mario Jan 15 '12 at 00:23
ahh ok, thanks, I will give that a go and let you know how it goes – user1148760 Jan 15 '12 at 00:24
When I press submit I get : INSERT INTO test (name, postcode) VALUES ('frank',ng435tj) – user1148760 Jan 15 '12 at 00:30
The problem is, all it adds to the table is a record with the name 'name' and nothing more. – user1148760 Jan 15 '12 at 00:31
Hurray !, sorted it, I didnt wrap the second %s I replaced in 's . it should have looked like this VALUES ('%s','%s') , but looked like this VALUES ('%s', %s) . Thanks :-) – user1148760 Jan 15 '12 at 00:36

score 0 · Answer 2 · answered Mar 08 '14 at 22:09

Try this one.

$file = $_FILES['file']['tmp_name'];

        $handle = fopen($file,"r");

        while(($fileop = fgetcsv($handle,1000,",")) !==false)
        {
            $username = $fileop[0];
            $name = $fileop[1];
            $address = $fileop[2];

            $sql = mysql_query("INSERT INTO ...... ");
        }

Uploading a CSV file and importing to mysql giving an error

2 Answers2