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 of importing large data in the database rather than entering it one by one. Adding large records programmatically saves a ton of time.

To accomplish such tasks, you need to write a program that can read data from spreadsheets. These records can then be inserted into the database.

PhpSpreadsheet is the library that 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 the 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. Using PhpSpreadsheet, it is easier to handle the data from CSV and Excel files. Plus, 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 CSV/Excel files and insert their data into the database. For this, I created dummy files that contain fake data as follows:

dummy-records

To store these records, I am creating 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,
 `company` 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.

Next, create a config.php and add a code for database connection in it.

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 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 and hits the submit button. On form submission, the entries of the uploaded file should be inserted into 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>

Next, 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)) {
     
        $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++) { //skipping first row
                $name = $sheetData[$i][0];
                $email = $sheetData[$i][1];
                $company = $sheetData[$i][2];

                $db->query("INSERT INTO USERS(name, email, company) VALUES('$name', '$email', '$company')");
            }
        }
        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. And then I am reading the file using the functions provided for these file formats. You can read more about this on official documentation here.

I hope you understand how to read CSV and Excel files using PhpSpreadsheet. If you want to write to the spreadsheet then check out the article Exporting MySQL Database Data to Excel/CSV Using PHP.

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 *