Geek asks and answers

Import csv data to mysql database using php

Hi, I need to import a csv file of 15,000 lines. I use the fgetcsv function and parse each line. But every time I get a timeout error. The process is too slow, and data is partially imported. Is there a way to make 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 occurs every time it gets stuck between the import process and displays the following errors:

Error 1: Fatal error: maximum limit of 30 seconds exceeded on line 175.

Error 2:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to 'S', 0,0) 'on line 1

This error that I could not detect ...

.. 200 300 10000 .

+3
6

:

set_time_limit ( 0 )

. , , , !

.

, SQL- , .

, , , , , . , .

+3

500 csv, , mysql . .

:

  • 500 ,
  • csvimporter.php? offset = 500
  • 1. 500 , 500 .

- 0 :

set_time_limit(0);
+3

LOAD DATA INFILE, mysql, , fgetcsv

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

+2

@ php

ini_set('max_execution_time',0);
+1

, PHPMyAdmin CSV. import-a-csv-file-to-mysql-via-phpmyadmin

MySQL LOAD DATA LOCAL INFILE. . Mysql Docs

EDIT:

:

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

, SQL ..

+1

:
INSERT . INSERT , 15000 INSERT, !

::
, , mysqldump. insert 15000, :

:

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

, :

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

Inside the loop, check that the counter is 5000 or 10000 or 15000, then paste the data into the table vpireprot, and then set $ q to again INSERT INTO....
run the request and enjoy !!!

+1
source

More articles:


All Articles