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, May 19 '12 at 05:53
can u please show your table definition(DDL) ? posibly $data wwas not properly escaped.\ — channa ly, May 19 '12 at 05: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, May 19 '12 at 06:01
hey Neal i think this link may help to you it had clear explanation......http://blog.lysender.com/2009/04/importing-huge-csv-file-via-php/ — Venkata Krishna, Oct 20 '12 at 07:32
http://stackoverflow.com/questions/4143938/import-csv-file-directly-into-mysql you can use load data infile as answered by pekka in above question. load data infile syntax: http://dev.mysql.com/doc/refman/5.1/en/load-data.html — srbhbarot, May 19 '12 at 06:09

score 4 · Answer 1 · answered May 19 '12 at 06:22

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.

score 1 · Answer 2 · answered May 19 '12 at 05:58

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

score 0 · Answer 3 · answered May 19 '12 at 12:58

$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);

Import a CSV file into MySQL using PHP

3 Answers3