Import excel file into mysql in PHP

 
coding-zon-upload-exce-to-mysql


Upload Excel file and Import  it into MySQL in PHP


  1.  Create a table in some database with given code.
  2.  Download Library - extract it, rename the folder to library.
  3. Create index.php file with given code in a server.
  4. Create excelUpload.php with given code.
  5. Create db_config.php.
  6. Create one Excel file with some sample data as shown in the below image of Excel file.
  7. Create 'uploads' folder in the same directory.
  8. Now start server, run the index.php. Upload a .xls file.
  9. If Everything is fine, Excel data inserted in the above created MySQL table - tbl_products.

PHP Version:  5.5.11

Folder Structure

 



 

 Query:

CREATE TABLE IF NOT EXISTS `tbl_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `productname` varchar(100) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

 

 Top

index.php

<!DOCTYPE html>
<html>
<head>
<title>coding-zon Demo - Upload Excel to MySQL in PHP</title>
<link rel="stylesheet" type="text/css"  
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body>


<div class="container">
<h1>Excel Upload</h1>


<form method="POST" action="excelUpload.php" enctype="multipart/form-data">
<div class="form-group">
	<label>Upload Excel File</label>
	<input type="file" name="file" class="form-control">
</div>
<div class="form-group">
	<button type="submit" name="Submit" class="btn btn-success">Upload</button>
</div> 
</div>


</body>
</html>	
 
output:
coding-zon-excel-upload

 
  
 

 

db_config.php

 

<?php

$databaseHost = 'localhost:3306';
$databaseuser = 'root';
$databasePassword = '';
$databaseName = 'financialdb';


$conn = mysqli_connect($databaseHost, $databaseuser, $databasePassword, $databaseName); 
 
?>

 

 TopMenu

excelUpload.php 

<?php


require('library/php-excel-reader/excel_reader2.php');
require('library/SpreadsheetReader.php');
require('db_config.php');if(isset($_POST['Submit'])){


$mimes = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.oasis.opendocument.spreadsheet'];
if(in_array($_FILES["file"]["type"],$mimes)){


$uploadFilePath = 'uploads/'.basename($_FILES['file']['name']);
move_uploaded_file($_FILES['file']['tmp_name'], $uploadFilePath);


$Reader = new SpreadsheetReader($uploadFilePath);
$totalSheet = count($Reader->sheets());

echo "You have total ".$totalSheet." sheets<br>".


$Reader->ChangeSheet(0);
echo "count=" .count($Reader)." added <br>";
$count = 0;
foreach ($Reader as $Row)
{
	
	$count++;
	$id = isset($Row[0]) ? $Row[0] : '';
	$productname = isset($Row[1]) ? $Row[1] : '';
	$description = isset($Row[2]) ? $Row[2] : '';
	
	if($count == 1)   continue;   //  skips titles from excel file while inserting
	
	$query = "insert into tbl_products(productname, description) values('".$productname."','".$description."')";
	$result = mysqli_query($conn,$query);
}

echo "<br />Data Inserted in dababase";

 
}else { 
die("<br/>Sorry, File type error. Only Excel file allowed."); 
}

mysqli_close($conn);
}


?>

 

 

products.xls

 

codingzon-prouct-sample-sheet

 

 Demo