Import Excel File Data into MySQL Database using PHP

Import Excel File Data into MySQL Database using PHP
 

Excel is a spreadsheet used to store data in tabular form. In the web application, the Excel file format is used mainly for 2 purposes, import and export data. The import feature helps to parse data from Excel files and insert data in the database. On the other hand, the Export feature used to download data from the database and save it as a file.

Generally, you need to insert data one by one manually to add records to the database. But, when there are a large number of records that need to be inserted in the database, it’s very time-consuming to insert data manually. The import feature is a useful option to insert bulk data into the database at once dynamically. In this tutorial, we will show you how to import data from Excel file and insert into the database using PHP.

In this example script, we will import members’ data from an Excel file in the database using PHP and MySQL.

• Upload Excel file with HTML and PHP.
• Read and parse data from Excel files with PHP.
• Insert data in the database with PHP and MySQL.

Create Database Table

To store the member’s data, a table is required in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Excel File Format

Based on the database table structure, the Excel file should have these fields – First Name, Last Name, Email, Phone, and Status. To import the data from Excel file, the data format should be similar to the following screen.

PhpSpreadsheet Library

We will use the PhpSpreadsheet library to read Excel files and parse data. The PhpSpreadsheet is a PHP library that helps to parse data from spreadsheet file formats (.xls, .xlsx, etc).

Use composer to install PhpSpreadsheet in the script folder.

composer require phpoffice/phpspreadsheet

Alternatively, you can use our source code to install PhpSpreadsheet without composer.

Note that: All the required files including the PhpSpreadsheet library are included in our source code, you do not require to install it separately.

Before getting started, look at the file structure of the Excel data import in PHP script.

import_excel_data_with_php/
├── dbConnect.php
├── index.php
├── importData.php
├── vendor/
├── assets/
    └── css/
        ├── bootstrap.min.css
        └── style.css

Database Configuration (dbConfig.php)

The dbConfig.php is used to connect and select the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld_db"; 
// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
} 
?>

Excel File Upload (index.php)

Initially, the existing member’s data is listed with the Excel file import option.

• Existing members’ data are fetched from the database and listed on the webpage.
• An Import Excel button is placed at the top of the list.
• By clicking the Import button, an HTML form appears to select and upload an Excel file.
• On submission, the form is submitted to the server-side script (importData.php) to process the Excel data import functionality.
• formToggle() – It is a JavaScript function that helps to Show/Hide the Excel file upload form. This function is triggered on the click event of the Import button.

If the form is already submitted,

• The status message is retrieved from the URL and the import status is displayed on the web page.

<?php 
// Load the database configuration file 
include_once 'dbConfig.php'; 
// Get status message 
if(!empty($_GET['status'])){ 
    switch($_GET['status']){ 
        case 'succ': 
            $statusType = 'alert-success'; 
            $statusMsg = 'Member data has been imported successfully.'; 
            break; 
        case 'err': 
            $statusType = 'alert-danger'; 
            $statusMsg = 'Something went wrong, please try again.'; 
            break; 
        case 'invalid_file': 
            $statusType = 'alert-danger'; 
            $statusMsg = 'Please upload a valid Excel file.'; 
            break; 
        default: 
            $statusType = ''; 
            $statusMsg = ''; 
    } 
} 
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Excel File Data with PHP</title>

<!-- Bootstrap library -->
<link rel="stylesheet" href="assets/css/bootstrap.min.css">

<!-- Stylesheet file -->
<link rel="stylesheet" href="assets/css/style.css">
<!-- Show/hide Excel file upload form -->
<script>
function formToggle(ID){
    var element = document.getElementById(ID);
    if(element.style.display === "none"){
        element.style.display = "block";
    }else{
        element.style.display = "none";
    }
}
</script>
</head>
<body>

<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-12 p-3">
    <div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>

<div class="row p-3">
    <!-- Import link -->
    <div class="col-md-12 head">
        <div class="float-end">
            <a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import Excel</a>
        </div>
    </div>
    <!-- Excel file upload form -->
    <div class="col-md-12" id="importFrm" style="display: none;">
        <form class="row g-3" action="importData.php" method="post" enctype="multipart/form-data">
            <div class="col-auto">
                <label for="fileInput" class="visually-hidden">File</label>
                <input type="file" class="form-control" name="file" id="fileInput" />
            </div>
            <div class="col-auto">
                <input type="submit" class="btn btn-primary mb-3" name="importSubmit" value="Import">
            </div>
        </form>
    </div>

    <!-- Data list table --> 
    <table class="table table-striped table-bordered">
        <thead class="table-dark">
            <tr>
                <th>#</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Email</th>
                <th>Phone</th>
                <th>Status</th>
                <th>Created</th>
            </tr>
        </thead>
        <tbody>
        <?php 
        // Get member rows 
        $result = $db->query("SELECT * FROM members ORDER BY id DESC"); 
        if($result->num_rows > 0){ $i=0; 
            while($row = $result->fetch_assoc()){ $i++; 
        ?>
            <tr>
                <td><?php echo $i; ?></td>
                <td><?php echo $row['first_name']; ?></td>
                <td><?php echo $row['last_name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['phone']; ?></td>
                <td><?php echo $row['status']; ?></td>
                <td><?php echo $row['created']; ?></td>
            </tr>
        <?php } }else{ ?>
            <tr><td colspan="7">No member(s) found...</td></tr>
        <?php } ?>
        </tbody>
    </table>
</div>

</body>
</html>

Import Excel Data to Database (importData.php)

The importData.php file handles the file upload and Excel data import operations using PHP and MySQL.

• Include PhpSpreadsheet library autoloader and specify Xlsx namespace from PhpSpreadsheet\Reader.
• Validate the selected file format to check whether it is a valid Excel file (.xlsx).
• Check whether the Excel file is uploaded using the PHP is_uploaded_file() function.
• Retrieve row data from Excel file with PhpSpreadsheet using PHP.
  • Initialize the Xlsx() class of the PhpSpreadsheet library.
  • Load the Excel file using the load() method of Xlsx class.
  • Parse data from the spreadsheet using the getActiveSheet() method.
  • Convert Excel file data into an array using the toArray() method.
• Insert/Update member data in the database based on the email address.
• Redirect to the listing page with the import status code.

<?php 
// Load the database configuration file 
include_once 'dbConfig.php'; 
// Include PhpSpreadsheet library autoloader 
require_once 'vendor/autoload.php'; 
use PhpOffice\PhpSpreadsheet\Reader\Xlsx; 
if(isset($_POST['importSubmit'])){ 
     
    // Allowed mime types 
    $excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); 
     
    // Validate whether selected file is a Excel file 
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $excelMimes)){ 
         
        // If the file is uploaded 
        if(is_uploaded_file($_FILES['file']['tmp_name'])){ 
            $reader = new Xlsx(); 
            $spreadsheet = $reader->load($_FILES['file']['tmp_name']); 
            $worksheet = $spreadsheet->getActiveSheet();  
            $worksheet_arr = $worksheet->toArray(); 
            // Remove header row 
            unset($worksheet_arr[0]); 
            foreach($worksheet_arr as $row){ 
                $first_name = $row[0]; 
                $last_name = $row[1]; 
                $email = $row[2]; 
                $phone = $row[3]; 
                $status = $row[4]; 
                // Check whether member already exists in the database with the same email 
                $prevQuery = "SELECT id FROM members WHERE email = '".$email."'"; 
                $prevResult = $db->query($prevQuery); 
                 
                if($prevResult->num_rows > 0){ 
                    // Update member data in the database 
                    $db->query("UPDATE members SET first_name = '".$first_name."', last_name = '".$last_name."', email = '".$email."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'"); 
                }else{ 
                    // Insert member data in the database 
                    $db->query("INSERT INTO members (first_name, last_name, email, phone, status, created, modified) VALUES ('".$first_name."', '".$last_name."', '".$email."', '".$phone."', '".$status."', NOW(), NOW())"); 
                } 
            } 
             
            $qstring = '?status=succ'; 
        }else{ 
            $qstring = '?status=err'; 
        } 
    }else{ 
        $qstring = '?status=invalid_file'; 
    } 
} 
// Redirect to the listing page 
header("Location: index.php".$qstring); 
?>

THAM KHẢO THÊM https://www.kodingmadesimple.com/2017/02/insert-excel-file-into-mysql-using-php.html
Video 
PHP + Excel Bài 2/6: Insert 1 sheet file Excel vào database MySQL (youtube.com)


Chi tiết hơn dành cho php 5.6 của CMND (Phần mềm điều khiển, quản lý tivi tập trung ): 

Lập trình import dữ liệu từ file excel vào mysql bằng php với phpexcel

Bước 1: Cài đặt thư viện PHPExcel

Sử dụng composer để cài đặt thư viện PHPExcel: download PHPExcel library, unzip and move it to your root folder.

composer require phpexcel/phpexcel

Bước 2: Kết nối đến database MySQL

Tạo file db_connect.php để kết nối đến database MySQL:

PHP

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "matkhau"; 
$dbName     = "smartinstall"; 
// Create database connection 
//$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
$mysqli =  mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); 
$mysqli->set_charset('utf8');
// Check connection 
if (mysqli_connect_errno()) { 
    echo 'Kết nối thất bại: '.mysqli_connect_error();
	exit;
}
?>

Bước 3: Xử lý file Excel và import dữ liệu vào MySQL

Tạo file index.php để xử lý file Excel và import dữ liệu vào MySQL:

PHP

<?php

require('db_connect.php');
require('Classes/PHPexcel.php');
if(isset($_POST['btnGui'])){
	$file = $_FILES['file']['tmp_name'];
	// in ra đường dẫn file
	//echo $file; 
	$objReader = PHPExcel_IOFactory::createReaderForFile($file);
	$objReader->setLoadSheetsOnly('guestinfo');
	$objPHPExcel = $objReader->load($file);
	$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
	// In ra mảng dữ liệu lấy được từ sheet trên
	//print_r($sheetData);

	//Đếm cột cao nhất
	//$getHighestColumn = $objPHPExcel->setActiveSheetIndex()->getHighestColumn();

	// Đếm dòng cao nhất
	$highestRow = $objPHPExcel->setActiveSheetIndex()->getHighestRow();

	// in ra số dòng cao nhất ( 
	//echo $highestRow;

	// Load ra tên sheet của file
	//$loadedAllSheetNames = $objReader->listWorksheetNames($file);
	// Lặp qua từng dòng dữ liệu trong sheet
    for ($row = 2; $row <= $highestRow; $row++) {

    // Lấy dữ liệu từ từng cột
    $guestId = $sheetData[$row]['B'];
    $roomid = $sheetData[$row]['C'];
    $guestName = $sheetData[$row]['D'];

    // Tạo câu lệnh SQL để insert dữ liệu vào database
    $sql = "INSERT INTO guestinfo (guestId, roomid, guestName) VALUES ('$guestId', '$roomid', '$guestName')";

    // Thực thi câu lệnh SQL
    $mysqli->query($sql);
	}
	echo 'Thêm dữ liệu thành công!';
}

 ?>
 <!DOCTYPE html>
 <html>
 <head>
 <meta charset="utf-8">
 <meta http-equiv="X-UA-Compatible" content="IE=edge">
 <title>Import data khach hang </title>
 <link rel="stylesheet" href="">
 </head>
 <body>
 <form method="POST" enctype="multipart/form-data">
 <input type="file" name="file">
 <button type="submit" name="btnGui">Import</button> 
 </form>
 </body>
 </html>

Bước 4: Chạy script

Chạy file index.php để import dữ liệu từ file Excel vào database MySQL.

Lưu ý:

• Thay đổi your_file_name.xlsx thành tên file Excel của bạn.
• Thay đổi thông tin kết nối database MySQL trong file db_connect.php.
• Chỉnh sửa mã PHP để phù hợp với cấu trúc dữ liệu của file Excel và database MySQL của bạn.
• File excel của tôi đính kèm theo bài viết

Chương trình import excel danh sách khách hàng vào lời chào tivi Việt Thuận tại đây