Welcome back to shortlearner.com, in this post we will learn how to read an excel file and insert the excel data into MySQL with the help of PHP Excel library.
Now a days i am working on a project of one of my client and he has a bulk amount of data in excel(.xlsx) format.
he wants to insert all his data into database using single process to save his time.
Also Read :
Get Domain name from URL
How to Send Attachment on mail using PHP.
PHP Login Script With Remember me.
Change password using javascript, php and mysqli.
Password and Confirm Password Validation Using JavaScript
Check Email is Already Registered in Database using Ajax and JavaScript.
How to hide extension of html and php file.?
Hence, we have created a process which fulfills our client requirement.
So, in this post we are going to share the way
how we can achieve the same. You can also try the same way which will save your time and easy to implement.
Below is the example of inserting excel data into mysql with the help of PHPExcel library.
Short overview of our working flow:
1.Create database and it’s tables.
2.Download php excel library.
3.Write a code which helps to import data into database and also shows the excel data into our webpage.
Here is the way to implement the flow :
We are creating a database named jyotishi and also creating a table ank_jyotishkosh into our database.
CREATE TABLE `ank_jyotishkosh` (
`id` int(11) NOT NULL,
`word` longtext,
`image` varchar(255) NOT NULL,
`structure` longtext,
`meaning` longtext,
`extra` longtext,
`search` longtext,
`lang` varchar(255) NOT NULL DEFAULT 'hindi',
`status` varchar(255) NOT NULL DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;we move to our second step and download the PHPExcel library click here to download PHPExcel library .
We are creating a config.php file, which helps to make a connection to our MySQL database.
<?php
$con = mysqli_connect("localhost","root","rootroot","jyotishi");
?>Now we are creating another PHP file where we write a code for import data into database and shows the data into web page as well.
we are creating a form in bootstrap which takes excel file from user and also add some bootstrap maxcdn files and custom CSS as well.
<!DOCTYPE html>
<html>
<head>
<title>Import Excel data into mysql database</title>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:700px;
border:1px solid #ccc;
background-color:#fff;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box"> <h3 align="center">Please Upload Only Excel File</h3><br />
<form method="post" enctype="multipart/form-data">
<label>Select Excel File</label>
<input type="file" name="excel" required/ >
<br />
<input type="submit" name="import" class="btn btn-info" value="Import" />
</form>
<br />
<br />
<?php echo $output; ?>
</div>
</body>
</html>In the above code you can see a php variable $output at the end of the code, which will use for showing excel data into web page.
Now move to the next step, when user hit on the import button then it checks whether the file is in the excel format or not.
If the file is in excel format then we move further otherwise it will show an error message and requests user to select valid excel file.
If user select valid excel file then we will include our php excel libaray into our code.
We put our data into PHP variable with the help of foreach loop and then shows in a table format and write an insert query which insert our data into database too.
<?php
if(isset($_POST["import"]))
{
require('config.php');
$output = '';
$file_name = $_FILES["excel"]["name"];
$tmp = explode('.', $file_name);
$extension = end($tmp);
$allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
{
$file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
include("PHPExcel-1.8/Classes/PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
$objPHPExcel = PHPExcel_IOFactory::load($file);
$output .= "<label class='text-success'>Data Inserted</label><br /><div class='container'><div class='row'><table class='table table-bordered'><tr><th>Word</th><th>Meaning</th><th>Extra</th></tr>";
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for($row=2; $row<=$highestRow; $row++)
{
$output .= "<tr>";
$word = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$image = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$structure = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(2, $row)->getValue());
$meaning = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(3, $row)->getValue());
$extra = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(4, $row)->getValue());
$search = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(5, $row)->getValue());
$lang = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(6, $row)->getValue());
$status = mysqli_real_escape_string($con, $worksheet->getCellByColumnAndRow(7, $row)->getValue());
mysqli_set_charset($con,'utf8');
$query = "insert into ank_jyotishkosh(word,image,structure,meaning,extra,search,lang,status) values('".$word."','".$image."','".$structure."','".$meaning."','".$extra."','".$search."','".$lang."','".$status."')";
mysqli_query($con, $query);
$output .= '<td>'.$word.'</td>';
$output .= '<td>'.$meaning.'</td>';
$output .= '<td>'.$extra.'</td>';
$output .= '</tr>';
}
}
$output .= '</table></div></div>';
}
else
{
$output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
}
}
?>Note : When you run this code and got some error then please make sure you have enabled zip extension on your server.This is the common error which we receive.
If you would like to know how to check whether extension is enabled or not, please check our post on how to enable extension on server.
Keep learning.
Stay connected with us for any queries.