How To Import CSV File Data Into Mysql Using PHP

If you are a developer then definitely you might have faced this. Many times you  need to import data from a CSV (comma separated value) file and insert it into your MySQL database.


Say for Example consider a case when you have many records in a CSV file and you need to import them into your MySQL database then you can’t  insert each n every single record manually as it will take too much time.


This case arises mostly when you want to import existing data in your website. In this tutorial I am going to  explain you how easily you can do that.

 Note : Many users had problem with previous script because of the incorrect file type or incorrect file data. So here I have updated the script for the easy understanding and performance improvement. I have attached some snaps for better understanding. If you have too many records, then you should increase the max_execution_time in php.ini file before running this script.

Case 1 : 

In this example, I assume that you have the correct columns data in your .csv file. In above case the ID represents the ID's of other table data (that might be exported). In this case these ID's values will be added in the table.


Case 2 :

As you can see in the above case, if your csv file hasn't ID column, then don't include it as blank. Simply remove it and keep only other fields(columns). In this case, table's auto_increment ID values will be added.


Case 3 :  Your .csv file must have correct data and first row as header(fields title).



Usage Notes: If your .csv file hasn't ID column, then just remove the "ID" from the INSERT query and remove last one column value(change this as per your file columns).

$col3   = $col[2];
$query = "INSERT INTO csvtbl(name,city) VALUES('".$col1."','".$col2."')";

This Updated Script will solve the previous issues of getting repeated (three times) values, skipping first row etc,. just make sure that you have followed the above cases correctly.


SQL query to create csvdata table:

CREATE TABLE IF NOT EXISTS `csvtbl`(
 `ID` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `city` varchar(50) NOT NULL,
 PRIMARY KEY (`ID`) )

csvimport.php File

<?php

//database connection details
$connect = mysql_connect('localhost','root','123456');

if (!$connect) {
 die('Could not connect to MySQL: ' . mysql_error());
}

//your database name
$cid =mysql_select_db('test',$connect);

// path where your CSV file is located
define('CSV_PATH','C:/wamp/www/');

// Name of your CSV file
$csv_file = CSV_PATH . "test.csv"; 


if (($handle = fopen($csv_file, "r")) !== FALSE) {
   fgetcsv($handle);   
   while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        for ($c=0; $c < $num; $c++) {
          $col[$c] = $data[$c];
        }

 $col1 = $col[0];
 $col2 = $col[1];
 $col3 = $col[2];
   
// SQL Query to insert data into DataBase
$query = "INSERT INTO csvtbl(ID,name,city) VALUES('".$col1."','".$col2."','".$col3."')";
$s     = mysql_query($query, $connect );
 }
    fclose($handle);
}

echo "File data successfully imported to database!!";
mysql_close($connect);
?>