Import data from Excel to mysql database using PHPExcel

Earlier I wrote a post – how to import excel sheet data to mysql database using PHP.

That was by using PHPExcelreader API. This time, I am writing this tutorial again now using PHPExcel library.

I came to know that many new developers are searching for many ways or API to import excel data to MySQL database.

So, first, we download the Phpexcel API. You can download this from https://phpexcel.codeplex.com/ .

PHPExcel library is used to read and write from different excel formats like .xls, csv, ods etc. I am not going into details here about i. I will explain only how to import excel file in MySQL database using PHP.

First, download the PHPExcel library –https://phpexcel.codeplex.com/

Second, create a form to upload the excel sheet. You can use the same form as in the previous excel sheet import example.

Here is the PHP code –

Note – Don’t forget to include the database connection file.

<?php

/****** Include the EXCEL Reader Factory ***********/
error_reporting(0);
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';

Once you download the PHP excel API, you will get a folder name Classes.

We will use only this folder for importing the excel file. Please keep this folder in the root of your code.

In below code, we checked the uploaded excel file and we checked it is excel file or not.

if(isset($_POST) && !empty($_FILES['excelupload']['name']))
{
//print_r($_FILES['excelupload']);
$namearr = explode(".",$_FILES['excelupload']['name']);
if(end($namearr) != 'xls' && end($namearr) != 'xlsx')
{
echo '<p> Invalid File </p>';
$invalid = 1;
}

Below code shows that if it is excel file, then we move excel file to uploads folder. It is necessary to move the excel file on the same server to upload.

if($invalid != 1)
{
$target_dir = "uploads/";
$target_file = $target_dir . basename($_FILES["excelupload"]["name"]);
$response = move_uploaded_file($_FILES['excelupload']['tmp_name'],$target_file); // Upload the file to the current folder

Below code upload the excel file from local system to server and upload. $allDataInSheet variable contain the all excel data in an array.

if($response)
{

try {
$objPHPExcel = PHPExcel_IOFactory::load($target_file);
} catch(Exception $e) {
die('Error : Unable to load the file : "'.pathinfo($_FILES['excelupload']['name'],PATHINFO_BASENAME).'": '.$e->getMessage());
}
$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
//print_r($allDataInSheet);
$arrayCount = count($allDataInSheet); // Total Number of rows in the uploaded EXCEL file
//echo $arrayCount;

Below query insert excel sheet data to MySQL database table. By using the below method, any number of fields can be inserted in MySQL database.

$string = "INSERT INTO `table1` (`Sr.No.`,`Emp ID`, `Name`, `Address`) VALUES ";

for($i=2;$i<=$arrayCount;$i++){

$empid= trim($allDataInSheet[$i]["B"]);
$name = trim($allDataInSheet[$i]["C"]);
$address = trim($allDataInSheet[$i]["D"]);


$string .= "( '' , '".$empid."' , '".$name ."','".$address ."', ),";
}
$string = substr($string,0,-1);
//echo $string;

mysql_query($string); // Insert all the data into one query
}
}// End Invalid Condition
echo "<div style='color:#3C7C64'>Import Succesful</div>";
}

?>

Please write in a comment for any issue.

If you find it difficult then you can move to another option – How to import excel data to mysql database using php .

If anyone’s code is not working, then please send me a message I will try to help you out. Thanks for reading..:-)