How to import a CSV file into MySQL using PHP

In this article, you will learn how to read CSV file using PHP and store the CSV data in MySQL database.

CSV file stands for "Comma-separated-values", as it uses comma to separate values. This is a widely used file format that stores data in a tabular format. The data in a CSV are stored as sequences of records. With the help of PHP, we can easily store each comma separated sequence in database row.

Suppose, we have the following data stored in a CSV file -

Anjali,This email address is being protected from spambots. You need JavaScript enabled to view it.,878433948
Priska,This email address is being protected from spambots. You need JavaScript enabled to view it.,493905490
Abhi,This email address is being protected from spambots. You need JavaScript enabled to view it.,403022139
Smith,This email address is being protected from spambots. You need JavaScript enabled to view it.,504903904

How to read CSV file in PHP and store in MySQL

Here is the script with step by step code explanation. First, we create a main file 'index.php', that we will call on the browser. In this, we take an HTML file upload form with a submit button.

index.php

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<form class="md-form"  action='#' method="post" enctype="multipart/form-data">
    <div class="file-field">
    <div class="btn btn-primary btn-sm float-left">
      <span>Choose file</span>
      <input type="file" name="file">
    </div>
    </div><br/><br/>
    <div class="form-group">
      <button type="submit" class="btn btn-primary">Submit</button>
    </div>
</form>
</body>
</html>

At the top of 'index.php' page, we have taken two PHP constants UPLOAD_DIR and MAXSIZE to define the upload directory and maximum allowed file size limit respectively.

define('UPLOAD_DIR',  '/var/uploaded_files/');
define('MAXSIZE', 7340032); // allow max 7 MB

Next, we have defined all the allowed file extensions in an array $ALLOWED_FILEEXT and all allowed MIME types in an array $ALLOWED_MIME.

$ALLOWED_MIME = array('text/comma-separated-values', 'text/csv', 'text/plain',
	'application/csv', 'application/excel',
	'application/vnd.ms-excel', 'application/vnd.msexcel');

Next, we create a function name 'allowedfile()' in the same 'index.php' file that accepts temporary file name and destination path as parameters. In this, we got the uploaded file extension using PHP predefined function pathinfo() and mime type of the uploaded file using mime_content_type() function. The allowedfile() function returns TRUE, if both file extension and MIME type of the uploaded file are allowed.

function allowedfile($tempfile, $destpath) {
 global $ALLOWED_MIME;
 $file_ext = pathinfo($destpath, PATHINFO_EXTENSION); 
 $file_mime = mime_content_type($tempfile);
 $valid_mime = in_array($file_mime, $ALLOWED_MIME);
 $allowed_file = ($file_ext == 'csv') && $valid_mime;
 return $allowed_file;
}

Next, create a function 'handleUpload()' and validate the file size and call the 'allowedfile()' function within it before moving the file to its destination.

function handleUpload() {
 $temp = $_FILES['file']['tmp_name'];
 $filename = basename($_FILES['file']['name']);
 $file_dest = UPLOAD_DIR. $filename;
 $is_uploaded = is_uploaded_file($temp); 
 $valid_size = $_FILES['file']['size'] <= MAXSIZE && $_FILES['file']['size'] >= 0;
	
 if ($is_uploaded && $valid_size && allowedfile($temp, $file_dest)) {
  move_uploaded_file($temp, $file_dest);
  insertCSV($file_dest);
 } else {
  $response = 'Error: uploaded file size or type is not valid.';
 }
 return $response;
}

Here, we have added different error handling cases and called 'handleUpload()' within one of the case. PHP returns an appropriate error code along with the file array. It is found in file error segment or, $_FILES['file']['error'], that returns the error code if any problem created during the file upload.

// Handle Error
if (!empty($_FILES)) {
 echo $error = $_FILES['file']['error'];
 switch($error) {
  case UPLOAD_ERR_OK:
  $response = handleUpload();
  break;
  case UPLOAD_ERR_INI_SIZE:
  $response = 'Error: file size exceeds the allowed.';
  break;
  default:
  $response = 'An unexpected error occurred; the file could not be uploaded.';
  break;
}
} else {
  $response = 'Please upload CSV file';
}
echo $response;

Next, we have created a function 'insertCSV()' to store data in the database. Here is the table structure, you can either copy paste this in your database or you can use your existing MySQL table.

