CODEWITHSUNDEEP
phpmysqlfgetcsv
Neal
Neal

Reputation: 527

csv data import into mysql database using php

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..

Upvotes: 3

Views: 3306

Answers (6)

Eric Cope
Eric Cope

Reputation: 877

If this is a one-time exercise, PHPMyAdmin supports Import via CSV. import-a-csv-file-to-mysql-via-phpmyadmin

He also notes the user of leveraging MySQL's LOAD DATA LOCAL INFILE. This is a very fast way to import data into a database table. load-data Mysql Docs link

EDIT:

Here is some pseudo-code:

// perform the file upload 
$absolute_file_location = upload_file();

// connect to your MySQL database as you would normally
your_mysql_connection();

// execute the query
$query = "LOAD DATA LOCAL INFILE '" . $absolute_file_location . 
         "' INTO TABLE `table_name`
         FIELDS TERMINATED BY ','
         LINES TERMINATED BY '\n'
         (column1, column2, column3, etc)";
$result = mysql_query($query);

Obviously, you need to ensure good SQL practices to prevent injection, etc.

Upvotes: 1

Alireza
Alireza

Reputation: 6848

PROBLEM:
There is a huge performance impact on the way you INSERT data into your table. For every one of your records you send an INSERT request to the server, 15000 INSERT requests that's huge!

SOLUTION::
Well you should group your data like the way mysqldump does. In your case you just need three insert statement not 15000 as below:

before the loop write:

$q = "INSERT into vpireport(item_code,company_id,purchase,purchase_value)values";

And inside the loop concatenate the records to the query as below:

$q .= "($data[0],$data[1],$data[2],$data[3]),";

Inside the loop check that the counter is equal to 5000 OR 10000 OR 15000 then insert data to the vpireprot table and then set the $q to INSERT INTO... again.
run the query and enjoy!!!

Upvotes: 1

Sachin Puri
Sachin Puri

Reputation: 886

You can make use of LOAD DATA INFILE which is a mysql utility, this is much faster than fgetcsv

more information is available on

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 2

sree
sree

Reputation: 498

simply use this @ the beginning of your php import page

ini_set('max_execution_time',0);

Upvotes: 1

somnath
somnath

Reputation: 1335

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.

Upvotes: 3

Taha Paksu
Taha Paksu

Reputation: 15616

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

Upvotes: 3

Related Questions