How To Read CSV And Excel File In PHP Using PhpSpreadsheet

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 to import large data in the database rather than entering it one by one.

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

File Supported

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

PhpSpreadsheet simplifies this task for developers. Using PhpSpreadsheet, it is easier to handle the data from CSV and Excel files. Plus, you don’t need to convert Excel to CSV for reading it, 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

For installation of this library, I recommend to use the 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 CSV/Excel files and insert their data into the database. For this, I created a dummy file which contains columns and data as follows:

dummy data

To store these details, I also created a table ‘users’ in the database.

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

For the sake of the tutorial, I am using the above dummy data. The user should change the table structure as per their dataset.

Create a db.php and add a code for database connection in it.

db.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 the actual values.

Read CSV/Excel File in PHP

Let’s do the actual coding. First, create a form that will have a file input and submit button. The user uploads their CSV/Excel file, hit the submit button. On submission, the entries from the uploaded file should be inserted into the ‘users’ table.

form.php

<form method="post" enctype="multipart/form-data">
    <div class="form-group">
        <label for="exampleInputFile">File Upload</label>
        <input type="file" name="file" class="form-control" id="exampleInputFile">
    </div>
    <button type="submit" name="submit" class="btn btn-primary">Submit</button>
</form>

Next, the code which will execute on the submission will be as follows.

<?php
require_once 'vendor/autoload.php';
require_once 'db.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)) {
    
        $arr_file = explode('.', $_FILES['file']['name']);
        $extension = end($arr_file);
    
        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++) {
                $name = $sheetData[$i][1];
                $email = $sheetData[$i][2];
                $db->query("INSERT INTO USERS(name, email) VALUES('$name', '$email')");
            }
        }
    }
}
?>

In the above code, I am finding out the type of file and basis of that I am reading the file using the functions provided by the library. You can read more about this on official documentation here.

I hope you understand how to read CSV and Excel files using PhpSpreadsheet. I would like to hear your thoughts and suggestions in the comment section below.

Related Articles

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

10 thoughts on “How To Read CSV And Excel File In PHP Using PhpSpreadsheet

  1. i really have a hard time to install the phpspreadsheet on my system, as it always ask me of command line to use and i never experience using it. please help me.

    1. Open the command prompt in your project’s root directory and run the below command:

      composer require phpoffice/phpspreadsheet

Leave a Reply

Your email address will not be published. Required fields are marked *