CREATE TABLE IF NOT EXISTS `empdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

function insertCSV($filename){ 
$conn = mysqli_connect('hostname', 'username', 'password', 'database');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
if($fileHandle = fopen($filename, "r")){
 while(($row = fgetcsv($fileHandle, 0, ",")) !== FALSE)
 {
  $insert = "INSERT into empdata(name,email,phone) values('$row[0]','$row[1]','$row[2]')";
  if(mysqli_query($conn, $insert)){
   echo 'Data inserted successfully';
  }
  else{
   echo 'Error: '.mysqli_error($conn);
  }
 }
 fclose($fileHandle);
  echo "CSV File has been successfully Imported.";
}
}

Complete Code: How to read CSV file in PHP and store in MySQL

Here, we have merged all codes that explained in details above to read uploaded CSV file and import in database using PHP. Make sure to replace 'hostname', 'username', 'password' and 'database' with your credentials.

<?php 
// Define constants
define('UPLOAD_DIR', '/var/uploaded_files/');
define('MAXSIZE', 7340032); // allow max 7 MB

// allowed file types
$ALLOWED_MIME = array('text/comma-separated-values', 'text/csv', 'text/plain',
					'application/csv', 'application/excel',
					'application/vnd.ms-excel', 'application/vnd.msexcel');

// Handling Error
if (!empty($_FILES)) {
	echo $error = $_FILES['file']['error'];
	switch($error) {
		case UPLOAD_ERR_OK:
		$response = handleUpload();
		break;
		case UPLOAD_ERR_INI_SIZE:
		$response = 'Error: file size exceeds the allowed.';
		break;
		default:
		$response = 'An unexpected error occurred; the file could not be uploaded.';
		break;
	}
} else {
	$response = 'Please upload CSV file';
}
echo $response;					
			
// allowed file extension		
function allowedfile($tempfile, $destpath) {
	global $ALLOWED_MIME;
	$file_ext = pathinfo($destpath, PATHINFO_EXTENSION); 
	$file_mime = mime_content_type($tempfile);
	$valid_mime = in_array($file_mime, $ALLOWED_MIME);
	$allowed_file = ($file_ext == 'csv') && $valid_mime;
	return $allowed_file;
}	

// handling file upload		
function handleUpload() {
	$temp = $_FILES['file']['tmp_name'];
	$filename = basename($_FILES['file']['name']);
	$file_dest = UPLOAD_DIR. $filename;
	$is_uploaded = is_uploaded_file($temp); 
	$valid_size = $_FILES['file']['size'] <= MAXSIZE && $_FILES['file']['size'] >= 0;
	
	if ($is_uploaded && $valid_size && allowedfile($temp, $file_dest)) {
		move_uploaded_file($temp, $file_dest);
	    insertCSV($file_dest);
	} else {
		$response = 'Error: uploaded file size or type is not valid.';
	}
	return $response;
}	

// store data in database
function insertCSV($filename){ 
$conn = mysqli_connect('hostname', 'username', 'password', 'database');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
if($fileHandle = fopen($filename, "r")){
	while(($row = fgetcsv($fileHandle, 0, ",")) !== FALSE)
	{
	$insert = "INSERT into empdata(name,email,phone) values('$row[0]','$row[1]','$row[2]')";
 	if(mysqli_query($conn, $insert)){
	 echo 'Data inserted successfully';
	}
	else{
	 echo 'Error: '.mysqli_error($conn);
	}
	}
	fclose($fileHandle);
	echo "CSV File has been successfully Imported.";
}
}
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<form class="md-form"  action='#' method="post" enctype="multipart/form-data">
	<div class="file-field">
    <iv class="btn btn-primary btn-sm float-left">
      <span>Choose file</span>
      <input type="file" name="file">
    </div>
	</div><br/><br/>
    <div class="form-group">
      <button type="submit" class="btn btn-primary">Submit</button>
    </div>
</form>
</body>
</html>

PHP File Upload MIME Type Validation with Error Handler
How to display PDF file in PHP from database
File Upload Validation in PHP
PHP SplFileObject Standard Library
Simple File Upload Script in PHP
jQuery File upload progress bar with file size validation
Simple star rating system using PHP, jQuery and Ajax
JavaScript display PDF in the browser using Ajax call
jQuery loop over JSON result after AJAX Success