How to Import a CSV File Data Into Mysql data base 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.

SQL query to create a csv_data table:

CREATE TABLE IF NOT EXISTS `csv_tbl`(
 `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 (($getfile = fopen($csv_file, "r")) !== FALSE) { 
        $data = fgetcsv($getfile, 1000, ",");
        while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
         $num = count($data); 
         for ($c=0; $c < $num; $c++) {
             $result = $data; 
             $str = implode(",", $result); 
             $slice = explode(",", $str);
             $col1 = $slice[0]; 
             $col2 = $slice[1];
             $col3 = $slice[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!!"; 
mysql_close($connect); 
?>

Recommended Reading : How to Export a My SQL Data to CSV or Excel Format