How to convert Excel files to CSV files

In this tutorial, we'll work with a Google sheet that contains information about car models. Including their name, country of origin and price in dollars.

* If you want to start working with an Excel file you can import it into a Google sheet.

Here's how the original Google sheet looks:

To convert the document to CSV, we'll download it as a comma-separated values (csv) file:

The result is a CSV file, with the fields in each row separated by commas. Here's how it looks in the Notepad++ text editor:

You are welcome to download the csv file that we're going to use in the following sections of the tutorial.

Download the CSV file for the tutorial

How to handle CSV file with PHP

The function that parses the CSV file is fgetcsv, with the following syntax:

fgetcsv("filename.csv", 1000, ",");

1. The name of the CSV file
2. 1000 - The length of the longest line
3. "," - Optional delimiter parameter. The default is, of course, comma

In order to use the fgetcsv function, we need to first open the file for reading with fopen, and end the code by closing the file with fclose. In between, we use a loop inside of which we parse each CSV row separately.

Let's write the code.

The first step is opening the file for reading with fopen:

$h = fopen("filename.csv", "r");

We use the "r" mode in order to open the file for reading.

The $h variable is the handle that holds the data from the file.

The second step is reading the file line-by-line using fgetcsv, and converting each row individually into an array that we call $data.

$data = fgetcsv($h, 1000, ",");

fgetcsv includes the handle ($h), from the previous section, and in order to read all the lines, we'll run in it inside a while loop.

while (($data = fgetcsv($h, 1000, ",")) !== FALSE) 
{
  // Read the data	
}

Lastly, we need to close the file.

fclose($h);

Let's combine the 3 steps:

<?php
// Open the file for reading
if (($h = fopen("cars.csv", "r")) !== FALSE) 
{
  // Convert each line into the local $data variable
  while (($data = fgetcsv($h, 1000, ",")) !== FALSE) 
  {		
    // Read the data from a single line
  }

  // Close the file
  fclose($h);
}

How to convert a CSV file into a PHP multidimensional array

The following code produces a multidimensional array ($ the_big_array), with each of its items being made of an array that was converted from a single line in the CSV file.

<?php

$filename = 'cars.csv';

// The nested array to hold all the arrays
$the_big_array = []; 

// Open the file for reading
if (($h = fopen("{$filename}", "r")) !== FALSE) 
{
  // Each line in the file is converted into an individual array that we call $data
  // The items of the array are comma separated
  while (($data = fgetcsv($h, 1000, ",")) !== FALSE) 
  {
    // Each individual array is being pushed into the nested array
    $the_big_array[] = $data;		
  }

  // Close the file
  fclose($h);
}

// Display the code in a readable format
echo "<pre>";
var_dump($the_big_array);
echo "</pre>";

And here is the array that the code in this tutorial generated from the CSV file:

array(6) {
  [0]=>
  array(3) {
    [0]=>
    string(5) "Model"
    [1]=>
    string(7) "Country"
    [2]=>
    string(5) "Price"
  }
  [1]=>
  array(3) {
    [0]=>
    string(14) "Toyota 4Runner"
    [1]=>
    string(5) "Japan"
    [2]=>
    string(5) "34000"
  }
  [2]=>
  array(3) {
    [0]=>
    string(13) "Tesla Model S"
    [1]=>
    string(3) "USA"
    [2]=>
    string(5) "75700"
  }
  [3]=>
  array(3) {
    [0]=>
    string(12) "BMW 5 Series"
    [1]=>
    string(7) "Germany"
    [2]=>
    string(5) "54384"
  }
  [4]=>
  array(3) {
    [0]=>
    string(10) "Porche 911"
    [1]=>
    string(7) "Germany"
    [2]=>
    string(6) "168500"
  }
  [5]=>
  array(3) {
    [0]=>
    string(18) "Chevrolet Corvette"
    [1]=>
    string(3) "USA"
    [2]=>
    string(5) "80590"
  }
}