Import a CSV file into MySQL using PHP

Question

I'm trying to import CSV data into a MySQL database using the fgetcsv function.

if(isset($_POST['submit'])) {
     $fname = $_FILES['sel_file']['name'];
     $var = 'Invalid File';
     $chk_ext = explode(".",$fname);

     if(strtolower($chk_ext[1]) == "csv") {
         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");
     $res = mysql_query("SELECT * FROM vpireport");
     $rows = mysql_num_rows($res);
     if($rows>=0) {
        mysql_query("DELETE FROM vpireport") or die(mysql_error());

        for($i =1;($data = fgetcsv($handle, 10000, ",")) !== FALSE; $i++) {
            if($i==1)
            continue;
            $sql = "INSERT into vpireport
                                    (item_code,
                                     company_id,
                                     purchase,
                                     purchase_value) 
                                     values
                                    (".$data[0].",
                                     ".$data[1].",
                                     ".$data[2].",
                                     ".$data[3].")";
            //echo "$sql";
            mysql_query($sql) or die(mysql_error());
        }
    }

     fclose($handle);
    ?>
     <script language="javascript">
     alert("Successfully Imported!");
     </script>
     <?
 }

The problem is it gets stuck in between the import process and displays 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 'S',0,0)' at line 1

The file is imported only partially each time. Only between 200-300 lines out of a 10000 line file are imported.

Here is the DDL of my table:

create table vpireport (
    id int not null auto_increment,
    item_code int,
    company_id int,
    purchase double,
    primary key(id),
    foreign key(company_id) references users(userid)
);

I haven't been able to find the problem so far, any help appreciated. Thanks.

i think it is not able to segregate comma separated values from data. can u post a sample data field? — sree
– sree, Commented May 19, 2012 at 5:53
can u please show your table definition(DDL) ? posibly $data wwas not properly escaped.\ — channa ly
– channa ly, Commented May 19, 2012 at 5:56
It looks like you have a ' in one of your fields. You should always use mysql_real_escape_string on strings that you interpolate into queries. — Corbin
– Corbin, Commented May 19, 2012 at 6:01
stackoverflow.com/questions/4143938/… you can use load data infile as answered by pekka in above question. load data infile syntax: dev.mysql.com/doc/refman/5.1/en/load-data.html — srbhbarot
– srbhbarot, Commented May 19, 2012 at 6:09
hey Neal i think this link may help to you it had clear explanation......blog.lysender.com/2009/04/importing-huge-csv-file-via-php — Venkata Krishna
– Venkata Krishna, Commented Oct 20, 2012 at 7:32

jlengstorf · Accepted Answer · 2012-05-19 06:22:23Z

You probably need to escape quotes, which you could accomplish using PDO and prepared statements.

I've skipped most of your code in the example for brevity and just focused on the for loop.

<?php

    // Use PDO to connect to the DB
    $dsn = 'mysql:dbname=YOUR_DB;host=localhost';
    $user = 'DB_USERNAME';
    $password = 'DB_PASSWORD';

    try {
        $dbh = new PDO($dsn, $user, $password);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }

    for($i =1;($data = fgetcsv($handle, 10000, ",")) !== FALSE; $i++) {
        // The query uses placeholders for data
        $sql = "INSERT INTO vpireport
                    (item_code,company_id,purchase,purchase_value) 
                VALUES
                    (:item_code,:company_id,:purchase,:purchase_value)";
        $sth = $dbh->prepare($sql);

        // The data is bound to the placeholders
        $sth->bindParam(':item_code', $data[0]);
        $sth->bindParam(':company_id', $data[1]);
        $sth->bindParam(':purchase', $data[2]);
        $sth->bindParam(':purhcase_value', $data[3]);

        // The row is actually inserted here
        $sth->execute();
        $sth->closeCursor();
    }

That won't get rid of any problem characters, though, so you may want to look at some kind of data sanitization if that poses a problem.

MilMike · Accepted Answer · 2012-05-19 05:58:38Z

1

uncomment the //echo "$sql"; and look what is the last query (with error) - it may be that the csv data contains strange characters or the query is cut off.

BTW: you can also import csv file by mysql:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

answered May 19, 2012 at 5:58

MilMike

12.9k16 gold badges69 silver badges85 bronze badges

Comments

user1405040 · Accepted Answer · 2012-05-19 12:58:23Z

0

$row = 1;

if (($handle = fopen("albums.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ',','"')) !== FALSE) {
            if($row!=1){    
                $num = count($data);
                $albumIndex=0;

                //Insert into tbl_albums 

                $sqlAlbums="INSERT INTO tbl_albums(albumName) VALUES ('".$data[$albumIndex]."')";
                $resultAlbums=mysql_query($sqlAlbums);



                }
            }
            $row++;

            }
        }
fclose($handle);

answered May 19, 2012 at 12:58

user1405040

1

Collectives™ on Stack Overflow

Import a CSV file into MySQL using PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest