CSV ( Comma-Separated Values ) is a simple file format which is used to store data in tabular format like spreadsheets, database. Here, in this blog we will import the CSV file data into MYSQL database using PHP.

Let us consider we have the following “emp.csv” file data to be imported into our database.

The file should contain field name as first row. Here, emp_name, emp_dept, emp_number, emp_address are the fields name same as in the database table. We have created sample database named Evon.

First of all we need to create database and connect

<?php

	//database connection
	$conn = mysql_connect('localhost','root','','Evon');

	if (!$conn) {
	 die('Could not connect to MySQL: ' . mysql_error());
	}
?>

Next, create table employees into which data is to be inserted.

CREATE TABLE IF NOT EXISTS `employees` (
  `emp_name` varchar(50) NOT NULL ,
  `emp_dept` varchar(50) NOT NULL,
  `emp_number` int(11) NOT NULL,
  `emp_address` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Function to import data directly into the database:

// path to your CSV file
define('CSV_PATH','/opt/lampp/htdocs/');

// your CSV file name
$filename = CSV_PATH . "emp.csv"; 

if (($request = fopen($filename, "r")) !== FALSE) {
	fgetcsv($request);   
	while (($data = fgetcsv($request, 1000, ",")) !== FALSE) {
	     $num = count($data);
	     for ($c=0; $c < $num; $c++) {
	      	$col[$c] = $data[$c];
	     }

	      $col1 = $col[0];
	      $col2 = $col[1];
	      $col3 = $col[2];
	      $col4 = $col[3];
			   
	     // query to insert data into database
	     $query = "INSERT INTO employees(
                    emp_name,emp_dept,emp_number,emp_address,created,modified)
                    VALUES('".$col1."','".$col2."','".$col3."','".$col4."',NOW(),NOW())";
			
                $s = mysql_query($query, $conn );   //$conn is a db connection variable.
            }
      fclose($request);
}

echo "File data successfully imported to database!!";
mysql_close($conn);

Function to first upload the CSV file and then import the data into database:

				
$class="";
$msg='';
$err=0;
$target_dir = dirname(__FILE__)."/";
if(isset($_POST["import"]) && !empty($_FILES)) {
    $target_file = $target_dir . basename($_FILES["uploadFile"]["name"]);
    $fileType = pathinfo($target_file,PATHINFO_EXTENSION);
    if($fileType != "csv")  // check the file extension type.
    {
	$msg .= "Only CSV file is allowed.<br>";
	$err=1;
    }
    else
{
	if (move_uploaded_file($_FILES["uploadFile"]["tmp_name"], $target_file)) {
		$msg .="File uploaded successfully.<br>";
		if (($getdata = fopen($target_file, "r")) !== FALSE) {
		        fgetcsv($getdata);   
		        while (($data = fgetcsv($getdata)) !== FALSE) {
			$fieldCount = count($data);
			for ($i=0; $i < $fieldCount; $i++) {
			$colData[$i] = $data[$i];
		         }
			// query to insert data into database
			$emp_name = mysqli_real_escape_string($conn ,$colData[0]);
			$emp_dept = mysqli_real_escape_string($conn ,$colData[1]);
			$emp_number = mysqli_real_escape_string($conn ,$colData[2]);
			$emp_address = mysqli_real_escape_string($conn ,$colData[3]);
			$data[]= "'".$emp_name."','".$emp_dept."','".$emp_number."','".					$emp_address."',NOW(),NOW())";
			
 
		}
		$datarr = implode(",", $data);
		$query = "INSERT INTO employees(
			emp_name,emp_dept,emp_number,emp_address,created,modified) 
			VALUES  $datarr ;";
		$result = mysqli_query($conn ,$query);
		$msg .="Data imported successfully.";
		fclose($getdata);
	}
 
	} else {
		$msg .="Some error occurred.";
		$err=1;
	}
      }
}

HTML Code for view:

<form role="form" action="<?php echo $_SERVER['REQUEST_URI'];?>" method="post" 	enctype="multipart/form-data">

 <fieldset class="form-group">

	<div class="form-group">
	  <input type="file" name="uploadFile" id="uploadFile">
	   <label for="image upload" class="control-label">Only .csv file is allowed. </label>
	</div>

	<div class="form-group">
       <input type="submit" class="btn btn-warning" value="Import" name="import">
	</div>

  </fieldset>
</form>

Happy Coding!