28K Views

Working with CSV files in PHP/Symfony

CSV files are widely used in data export, import. Here, I am going to describe how to work with CSV file(s) in PHP or Symfony. Blog also mentions the use of BOM in CSV files for interoperability in Excel.

What are CSV files?

According to Wikipedia,  comma-separated values (CSV) file stores tabular data (numbers and text) in plain text. Each line of the file is a data record. Each record consists of one or more fields, separated by commas.

As clear by name, Each CSV file record contains values separated by comma like
John,Harry,Sejal

(,) is used as the separator

UTF-8 CSV file for Excel:

If we want CSV file to be recognized by excel software. Then we should use BOM for CSV file also.

Although BOM (byte order mark is optional for CSV files), it could be used for interoperability.

BOM for CSV can be generated in PHP/Symfony as

chr(hexdec('EF')).chr(hexdec('BB')).chr(hexdec('BF'))

Read more

Reading CSV files

Since CSV files provide the easiest way to save Tabular data. so, they are often used for Uploading data or other purposes.

PHP provides inbuilt function fgetcsv for reading CSV files. We could use fgetcsv like in example below
Example:
<?php
    $rowNo = 1;
        // $fp is file pointer to file sample.csv
    if (($fp = fopen("sample.csv", "r")) !== FALSE) {
        while (($row = fgetcsv($fp, 1000, ",")) !== FALSE) {
            $num = count($row);
            echo "<p> $num fields in line $rowNo: <br /></p>\n";
            $rowNo++;
            for ($c=0; $c < $num; $c++) {
                echo $row[$c] . "<br />\n";
            }
        }
        fclose($fp);
    }
?>

Writing CSV files

Unlike, fgetcsv PHP provides inbuilt function fputcsv for Writing CSV files.

Example:

<?php
    $list = array (
        array('aaa', 'bbb', 'ccc', 'dddd'),
        array('123', '456', '789', '102'),
        array('"aaa"', '"bbb"')
    );

    $fp = fopen("sample.csv", "w");

    foreach ($list as $line)
    {
        fputcsv(
            $fp, // The file pointer
            $line, // The fields
            ',' // The delimiter
        );      
    }

    fclose($fp); 
?>

We can also download CSV file instead of directly writing it,

We could do so by writing to PHP Output Stream.

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

We will need to set headers to download CSV file using this method instead of viewing it as String

Setting headers to download file

In PHP:

header("Content-length: " . $fileSize); // can omit this line 
header('Content-type: text/csv');
header("Content-Disposition: attachment; filename=\"$fileName\" ");

// add these two lines
ob_clean();   // discard any data in the output buffer (if possible)
flush();      // flush headers (if possible)

// echo chr(hexdec('EF')).chr(hexdec('BB')).chr(hexdec('BF')); //optional BOM
// $responseString contains csv string to be printed echo $responseString; // orfputcsv as described above exit();

In Symfony:

use Symfony\Component\HttpFoundation\Response;
..
..

$response = new Response();
$response->headers->set('Content-type', 'text/csv');
$response->headers->set('Cache-Control', 'private');
$response->headers->set('Content-length', $attachmentSize);
$response->headers->set('Content-Disposition', 'attachment; filename="' . $fileName . '";');
$response->sendHeaders();
// $responseString contains csv result string
$response->setContent($responseString);
return $response;

What’s More?

That’s all for now. Feel free to comment or ask any question about working with CSV.

Category(s) Symfony
. . .