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
1 |
chr(hexdec('EF')).chr(hexdec('BB')).chr(hexdec('BF')) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?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.
1 |
$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:
1 2 3 4 5 6 7 8 9 |
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 |
1 |
// $responseString contains csv string to be printed echo $responseString; // orfputcsv as described above exit(); |
In Symfony:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.