Load a csv file into mysql via php

Question

Possible Duplicate:
Importing CSV data using PHP/MySQL

Can some please show me the easiest way to import a file into MySQL database via PHP form

Possible duplicate: [Importing CSV data using PHP/MySQL](http://stackoverflow.com/q/11448307/1456376) — insertusernamehere, Feb 02 '13 at 08:20

Jake Sankey · Answer 1 · 2013-02-07T19:05:48.443

Give this a try. This uses a function to read the lines of the file into an array of strings, then loops through each line and stores the column values to a table. Obviously you will need to insert your db connection settings and proper query.

function loadData()
{
   $lines = readInputFromFile("myCSV.csv");
   for ($i = 0; $i < count($lines); $i++)
   (
      $columns = explode(",", $lines[$i]);
      $conn = new PDO("mysql:host=YOUR_HOST;port=YOUR_PORT;dbname=YOUR_DBNAME", "UserName", "Password");

      $query = "INSERT INTO MYTABLE VALUES ('".$columns[0]."', '".$columns[1]."', '".$columns[2]."')";      
      $stmt = $conn->prepare($query);
      $stmt->execute();
   )
}

function readInputFromFile($file)
{
   $fh = fopen($file, 'r');
   while (!feof($fh))
   {
      $ln = fgets($fh);
      $parts[] = $ln;
   }

   fclose($fh);

   return $parts;
}

score 1 · Answer 2 · answered Feb 02 '13 at 08:17

1

It can be done by using the fgetcsv PHP function.

http://php.net/manual/en/function.fgetcsv.php

Afterwards, use the Mysql INSERT function in order to save that data in the Database.

answered Feb 02 '13 at 08:17

zurfyx

31,043
20
111
145

Afsar · Answer 3 · 2020-01-02T07:55:24.393

MySQL has a feature LOAD DATA INFILE, which allows to import a CSV file directly in a single SQL query.

Simple example:

          <?php
            $query = <<<eof
                LOAD DATA INFILE '$fileName'
                 INTO TABLE tableName
                 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
                 LINES TERMINATED BY '\n'
                (field1,field2,field3,etc)
            eof;

            $db->query($query);
            ?>

It's as simple as that.

No loops, no fuss. And much much quicker than parsing it in PHP.

MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope that helps author

Please give credit to the author if you copy an answer word by word: [Importing CSV data using PHP/MySQL](http://stackoverflow.com/q/11448307/1456376) — insertusernamehere, Feb 02 '13 at 08:21

score 0 · Answer 4 · answered Feb 02 '13 at 08:20

If you are looking for the easiest method to import csv file into mysql, then go with following:

Eg:

LOAD DATA LOCAL INFILE 'import.csv' INTO TABLE from_csv FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'  (si, distro, available);

For more reading:

http://www.techtrunch.com/linux/import-csv-file-mysql-table

score 0 · Answer 5 · answered Feb 02 '13 at 08:20

see the code below

csv2sql("pre_employee","ch_data.csv");

function csv2sql($table_name,$csvname)
{
$fl=fopen($csvname,r);
//if(!file_exists('a.txt'))
//{
    $flw=fopen("a.txt",'w');
//}
$sql='';
$i=2;
//echo $csvname.$data=fgetcsv($fl,1000,",");die();
while(($data=fgetcsv($fl,1000,","))!=false)
{
    $sql.="insert into ".$table_name." values('".$data[0]."','".$data[2]."','".$data[3]."');".chr(13);
    $i++;
}
//echo $sql;
fwrite($flw,$sql);
fclose($flw);
fclose($fl);
 }

a.txt is the filename saving your sql. nor import this txt file to your mysql database.

Load a csv file into mysql via php

5 Answers5