15 1 0 4000 1 https://codeblock.co.za 300 true 0
How to Import a CSV File in PHP

How to Import and Validate a CSV File in PHP

0 Comments

Many apps allow you to export data via CSV, a comma separated list of values. CSV is one of the most universal methods of exporting and importing data between apps and platforms, so knowing how to import them into a PHP web app is crucial and super simple to achieve.

See a working example on the demo or download the code on GitHub.

Prerequisites

  • A firm understanding of PHP.

This tutorial has two functions: one that handles the CSV upload and the other to generate the HTML. The second function is optional, depending on how you intend to use the data captured from the CSV.

PHP Function to Import a CSV File

This function will start out very simple. Let’s have look at the basis of it.

<?php
function import_csv($file) {
    $output = array (
         'headers' => array(),
         'rows' => array()
     );

    // Validation
    if( !empty($file) ){
        
        // Get the file extension
        $extension = pathinfo($file['name'], PATHINFO_EXTENSION);

        // File Type Validation
        if( empty($extension) || $extension != 'csv' ) return $output;
       
        // Declare the delimiter
        $delimiter = ',';
        
        // Open the file in read mode
        $csv = fopen($file['tmp_name'], 'r');

       /*******
       THE REST OF THE CODE GOES HERE
       ********/
    }

    return $output;
}

The above function, import_csv() takes in one parameter, that is the uploaded file. Namely, that would be “$_FILES[‘uploaded-csv’]” in my case. $_FILES is a superglobal variable that deals with file uploads.

Preparing and Array to Save the Data

Since I’m outputting my data into an HTML table, I’d like to keep my headers and rows separate because it makes it easier to access them later. So here I declare an array that will store the headers and the rows.

File Validation

Next up, we need to validate there is a file and also that the extension is .csv. We can use pathinfo() for this because we don’t want people uploading things we don’t accept.

The rest of the code will go in this conditional. If the conditions aren’t met, we simply return the array with empty headers and rows.

While we’re here, we declare the delimiter. This will make it easier to have one place we need to change it if need be.

Finally, if all is good, we can open the file to read its contents using fopen().

Store the CSV Headers

With the validation out of the way and the file open we can now start storing the data from the CSV. Let’s start with the headers.

<?php
...

$headers = fgetcsv($csv, 0, $delimiter);

...

Here, we use fgetcsv() to get the first line of the CSV, the headings of each column in our CSV. fgetcsv() takes in multiple parameters, but we’ll just need to use the first three.

  • $csv: The file we opened.
  • $length: I’ve set this to 0 to ensure there is no limit on the line length.
  • $delimiter: This is the delimiter. For this example, we’ve already declared our delimiter as a comma (‘,’).

The headers are now stored. Let’s move on to the rows.

Store the CSV Rows

One thing you need to understand is that fgetcsv() functions very similar to array_shift(). It removes a record off the top, returns the removed record and alters the original array. So if we use it again, you’ll find that the first row is no longer the header row, but the next row (or the first row of the data we want).

With that being said, the next task is to loop through the remaining rows and store each row into a new array. It’s a little more code, because I want to store them as key-value pairs with the headers as keys. Let’s have a look at that:

<?php
...

$rows = array();
$row_number = 0;
while( $csv_row = fgetcsv($csv, 0, $delimiter) ){

    // Increment Row Number
    $row_number++;
    
    // Optional
    $encoded_row = array_map('utf8_encode', $csv_row);

    // Check for discrepancies between the amount of headers and the amount of rows
    if( count($encoded_row) !== count($headers)) { 
       return 'Row ' . $row_number . '\'s length does not match the header length: ' . implode(', ', $encoded_row);
    }
    else {
        $rows[] = array_combine($headers, $encoded_row);\
    }
   
    // Optional: limit how many rows can be imported at a time.
    if( $row_number === 5 ) break;
}

...

First we declare a variable to store our rows and also one to track which row we’re on. When we loop the while loop, we’ll increment the row and store a row in $rows[].

The condition for the while loop might look a bit strange, but to do what fgetcsv does, we need to declare it in the condition every time. fgetcsv() will return false when it reaches the end of the file, so that will escape the while loop if there are no more rows.

Encode the Rows To UTF-8

<?php
... 
$encoded_row = array_map('utf8_encode', $csv_row);
...

Encoding the row’s data is optional but it’s a good way to ensure valid utf-8 characters are used, especially if you’re saving the data into a database that’s character encoding uses the same.

Validate that the Column Count Matches the Header Count

<?php
...
if( count($encoded_row) !== count($headers)) { 
   return 'Row ' . $row_number . '\'s length does not match the header length: ' . implode(', ', $encoded_row);
}
...

To minimised user errors in the case that the CSV was created manually, it’s a good idea to check that the number of columns matches the number of headers. I’ve opted to stop the function from running and let the user know which row was the problem. This way they can fix the error and know exactly where to start importing again.

