PHP, MySQL, Tips • 1 year ago

Efficiently Import CSV File Data into MySQL with PHP

Importing CSV data into MySQL doesn't have to be a time-consuming process. Discover how to efficiently handle this task with PHP in our step-by-step guide.

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:

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

 

Download Source

 

Categories

Tags