November 5, 2023
Category : PHP
Bootstrap
MySql
PHP Import Excel File into MySQL Database Tutorial
In this post, i going to explain how to import excel file into mysql database using PHP. In this post i will give you full example of csv file, ods file, xls file, xlsx file import into database using phpexcel package.
We always require to add data from admin panel like products, items, users, emails etc. If we have few records and require to add manually then it is ok, But if we have more data on excel file or csv file then it take long time to store data, At that time we can use direct import xls file or csv file into mysql database.
So, In this tutorial i am using Spreadsheet_Excel_Reader class for import csv file into php database. So you can do it by following few step. In this tutorial you have to do just as bellow file:
1.Download Package
2.Create db_config.php file
3.Create index.php file
4.Create excelUpload.php
5.Create Upload Folder
So, you have to just follow few step, So let's follow:
s
Step 1: Download Package
In first step we have to download PHP Excel Reader library from GitHub, So first let's download from here :
Click Here to download PHP Excel
After download extract it to your root folder and rename it to "library".
Step 2: Create db_config.php file
In this step, we have to create db_config.php file for database configuration, In this file you have to set database host, database username, database password, database name. This file will use for store data into database.
So, let's create db_config.php file and put bellow code:
db_config.php
<?php
$dbHost = "localhost";
$dbDatabase = "h_php";
$dbPasswrod = "root";
$dbUser = "root";
$mysqli = new mysqli($dbHost, $dbUser, $dbPasswrod, $dbDatabase);
?>
Step 3: Create index.php file
In this step i am going to create index.php file in your root directory, in this file i created simply form using bootstrap, this way you have to just feel this form and click to "Submit" button. You can import excel file from file input button.
So let's copy bellow code and put it into index.php file.
index.php
<!DOCTYPE html>
<html>
<head>
<title>Excel Uploading 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>
<p>Download Demo File from here : <a href="demo.ods"><strong>Demo.ods</strong></a></p>
</form>
</div>
</body>
</html>
Step 4: Create excelUpload.php file
You have to create excelUpload.php file that will manage for import data into database, In this step we have to create uploads folder for store excel file into this file and then read that file.
So, let's create excelUpload.php file and put bellow code:
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".
$html="<table border='1'>";
$html.="<tr><th>Title</th><th>Description</th></tr>";
/* For Loop for all sheets */
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.");
}
}
?>
Run PHP App:
All the required steps have been done, now you have to type the given below command and hit enter to run the PHP app:
php -S localhost:8000
Now, Go to your web browser, type the given URL and view the app output:
Read Also: PHP Ajax Form Validation Example Tutorial
http://localhost:8000
I hope it can help you...
Hardik Savani
I'm a full-stack developer, entrepreneur and owner of ItSolutionstuff.com. I live in India and I love to write tutorials and tips that can help to other artisan. I am a big fan of PHP, Laravel, Angular, Vue, Node, Javascript, JQuery, Codeigniter and Bootstrap from the early stage. I believe in Hardworking and Consistency.
We are Recommending you
- Laravel Create Word Document File using phpoffice/phpword package
- PHP MySQL Contact US Form with Validation Example
- PHP Ajax Infinite Scroll Pagination Example
- PHP Ajax Dependent Dropdown List Example
- Tags Input with Autocomplete using jQuery and PHP Example
- PHP MySQL Highcharts Chart Example
- PHP CRUD Operation Using Ajax and JQuery Example
- Login with Facebook using PHP MySQL Example
- Convert HTML to PDF in PHP with Dompdf Example
- PHP Capture Screenshot of Website from URL Example
- How to Integrate Google Recaptcha with PHP Form?
- PHP Crop Image Before Upload using Croppie JS Example
- PHP JQuery Select2 Ajax Autocomplete Example
- PHP AngularJS CRUD with Search and Pagination Tutorial
;void(0);){kind=link}
;void(0);){kind=link}