Upload CSV File and Insert Records in Database Table Using PHP and MySQL

Import CSV File into MySQL Database Table using PHP. This is very easy in PHP, we can give an upload interface from where the user will upload the CSV file and we can read Records from the CSV file and then we can insert the records one by one into Table.

In PHP, It is common to insert data into a database table from form input. But sometimes, when we need to insert thousands of records at a time, we don't do it from the front-end input form. It will be very time-consuming. We enter our data into a CSV file and then we import this CSV file into the database Table. In the below example, we will see how to Import a CSV file into a database table. Before Starting, we need to create a Database and Table First.

CREATE DATABASE csv_import;

CREATE TABLE users( 
   id INT(10) NOT NULL AUTO_INCREMENT,
   name VARCHAR(200) NOT NULL,
   address VARCHAR(200) NOT NULL,
   PRIMARY KEY(id) );

Code Demo of Importing CSV File in Database Table

	<h2>Import CSV file into sql table</h2>
	<form method="post" action="" enctype="multipart/form-data">
	<b>Upload CSV</b>
	<input type="file" name="file" required>
	<input type="submit" name="upload" value="Import" />
	</form>
	<?php
	  $connection = new MySQLi('localhost','root','','csv_import'); 
	  if(isset($_POST['upload'])){
	  	$csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
	  	 	    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$csvMimes)){
	  	 	        if(is_uploaded_file($_FILES['file']['tmp_name'])){

	  	 	      //open uploaded csv file with read only mode
	  	 	      $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
	  	 	      //skip first line
	  	 	      fgetcsv($csvFile);
	  	 	      while(($line = fgetcsv($csvFile)) !== FALSE){
	  	 	      	$name  = $line[0];
	  	 	      	$address  = $line[1];
	  	 	      	$sql = "INSERT INTO users SET name = '$name', address = '$address' ";
	  	 	        $connection->query($sql);
	  	 	      }
	  	 
	  	 }
	  	}
	  }
	?>	

Download Code Sample with Database SQL File and CSV File