Efficiently Import CSV File Data into MySQL with 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.

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.

If you have too many records, then you should increase the max_execution_time in php.ini file before running this script.


Case : 1

how to import csv file into mysql using php

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

how to import csv file into mysql using php

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:

 `name` varchar(50) NOT NULL,
 `city` varchar(50) NOT NULL,


csvimport.php File


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

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

if (($handle = fopen($csv_file, "r")) !== FALSE) {
   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 );

echo "File data successfully imported to database!!";


Download Source


Ketan Patel

As a backend and ecommerce developer, I have extensive experience in implementing robust and scalable solutions for ecommerce websites and applications. I have a deep understanding of server-side technologies and have worked with various programming languages. I have experience in integrating with payment gateways, and other third-party services.