Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel

In this first article of the series, we will try to create a function to export a PHP array as a CSV file, readable in Excel.

The purpose of the manipulation is to have in input data a PHP associative array and output a CSV file that will be downloaded to the user’s computer. For that, we will create the export_data_to_csv () function.

We will try to export the following PHP array

array(2) {
  [0]=>
  array(4) {
    ["first name"]=>
    string(7) "Bastien"
    ["last name"]=>
    string(10) "Malahieude"
    ["phone"]=>
    string(17) "06 XX XX XX XX XX"
    ["email"]=>
    string(28) "contact@bastienmalahieude.fr"
  }
  [1]=>
  array(4) {
    ["first name"]=>
    string(4) "John"
    ["last name"]=>
    string(3) "Doe"
    ["phone"]=>
    string(17) "06 XX XX XX XX XX"
    ["email"]=>
    string(11) "john@doe.fr"
  }
}

Into a CSV file that will contain all these data :

Our function will take 4 arguments to make it as flexible as possible

• $data: The data table
• $delimiter: The CSV delimiter you want to use. By default ” ; To be compatible with excel
• $enclosure: The character that delimits strings. By default the quotation mark
• $filename: The name of the file you want to export

The instantiation of our function will therefore be the following:

function export_data_to_csv($data,$filename='export',$delimiter = ';',$enclosure = '"') {
  //@TODO Do something here
 }

EXPORT DATA IN CSV FORMAT

The first step is then to force the browser to consider our data as a CSV file. This is done by using the http headers sent to the client.

// Tell the browser that the data returned is a file named $filename.csv
header ( "Content-disposition: attachment; filename = $ filename.csv" );
// Tell the browser that the data returned is a csv file.
header ( "Content-Type: text / csv" );

WRITE TO THE FILE

Since we want to return a file, we must open a “file” in the php memory.

For that, we use the function fopen with as argument php: //output

$fp = fopen("php://output", 'w');

This tells PHP that you want to write to the memory that will be sent to the browser.

Then, for compatibility problems, the data that we export must be encoded in UTF-8

To do this, add the UTF-8 BOM to the file. More information is available here

fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

ADD TABLE HEADERS

Then, we want to add the keys of our table as table header of our php file.

With the help of the array_keys function , we will be able to retrieve the keys of the array.

Subsequently, the fputcsv function is used to add a line to the CSV file.

fputcsv($fp,array_keys($data[0]),$delimiter,$enclosure);

ADD PHP TABLE DATA

The next step is to add all the data of the PHP array in the CSV file. For this, we will use a foreach loop which allows to add the elements line by line:

foreach ($data as $fields) {
 fputcsv($fp, $fields,$delimiter,$enclosure); 
}

Finally, we just have to close the file and stop the script

fclose($fp);
die();

Note: Since you are modifying http headers, it is important that this script be executed before any HTML code. More information on the subject is available in the PHP documentation .

FULL PHP FUNCTION

The final function we have just created is the following:

/**
 *
 * Exports an associative array into a CSV file using PHP.
 *
 * @see https://stackoverflow.com/questions/21988581/write-utf-8-characters-to-file-with-fputcsv-in-php
 *
 * @param array     $data       The table you want to export in CSV
 * @param string    $filename   The name of the file you want to export
 * @param string    $delimiter  The CSV delimiter you wish to use. The default ";" is used for a compatibility with microsoft excel
 * @param string    $enclosure  The type of enclosure used in the CSV file, by default it will be a quote "
 */
function export_data_to_csv($data,$filename='export',$delimiter = ';',$enclosure = '"')
{
    // Tells to the browser that a file is returned, with its name : $filename.csv
    header("Content-disposition: attachment; filename=$filename.csv");
    // Tells to the browser that the content is a csv file
    header("Content-Type: text/csv");

    // I open PHP memory as a file
    $fp = fopen("php://output", 'w');

    // Insert the UTF-8 BOM in the file
    fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

    // I add the array keys as CSV headers
    fputcsv($fp,array_keys($data[0]),$delimiter,$enclosure);

    // Add all the data in the file
    foreach ($data as $fields) {
        fputcsv($fp, $fields,$delimiter,$enclosure);
    }

    // Close the file
    fclose($fp);

    // Stop the script
    die();
}

The code is available on github in the repository xusifob/lib

Feel free to comment this article using the form underneath!

Share this article