Demo Download

In this tutorial, I will show you how to import CSV file data into MySQL database using PHP. Uploading the bulk data using CSV is a common practice in application development.

A CSV (comma-separated values) is a file format which stores data into table form similar to excel data. However, CSV file does not support any formatting or styling, it simply store data into plain text form.

All the values in CSV file are comma separated therefore, it is called CSV file format.

Now you must be thinking that why do we need CSV for data uploading, when we know that user can manually insert data into MySQL database.

When you are building application then you may need to add multiple data records exceeding thousands or even hundred of thousands.

No body can insert these huge amount of data manually, therefore we develop the program to import CSV file data into MySQL database using PHP.

Previously I have also shared a tutorial about how to create and export a CSV file using PHP. This means sometimes you also need to create a CSV file of your MySQL data and download that CSV file for further processing or backup purpose.

Steps to Import CSV File Data into MySQL Database using PHP

Follow the below steps to import CSV file data into MySQL database using PHP.

1. Create a Database with name allphptricks
2. Create a Table in DB with name import_csv_data
3. Create an index.php File
4. Create an style.css File in CSS Directory

1. Create a Database with name allphptricks

To create a allphptricks database run the following query in the MySQL.

CREATE DATABASE allphptricks;

2. Create a Table in DB with name import_csv_data

To create the table run the following query in the above created DB.

CREATE TABLE `import_csv_data` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I will use this table to store all CSV imported data, you can add/remove any column as per your application needs, but make sure that you upload CSV file with same number of column data.

Note: I have attached the SQL file of this table in download file of this tutorial.

3. Create an index.php File

Create an index.php file and paste the following code in the file.

<?php
$error = "";
$success = "";
$success_data ="";
// if form is submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST'){

    if($_FILES["upload_csv"]["error"] == 4) {
        $error.="<li>Please select csv file to upload.</li>";
    }else{
        $file_path = pathinfo($_FILES['upload_csv']['name']);
        $file_ext = $file_path['extension'];
        $file_tmp = $_FILES['upload_csv']['tmp_name'];
        $file_size = $_FILES['upload_csv']['size'];	 
        // CSV file extension validation
        if ($file_ext != "csv"){
            $error.="<li>Sorry, only csv file format is allowed.</li>";
          }
        // 1MB file size validation
        if ($file_size > 1048576) {
            $error.="<li>Sorry, maximum 1 MB file size is allowed.</li>";
          }
        if(empty($error)){
            // Number of rows in CSV validation (3 rows are allowed for now)
            $file_rows = file($file_tmp);
            if(count($file_rows) > 3){
                $error.="<li>Sorry, you can upload maximum 3 rows of data in one go.</li>";
            }
        }
    }
    // if there is no error, then import CSV data into MySQL Database
    if(empty($error)){
		// Include the database connection file 
		require_once 'dbclass.php';
		$db = new DB;
        $file = fopen($file_tmp, "r");
        while (($row = fgetcsv($file)) !== FALSE) {
			// Insert csv data into the `import_csv_data` database table
            $db->query("INSERT INTO `import_csv_data` (`id`, `name`, `email`) VALUES (:id, :name, :email)");
            $db->bind(":id", $row[0]);
			$db->bind(":name", $row[1]);
            $db->bind(":email", $row[2]);
            $db->execute();
          	$success_data .= "<li>".$row[0]." ".$row[1]." ".$row[2]."</li>";
        }
        fclose($file);
		$db->close();
		$success = "Following CSV data is imported successfully.";
    }
}
?>
<html>
<head>
<title>Demo Import CSV File Data into MySQL Database using PHP - AllPHPTricks.com</title>
<link rel='stylesheet' href='css/style.css' type='text/css' media='all' />
</head>
<body>

<div style="width:700px; margin:50 auto;">
<h1>Demo Import CSV File Data into MySQL Database using PHP</h1>

<?php
if(!empty($error)){
    echo "<div class='alert alert-danger'><ul>";
    echo $error;
    echo "</ul></div>";
	}
if(!empty($success)){
	  echo "<div class='alert alert-success'><h2>".$success."</h2><ul>";
    echo $success_data;
    echo "</ul></div>";
	}
?>

<form method="post" action="" enctype="multipart/form-data">
<input type="file" name="upload_csv" />
<br /><br />
<input type="submit" value="Upload CSV Data"/>
</form>

</div>
</body>
</html>

I am using the PDO Prepared Statements to insert data into database, you can learn more about PHP CRUD operations using PDO prepared statements.

Make sure that you update your database credentials in dbclass.php which is available in download of this tutorial. This dbclass.php is also available in my above PHP PDO prepared statements tutorial.

I am also validating the CSV file before insert into database, first make sure that file is selected, then check the file extension.

I placed maximum 1 MB file size limit for uploading and maximum 3 number of rows to add in CSV, you can skip this number of rows limit or you can utilize this limit by mentioning your own numbers like 1000 or 50000.

4. Create an style.css File in CSS Directory

Create an style.css file and paste the following code in the file.

body {
  font-family: Arial, sans-serif;
  line-height: 1.6;
}
input[type=submit] {
  font-family: Arial, sans-serif;
  font-weight: bold;
  color: rgb(255, 255, 255);
  font-size: 16px;
  background-color: rgb(0, 103, 171);
  width: 200px;
  height: 40px;
  border: 0;
  border-radius: 6px !important;
  cursor: pointer;
}
.alert {
  padding: 0.75rem 1.25rem;
  margin-bottom: 1rem;
  border: 1px solid transparent;
  border-radius: 0.25rem;
}
.alert ul {
padding: 0px 20px;
}
.alert-danger {
  color: #721c24;
  background-color: #f8d7da;
  border-color: #f5c6cb;
}
.alert-success {
  color: #155724;
  background-color: #d4edda;
  border-color: #c3e6cb;
}

The above CSS will give some nicer look to our upload button, error and success message of our application.

Now you can test the application, once the CSV data is imported into database then you can check your database table, it will have records like below.

Conclusion

I hope by now you know how to import CSV file data into MySQL database using PHP. By following all above steps anyone can easily upload and import a CSV file data into database using PHP.

If you found this tutorial helpful, share it with your friends and developers group.

I spent several hours to create this tutorial, if you want to say thanks so like my page on Facebook, Twitter and share it.

Demo Download

Facebook Official Page: All PHP Tricks

Twitter Official Page: All PHP Tricks