Store the Rows With Header Keys and Column Values

<?php
...
else {
   $rows[] = array_combine($headers, $encoded_row);
}
...

If the header count and column count match, we can save that row. array_combine() will stored the $headers as keys and each column in $encoded_row as the value for each header.

Optionally Limit the Number of Rows That Can Be Imported

<?php
...
if( $row_number === 2 ) { break; }
...

Some CSV’s can be very large and processing that much data can cause server hang ups and other issues. To prevent this, you could break out of the while loop after a set limit. Here, I’m using the $row_number to limit the rows to 5.

Store the Headers and Row in an Array

Finally, we can store the data from our $headers array and our data and return the array. That’s the CSV import function done!

<?php
...
   } // End the while loop
    $output['headers'] = $headers;
    $output['rows'] = $csv_rows;
} // End if empty file

return $output;

If you call the import_csv() function now, you get something like this.

Array (
    [headers] => Array ( 
       [0] => First Name
       [1] => Last Name
       [2] => Age
       [3] => Role )
    [rows] => Array ( 
       [0] => Array ( 
          [First Name] => John 
          [Last Name] => Doe
          [Age] => 30
          [Role] => Owner
       )
       [1] => Array ( 
          [First Name] => Jane 
          [Last Name] => Smith
          [Age] => 26
          [Role] => Accountant
       )
    )
)

Sanitising and Outputting the CSV Data in PHP

Now that we can get the data we need to make sure the data itself is valid before doing anything with it. Now, you could validate and sanitise the data, but I’ll just sanitise it for this example, since it is the most important. We don’t want anybody uploading any nasty stuff.

The below function is pretty straight forward. I’m generating HTML, but if you’re storing this data, pay attention to the foreach loop that handles the rows. You can ignore the headers array because it will only be used for outputting the HTML table headers here.

function generate_csv_html(array $data) {
    $headers = $data['headers'];
    $rows = $data['rows'];

    if(empty($rows)) return 'Nothing imported.';
    
    $html = '<table class=" table table-striped mt-3"><tbody>';

    if (!empty($headers)) {
        $html .= '<tr>';
        foreach($headers as $header) {
           $html .= '<th>' . strip_tags($header) . '</th>'; 
        }     $html .= '</tr>'; }

        foreach ($rows as $columns) { 
           $html .= '<tr>';

           foreach ($columns as $column) {
             $html .= '<td>' . strip_tags($column) . '</td>';
           }

           $html .= '</tr>';
        }
     $html .= '</tbody></table>';
     return $html;
 }

Notice the strip_tags() function in the above code. This will remove all HTML tags from the column’s data. If we don’t remove the tags, the uploader would be able to insert and execute scripts by adding them to the CSV. We definitely don’t want that.

Storing CSV Data in a Database

We stored the data in the array as key-value pairs, so using the function above, we could store the data in a database just as easily. In Laravel, for example, we could do something like this:

<?php
use App\Models\Person;
public function store($rows) {
    $person = new Person;
    foreach ($rows as $columns) { 
        $person->name = strip_tags($columns['First Name']);
        $person->last_name = strip_tags($columns['Last Name']);
        $person->age = strip_tags($columns['Age']);
        $person->role = strip_tags($columns['Role']);
        $person->save();
    }
}

Allow CSV Files Where The Delimiter is Unknown

Some apps might export CSV files with a semicolon rather than a comma. This is not common but it happens. Instead of changing your code, check this function enables you to check the for the delimiter in a CSV file.

<?php

function getFileDelimiter($input_file){
     $file = new SplFileObject($input_file);
     $delimiters = array(
          ',',
          ';',
     );
     $delimiter_quantities = array();
     $i = 0;
     while($file->valid() && $i <= 5){
         $row = $file->fgets();

         foreach ($delimiters as $delimiter){
            $columns = explode($delimiter, $row);

            if(count($columns) > 1){      
                 if(!empty($delimiter_quantities[$delimiter])){                
                     $delimiter_quantities[$delimiter]++;
                 } else { 
                    $delimiter_quantities[$delimiter] = 1;
                 }
            }
        }
        $i++;
    } 

    $delimiter_by_usage = array_keys($delimiter_quantities, max($delimiter_quantities));
    return $delimiter_by_usage[0];
}

Where To Use This Code

The HTML output is in this post is for demonstration purposes only. Once you have the CSV data stored in an array, you can use it for storing the values in a database or to create a JSON object.

Note: If you get an empty warning ie. “Warning: fopen(): Filename cannot be empty”, the file may exceed the file size limit set on your server. You can change this in the php,ini settings on your server:

  • upload_max_filesize = 256M
  • post_max_size = 256M

Is this still valid in 2021? Please let me know in the comments below.

Was This Helpful?

Make Your HTML Buttons More Attractive With These CSS Animations
Previous Post
Make Your HTML Buttons More Attractive With These CSS Animations

0 Comments

Leave a Reply