How to insert / import excel data into mysql database in php
In this post, you will be learning how to import excel file into mysql database using PHP.
Where we are going to see full example of csv file, xls file, xlsx file import into database using phpspreadsheet package. Click to view documentation
So, let's begin with import excel data into database in php by the following few steps.
Step 1: Download/Install the Package (phpSpreadSheet - Click here ):
Let's install via composer, open your terminal from your application root directory (Demo in video):
HP@HP-PC MINGW64 /c/xampp/htdocs/php-excel
$ composer require phpoffice/phpspreadsheet
After successful installation of this package, you will find few files in your application like vendor, composer.json, etc.
Step 2: Create Database Connection in dbconfig.php file:
<?php
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database_name";
// Create DB Connection
$conn = mysqli_connect($host, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Step 3: Create a form in index.php as given below:
<?php session_start(); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>How to Import Excel Data into database in PHP</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12 mt-4">
<?php
if(isset($_SESSION['message']))
{
echo "<h4>".$_SESSION['message']."</h4>";
unset($_SESSION['message']);
}
?>
<div class="card">
<div class="card-header">
<h4>How to Import Excel Data into database in PHP</h4>
</div>
<div class="card-body">
<form action="code.php" method="POST" enctype="multipart/form-data">
<input type="file" name="import_file" class="form-control" />
<button type="submit" name="save_excel_data" class="btn btn-primary mt-3">Import</button>
</form>
</div>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
After successfully creating the form for import excel, then let's click on the submit button which takes FORM using POST method to code.php and insert or import excel data into database over there.
Step 4: Create a code.php file and paste the below code:
In this code.php file, we are wrting the code of import excel file data into mysql database in php. where we are checking the Valid file upload and redirect with the message using session.
<?php
session_start();
include('dbconfig.php');
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
if(isset($_POST['save_excel_data']))
{
$fileName = $_FILES['import_file']['name'];
$file_ext = pathinfo($fileName, PATHINFO_EXTENSION);
$allowed_ext = ['xls','csv','xlsx'];
if(in_array($file_ext, $allowed_ext))
{
$inputFileNamePath = $_FILES['import_file']['tmp_name'];
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);
$data = $spreadsheet->getActiveSheet()->toArray();
$count = "0";
foreach($data as $row)
{
if($count > 0)
{
$fullname = $row['0'];
$email = $row['1'];
$phone = $row['2'];
$course = $row['3'];
$studentQuery = "INSERT INTO students (fullname,email,phone,course) VALUES ('$fullname','$email','$phone','$course')";
$result = mysqli_query($con, $studentQuery);
$msg = true;
}
else
{
$count = "1";
}
}
if(isset($msg))
{
$_SESSION['message'] = "Successfully Imported";
header('Location: index.php');
exit(0);
}
else
{
$_SESSION['message'] = "Not Imported";
header('Location: index.php');
exit(0);
}
}
else
{
$_SESSION['message'] = "Invalid File";
header('Location: index.php');
exit(0);
}
}
?>
Thank you.