csv data import into mysql database using php

Question

Hi I need to import a csv file of 15000 lines. I m using the fgetcsv function and parsing each and every line.. But I get a timeout error everytime. The process is too slow and data is oly partially imported. Is there any way out to make the data import faster and more efficient?

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 everytime it gets stuck in between the import process and displays the following errors:

Error 1 : Fatal Error: Maximum time limit of 30 seconds exceeded at line 175.

Error 2 :

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

This error I m not able to detect...

The file is imported oly partial everytime.. oly around 200 300 lines out of a 10000 lines..

Answer 1

You can build a batch update string for every 500 lines of csv and then execute it at once if you are doing the mysql inserts on each line. It'll be faster.

Another solution is to read the file with an offset:

1. Read the first 500 lines,
2. Insert them to the database
3. Redirect to csvimporter.php?offset=500
4. Return the 1. step and read the 500 lines starting with offset 500 this time.

Another solution would be setting the timeout limit to 0 with:

set_time_limit(0);

Answer 2

Set this at the top of the page:

set_time_limit ( 0 )

It will make the page run endlessly. However, that is not recommended but if you have no other option then cant help!

You can consult the documentation here.

To make it faster, you need to check your the various SQL you are sending and see if you have proper indexes created.

If you are calling user defined functions and these functions are referring to global variables, then you can minimize the time take even more by passing those variables to the function and change the code so that the function refers to those passed variables. Referring to global variables is slower than local variables.