How to import a CSV file into MySQL using PHP

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

The CSV stands for "Comma-separated-values", as it uses a comma to separate values. This is a widely used file format that stores data in a tabular format. We generally use this in business, data-based applications for data exchange. Most organisations are web-based, so there may also be a common need to import data from a spreadsheet or a CSV file to a database. The data in a CSV is stored as sequences of records. With the help of PHP, we can easily store each comma-separated sequence in a 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 a step-by-step code explanation. First, we create a main file 'index.php' that we will call in 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 the 'index.php' page, we have used 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 the 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 a temporary file name and destination path as parameters. In this, we got the uploaded file extension using the PHP predefined function pathinfo() and the 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 cases. PHP returns an appropriate error code along with the file array. It is found in the file error segment or, $_FILES['file']['error'], that returns the error code if any problem is 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 a CSV file in PHP and store in MySQL

Here, we have merged all codes that were explained in detail above to read uploaded CSV file and import it in the 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 program to reverse a string
PHP remove last character from string
Export data from MySQL table to CSV file using PHP
PHP Fix: invalid argument supplied for foreach
PHP Connection and File Handling on FTP Server
Django Export Model Data to CSV
Convert Excel to CSV Python Pandas
Ajax live data search using jQuery PHP MySQL
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

How to import a CSV file into MySQL using PHP

index.php

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

Related Articles