How To Import CSV File Into MySQL Using PHP

In this php post, I am discussing how to import CSV file into a MySQL database table using PHP.CSV stands for “Comma Separated Values” and contains all data in comma separated.

It’s a very common task for every application which has too much database and needs to import into MySql.

I am using two files one is index.php file that will contain a UI part to show upload CSV file and import_csv.php file to connect MySQL and parse CSV data and store it into MySQL table.

Video Tutorial

If you are more comfortable watching a video that explains about How To Import CSV File Into MySQL Using PHP, then you should watch this video tutorial.

Normally, We need to import data into the database then we used the following file types.

  1. Xml file
  2. CSV file
  3. Excel file

To import data through CSV file into MySQL Database table. I am doing the following steps to import data from CSV file into MYSQL using PHP Script code.

You can also check another tutorial of Export/Import Data with PHP,

Steps to import CSV file into MySql database using PHP

Step 1: Create import_csv.php and put the below code into this file.

<?php
if(isset($_POST["submit"]))
{
$host="localhost"; // Host name.
$db_user="root"; //mysql user
$db_password=""; //mysql pass
$db='phpDB'; // Database name.
//$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
//mysql_select_db($db) or die (mysql_error());
$con=mysqli_connect($host,$db_user,$db_password,$db);
// Check connection
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


echo $filename=$_FILES["file"]["name"];
$ext=substr($filename,strrpos($filename,"."),(strlen($filename)-strrpos($filename,".")));

//we check,file must be have csv extention
if($ext=="csv")
{
  $file = fopen($filename, "r");
         while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
         {
            $sql = "INSERT into tableName(name,email,address) values('$emapData[0]','$emapData[1]','$emapData[2]')";
            mysqli_query($con, $sql);
         }
         fclose($file);
         echo "CSV File has been successfully Imported.";
}
else {
    echo "Error: Please Upload only CSV File";
}


}
?>

The below code is used for creating an interface to upload CSV file.

Step 2: Create a new index.php file to display the CSV upload form. You need to put the below code into index.php file.

<form action="import_csv.php" enctype="multipart/form-data" method="post">
<table border="1">
<tbody>
<tr>
<td colspan="2" align="center"><strong>Import CSV file</strong></td>
</tr>
<tr>
<td align="center">CSV File:</td>
<td><input id="file" name="file" type="file"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value="submit"></td>
</tr>
</tbody>
</table>
</form>

Importing a csv into MySQL via command line

we can also import a CSV file into a MySQL table using the command line:

Option 1: import CSV into MySQL

Import CSV file from a specific location.

LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/test.csv' 
INTO TABLE tableName 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

Option 2: import CSV into MySQL

mysql -uusername -ppassword --local-infile scrapping -e "LOAD DATA LOCAL INFILE 'CSVname.csv'  INTO TABLE table_name  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"

Option 3: Using the Mysql Dump command

mysqlimport --columns='head -n 1 $yourfile' --ignore-lines=1 dbname $yourfile`

Note: file is comma separated and is not semi-colon separated

6 thoughts on “How To Import CSV File Into MySQL Using PHP

  1. Thanks for the snippet. It is very helpful. I recommend including/adding the rest of the html page so that folks can just copy, paste and use it “as-is”.

    • Right now user can select csv file from any folder but as per your scenario, you can store csv file into root directory of project

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.