A simple way to import CSV file in MySql database table using PHP

By: Array99      Posted On: 09 Oct, 2018      Category: PHP

In this post, it is explained that, how can a user import records from a CSV file. According to below script functionality, a user is able to import employee information in employee table. A sample CSV file available here for this example. Please click here to download

CSV (Comma Separated Values) is a file format used to store tabular data. Files in the CSV format can be imported to and exported from programs that store data in tables, such as Microsoft Excel or OpenOffice Calc very easy way. There are two primary reasons CSV files are used online.

  • CSV files are plain-text files, which makes them easy to create.
  • CSV files are plain-text files that makes the data easy to import into any spreadsheet program or database regardless of what type of computer or software program you are using.

 It is very common task for every application to import records from CSV file in MySql table. Import CSV into MySQL tables helps you to save time and avoid repetitive work.

Let’s Get Started

Create a database with name testdb and create a employees table in this database by executing below SQL query.

CREATE TABLE `employees` (
  `id` int(10) UNSIGNED NOT NULL,
  `emp_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact_no` varchar(15) NOT NULL,
  `status` tinyint(3) UNSIGNED NOT NULL COMMENT '0=>Inactive, 1=>Active',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `employees` ADD PRIMARY KEY (`id`);

ALTER TABLE `employees` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

Create a file with import-form.php for HTML inputs and put below code.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Example of Bootstrap 3 Static Navbar</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

</head> 
<body>

<div class="container">
	<?php
	if(isset($_SESSION['msg'])){
		echo '<div class="alert alert-success">'.$_SESSION['msg'].'</div>';
		unset($_SESSION['msg']);
	}
	?>
    <form method="post" action="import-csv-data.php" enctype="multipart/form-data">
		<h3>Import Data From CSV File</h3>
		<div class="form-group">
			<input type="file" id="csvFile" name="csvFile">
		</div>
		<button type="submit" class="btn btn-primary">Import</button>
	</form>
</div>
</body>
</html>

Create a file import-csv-data.php where data post and check CSV file uploaded with function is_uploaded_file().

<?php	
session_start();
$DBconfig = require "DBconfig.php";
require "ImportCsvFile.php";
require "DBConnection.php";

if (is_uploaded_file($_FILES['csvFile']['tmp_name'])) {
	$dbObject = new DBConnection($DBconfig);
	$db = $dbObject->createConnection();
	$importObj = new ImportCsvFile($db, $_FILES['csvFile']);	
	$importObj->importFile();
	$_SESSION['msg'] = "Records has been imported successfully";
	header("Location: import-view.php");
}else{
	$_SESSION['msg'] = "Unable to upload file.";
	header("Location: import-view.php");	
}
?>

Create a DBconfig.php for keeping database credentials put below code in this file.

<?php	

return array(
	'db' => array(
		'host' => 'localhost',
		'dbUsername' => 'root',
		'dbPassword' => '',
		'dbName' => 'test',
	),
)
?>

Now, create a file DBConnection.php to established a database connection with MySql and put below code in this file.

<?php
class DBConnection
{
	private $DBconfig;
	
	public function __construct($DBconfig)
	{
		$this->DBconfig = $DBconfig;
	}
	
	public function createConnection()
	{
		$db = new mysqli($this->DBconfig['db']['host'], $this->DBconfig['db']['dbUsername'], $this->DBconfig['db']['dbPassword'], $this->DBconfig['db']['dbName']);
		if ($db->connect_error) {
			die("Unable to connect database: " . $db->connect_error);
		}
		return $db;
	}
}
?>

Now, create a file ImportCsvFile.php where script is keeping for importing CSV file in table. Put below code in this file. In below script:

  • The fopen() function is used to open the CSV file in read-only mode.
  • The fgetcsv() function is used to parse the records from the open CSV file. This function is called in the while loop to parse data from CSV file line by line.
<?php

class ImportCsvFile
{
	private $FILES;
	
	private $db;
	
	private $STATUS = array('Active' => 0, 'Inactive' => 1);
	
	public function __construct($db, $files)
	{
		$this->db = $db;
		$this->FILES = $files;
	}
	
	public function importFile()
	{
		//open uploaded csv file with read only mode
		$csvFile = fopen($this->FILES['tmp_name'], 'r');
		
		//skip first line
		fgetcsv($csvFile);
		
		while (($line = fgetcsv($csvFile)) !== FALSE){
			$this->saveRecord($line);
		}
		fclose($csvFile);
	}
	
	private function saveRecord($row)
	{
//Check whether a record already exists or not
		$query = $this->db->query("SELECT email FROM employees WHERE email = '".$row[1]."' ORDER BY id DESC LIMIT 1");
		if($query->num_rows > 0)
		{ 
			 $this->db->query("UPDATE employees SET emp_name = '".$row[0]."', contact_no = '".$row[2]."', updated_at = NOW(), status = '".$this->STATUS[$row[3]]."' WHERE email = '".$row[1]."'");
		}
		else
		{

			$this->db->query("INSERT INTO employees (emp_name, email, contact_no, created_at, status) VALUES ('".$row[0]."','".$row[1]."','".$row[2]."',NOW(),'".$this->STATUS[$row[3]]."')");
		}
	}
}
?>

Hope this script will help you to implement the import feature in your web project easily.  

You may like: