While working on web applications, sometimes we come across a situation where we need to import a CSV or Excel file into the database. It’s a preferred way of importing large data rather than inserting it manually. Writing a script that adds hundreds of records automatically will save a ton of time.

To accomplish such tasks, you have to build a program that can read data from these spreadsheets(CSV or Excel). Once you’re able to read it programmatically, records can easily be inserted into the database.

PhpSpreadsheet is the library that helps you to read CSV and Excel files. The library provides support for reading and writing different types of file formats. Below is the screenshot of the supported file formats.

Before I came to know about this library, I was using fgetcsv method for reading the CSV file. And for the Excel files, I needed to convert Excel to CSV first and then read it using the fgetcsv() function.

PhpSpreadsheet simplifies this task for developers. It becomes easier to handle the data from CSV and Excel files. You don’t need to convert Excel to CSV, the library directly reads the Excel file.

That being said, let’s take a look at how to read CSV and Excel files using PhpSpreadsheet.

Installation of PhpSpreadsheet Library

For the installation of this library, I recommend using Composer. Open the terminal in your project root directory and run the command:

composer require phpoffice/phpspreadsheet

In the next part of the tutorial, I am going to read both CSV and Excel files. We collect the rows from these files and insert them into the database. For this, I created dummy files with some fake data as follows:

To store these records, I am creating the users table in the database.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 `company` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Here, I created a table matching my spreadsheet. The user should change the table structure as per their dataset.

Next, create the config.php file which holds the code for the database connection.

config.php

<?php
$db_host = 'DB_HOST';
$db_username = 'DB_USERNAME';
$db_password = 'DB_PASSWORD';
$db_name = 'DB_NAME';
 
$db = new mysqli($db_host, $db_username, $db_password, $db_name);
 
if($db->connect_error){
    die("Unable to connect database: " . $db->connect_error);
}

Make sure to replace the placeholders with their actual values.

Read CSV and Excel Files in PHP

Now comes the actual coding part. First, create a form that will have a file input and submit button. The user uploads their CSV or Excel file and hits the submit button. On form submission, we read the entries of the uploaded file and send it to the users table.

<form method="post" enctype="multipart/form-data">
    <input type="file" name="file" />
    <p><button type="submit" name="submit">Submit</button></p>
</form>

The code which will execute on the form submission will be as follows.

<?php
require_once 'vendor/autoload.php';
require_once 'config.php';
  
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
  
if (isset($_POST['submit'])) {
  
    $file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      
    if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {
        $extension = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
      
        if('csv' == $extension) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }
  
        $spreadsheet = $reader->load($_FILES['file']['tmp_name']);
  
        $sheetData = $spreadsheet->getActiveSheet()->toArray();
  
        if (!empty($sheetData)) {
            for ($i=1; $i<count($sheetData); $i++) { //skipping first row
                $name = $sheetData[$i][0];
                $email = $sheetData[$i][1];
                $company = $sheetData[$i][2];
 
                $sql = sprintf("INSERT INTO USERS(name, email, company) VALUES('%s', '%s', '%s')", $db->real_escape_string($name), $db->real_escape_string($email), $db->real_escape_string($company));
                $db->query($sql);
            }
        }
        echo "Records inserted successfully.";
    } else {
        echo "Upload only CSV or Excel file.";
    }
}
?>

In the above code, I am finding the type of file whether it is CSV or Excel. Then I am reading the file using the functions provided for these file formats. For instance, to read CSV files the following statement will be used.

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();

In terms of Excel files, the statement will be

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

This $reader object loads the spreadsheet and builds an array of records out of it. We then loop through this array and add records to the database.

I hope you understand how to read CSV and Excel files using PhpSpreadsheet. You may also like to read our article Exporting MySQL Database Data to Excel/CSV Using PHP.

Related Articles

• How to Read and Write Spreadsheet Files in PHP
• Upload Images using Bulletproof Library in PHP
• IP Address Lookup using Abstract API and PHP

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.