in this tutorial you will learn how to import data from excel to mysql using php – import excel file into mysql database tutorial. We always require to add/updated or delete data from php admin panel like employee, products, items, users, emails etc.
how to import data from excel to mysql using php
simply step by step Import Excel File into MySQL Database using PHP.
Step 1.Download Package
We have to use a third-party library that can read Excel files first of all Click Here to download PHP Excel. and then simply this download extract it to your main folder and rename it to “library”.
Don’t Miss : Import Data From Excel To Mysql Using Php Code
2.Create db_config.php file
db_config.php
<?php $dbHost = "localhost"; $dbDatabase = "pakainfo_v1"; $dbPasswrod = "root"; $dbUser = "root"; $mysqli = new mysqli($dbHost, $dbUser, $dbPasswrod, $dbDatabase); ?>
3.Create index.php file
<!DOCTYPE html> <html> <head> <title>Excel Uploading PHP - www.pakainfo.com</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="doFlUpl.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> <p>Download Example File from here : <a href="Example.ods"><strong>Example.ods</strong></a></p> </form> </div> </body> </html>
4.Create doFlUpl.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)){
$myFileBasePath = 'uploads/'.basename($_FILES['file']['name']);
move_uploaded_file($_FILES['file']['tmp_name'], $myFileBasePath);
$Reader = new SpreadsheetReader($myFileBasePath);
$totalSheet = count($Reader->sheets());
echo "You have total ".$totalSheet." sheets".
$html="<table border='1'>";
$html.="<tr><th>Title</th><th>Description</th></tr>";
for($i=0;$i<$totalSheet;$i++){
$Reader->ChangeSheet($i);
foreach ($Reader as $Row)
{
$html.="<tr>";
$title = isset($Row[0]) ? $Row[0] : '';
$description = isset($Row[1]) ? $Row[1] : '';
$html.="<td>".$title."</td>";
$html.="<td>".$description."</td>";
$html.="</tr>";
$query = "insert into items(title,description) values('".$title."','".$description."')";
$mysqli->query($query);
}
}
$html.="</table>";
echo $html;
echo "<br />Data Inserted in dababase";
}else {
die("<br/>Sorry, File type is not allowed. Only Excel file.");
}
}
?>
How to Import Excel File into Mysql Database Table in PHP?
index.php
<?php
if(isset($_POST["submit"]))
{
$url='localhost';
$username='root';
$password='[email protected]';
$db_connect=mysqli_db_connectect($url,$username,$password,"location");
if(!$db_connect){
die('Could not db_connectect My Sql:' .mysqli_error());
}
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$c = 0;
while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
$fname = $filesop[0];
$lname = $filesop[1];
$sql = "insert into excel(fname,lname) values ('$fname','$lname')";
$stmt = mysqli_prepare($db_connect,$sql);
mysqli_stmt_execute($stmt);
$c = $c + 1;
}
if($sql){
echo "sucess";
}
else
{
echo "Sorry! Unable to impo.";
}
}
?>
<!DOCTYPE html>
<html>
<body>
<form enctype="multipart/form-data" method="post" role="form">
<div class="form-group">
<label for="exampleInputFile">File Upload</label>
<input type="file" name="file" id="file" size="150">
<p class="help-block">Only Excel/CSV File Import.</p>
</div>
<button type="submit" class="btn btn-default" name="submit" value="submit">Upload</button>
</form>
</body>
</html>