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” a file format where data is organized with commas as separators.

Additionally, I will provide instructions on how to perform the same CSV import into MySQL using the command line.

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

I’ve implemented a two-file approach, with 'index.php' handling the user interface for uploading CSV files and 'import_csv.php' responsible for establishing a connection to MySQL, parsing CSV data, and storing it in a 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,

• Import CSV File Into MySql Using PHP
• Exporting Data to Excel with PHP and MySQL
• Export Data to CSV and Download Using PHP and MySQL
• Export HTML Table Data to Excel, CSV, PNG and PDF using jQuery Plugin

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 the Command line

We can also import a CSV file into a MySQL table using the command line. Let’s open your command line terminal or shell.

• Windows: You can use Command Prompt or PowerShell.
• Linux, macOS: You can use Terminal in Unix-based systems.

Step 1: How To Login into the Mysql Server

You need to run the following command to access your MySQL database, and when prompted, enter your MySQL username and password:

mysql -u username -p

Replace username with your MySQL username.

Step 2: How To Select Mysql Database

Let’s select the database where you want to import the CSV file, if you haven’t created a database, then you need to create one using 'CREATE DATABASE your_database_name'.

USE your_database_name;

Option 1: Import a CSV file into MySQL Command LOAD DATA INFILE

use the LOAD DATA INFILE statement to import the CSV data. We’ll run the following command to load data.

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

• Replace C:/Users/userName/Downloads/test.csv with the actual path to your CSV file.
• Replace tableName with the name of the MySQL table where you want to import the data.
• Adjust the FIELDS TERMINATED BY, ENCLOSED BY, and LINES TERMINATED BY options according to your CSV file format.

Option 2: Import CSV into MySQL Using a Single Command

We can also create a single command that ll use username, password and other params in a one command.

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: Import file Using the mysqlimport Command

You can also import file using mysqlimport command while specifying columns and ignoring lines

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

Replace yourfile with the actual name of your CSV file and dbname with the name of your MySQL database.

This command will import data from the CSV file into the specified database while considering the column headers from the first line and ignoring the first line of data in the CSV file.

Note: The file uses commas as separators and is not delimited by semi-colons.

Exit MySQL

Exit the MySQL command line interface by typing:

EXIT;

Conclusion:

By following the steps outlined in this article, you can easily import CSV data into MySQL using a PHP script. You can also import a CSV file into MySQL via the command line is a powerful and efficient way to manage your database.