Import Excel file data in mysql database using PHP

Abhigyan Singh 10th Oct 2020

This tutorial will learn how to import excel sheet data in mysql database using php. In web development, Sometime it is necessary to upload bulk data in database. So just inserting data one by one is very difficult and time consuming.

Here You will learn how to upload bulk data in database from excel sheet. Currently, In all website as well as portal, it need to upload bulk amount of data or import bulk amount of data from excel sheet to database.

Importing excel data into database using PHP is very easy by PHPExcel_IOFactory function. We will use this function to import data from excel sheet to database. Steps are given below to upload / import excel data in your database.

Also read This: (Download Mysql Table Data into Excel Sheet Format in PHP)

You can download all file from download link.

There are two file

  1. upload.php
  2. index.php

 

upload.php

This file is just use to upload excel sheet into any specific location on server.

<?php

$uploadedStatus = 0;
if ( isset($_POST["submit"]) ) {
if ( isset($_FILES["file"])) {
//if there was an error uploading the file
if ($_FILES["file"]["error"] > 0) {
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}
else {
if (file_exists($_FILES["file"]["name"])) {
unlink($_FILES["file"]["name"]);
}
$storagename = "discussdesk.xlsx";
move_uploaded_file($_FILES["file"]["tmp_name"], $storagename);
$uploadedStatus = 1;
}
} else {
echo "No file selected <br />";
}
}

?>

<table width="600" style="margin:115px auto; background:#f8f8f8; border:1px solid #eee; padding:20px 0 25px 0;">
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post" enctype="multipart/form-data">
<tr><td colspan="2" style="font:bold 21px arial; text-align:center; border-bottom:1px solid #eee; padding:5px 0 10px 0;">
<a href="https://discussdesk.com/" target="_blank">DiscussDesk.com</a></td></tr>
<tr><td colspan="2" style="font:bold 15px arial; text-align:center; padding:0 0 5px 0;">Browse and Upload Your File </td></tr>
<tr>
<td width="50%" style="font:bold 12px tahoma, arial, sans-serif; text-align:right; border-bottom:1px solid #eee; padding:5px 10px 5px 0px; border-right:1px solid #eee;">Select file</td>
<td width="50%" style="border-bottom:1px solid #eee; padding:5px;"><input type="file" name="file" id="file" /></td>
</tr>
<tr>
<td style="font:bold 12px tahoma, arial, sans-serif; text-align:right; padding:5px 10px 5px 0px; border-right:1px solid #eee;">Submit</td>
<td width="50%" style=" padding:5px;"><input type="submit" name="submit" /></td>
</tr>
</table>

index.php

This file plays main role to import these file data into your database. Here, first we will establish database connection into our database to insert data. then we will include IOFactory file and create an object for PHPExcel_IOFactory. Then we will get all data by getActiveSheet in array format.

Here the first column in the excel sheet in represented by A in this arry, second column in the excel sheet in represented by B in this arry, and so on. and then we will customize our query to insert data into database.

<?php
/************************ YOUR DATABASE CONNECTION START HERE ****************************/

define ("DB_HOST", "localhost"); // set database host
define ("DB_USER", ""); // set database user
define ("DB_PASS",""); // set database password
define ("DB_NAME",""); // set database name

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

$databasetable = "YOUR_TABLE";

/************************ YOUR DATABASE CONNECTION END HERE ****************************/

set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';

// This is the file path to be uploaded.
$inputFileName = 'discussdesk.xlsx';

try {
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch(Exception $e) {
die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$arrayCount = count($allDataInSheet); // Here get total count of row in that Excel sheet

for($i=2;$i<=$arrayCount;$i++){
$userName = trim($allDataInSheet[$i]["A"]);
$userMobile = trim($allDataInSheet[$i]["B"]);

$query = "SELECT name FROM YOUR_TABLE WHERE name = '".$userName."' and email = '".$userMobile."'";
$sql = mysql_query($query);
$recResult = mysql_fetch_array($sql);
$existName = $recResult["name"];
if($existName=="") {
$insertTable= mysql_query("insert into YOUR_TABLE (name, email) values('".$userName."', '".$userMobile."');");

$msg = 'Record has been added. <div style="Padding:20px 0 0 0;"><a href="https://discussdesk.com//import-excel-file-data-in-mysql-database-using-PHP.htm" target="_blank">Go Back to tutorial</a></div>';
} else {
$msg = 'Record already exist. <div style="Padding:20px 0 0 0;"><a href="https://discussdesk.com//import-excel-file-data-in-mysql-database-using-PHP.htm" target="_blank">Go Back to tutorial</a></div>';
}
}
echo "<div style='font: bold 18px arial,verdana;padding: 45px 0 0 500px;'>".$msg."</div>";

?>

I hope this tutorial will help you. If you have any query regarding this, Please feel free to ask in comment section.

Also read This: (Download Mysql Table Data into Excel Sheet Format in PHP)

Authored By Abhigyan Singh

He is a continuous blogger and has blogged on different topic. He loves to surf Internet and always trying to get new Idea about new Technology and Innovations and sharing these great information to all the technology lovers.

Also on DiscussDesk