Working with CSV Data

 fgetcsv($handle, 4096); 

CSV is a file format and stands for comma separated values. Many spreadsheet applications can export their data into CSV files. It seems to be easy to use explode() to convert CSV values into arrays; however, this turns out to be really complicated. What if there is a comma within a value? Then, the content is surrounded by double quotes, which makes it hard to use explode().

Reading CSV Information (fgetcsv.php)

 <table> <?php   $fp = fopen('file.csv', 'r');   while (!feof($fp)) {     $line = fgetcsv($handle, 4096);     echo '<tr><td>';     echo implode('</td><td>', htmlspecialchars       ($line));     echo '</td></tr>';   }   fclose($fp); ?> </table> 

As (almost) always, the PHP project has done most of the work and offers fgetcsv(). This function works as fgets(); however, it converts the line into an array, separating the contents at a comma (or any other character provided in the third parameter).

The second parameter to fgetcsv() is the maximum length of a line in the CSV file. This is optional in PHP 5.0.0 and later, but before, that had to be set. So you need to check the length of the longest line in the CSV file; otherwise, data will get truncated.

The code at the beginning of this phrase reads in a CSV file and outputs it as an HTML table. This is done using the following code within a loop:

 $line = fgetcsv($handle, 4096); echo '<tr><td>'; echo implode('</td><td>', $line); echo '</td></tr>'; 

This creates a row in an HTML table: <tr><td>...</td></tr>.

Figure 6.3 shows both the original spreadsheet file (in OpenOffice.org) and the result in the browser.

Figure 6.3. The CSV spreadsheet in OpenOffice.org and the result in the browser (fgetcsv.php; excerpt).

NOTE

In the code at the beginning of this phrase, the output is not sanitized using htmlspecialchars()in this case, to make the code short. However, depending on the data you have, you should consider running htmlspecialchars() on all elements of the array's fgetcsv() returns.

For writing PHP files, PHP offers a sister function, fputcsv(). You provide a file pointer and an array of values, and PHP does the rest, including escaping special characters. Unfortunately, this function currently only resides in PHP's CVS, so at the time of this writing, you have to manually emulate this behavior. Basically, you just join all array elements with commas. You also have to take care of commas within the elements, so you surround all values by double quotes. If the element contains double quotes, these have to be escaped. However, this is not done using backslashes, but by doubling the quotes. The following is an example for a valid line of CSV data:

 Quote,"And she said: ""No."" ...",Unknown 

The following is the code for fputcsv():

 if (!function_exists('fputcsv')) {   function fputcsv($fp, $line, $separator = ',') {     for ($i=0; $i < count($line); $i++) {       if (false !== strpos($line[$i], '"')) {         $line[$i] = ereg_replace('"', '""',           $line[$i]);       }       if (false !== strpos($line[$i], $separator) ||           false !== strpos($line[$i], '"')) {         $line[$i] = '"' . $line[$i] . '"';       }     }     fwrite($fp, implode($separator, $line)        "\r\n");   } } 

The following code uses our home-grown fputcsv() to manually create the CSV file that was used previously.

Writing CSV Information (fputcsv.php; excerpt)

 <?php   // ...   $data = array(     array('April 2005', '19,720,597', '1,310,181'),     array('January 2005', '18,455,683',       '1,317,871'),     array('December 2004', '17,826,404',       '1,318,739')   );   if ($fp = @fopen('usage.csv', 'w')) {     foreach ($data as $line) {       fputcsv($fp, $line);     }     fclose($fp);     echo 'CSV written.';   } else {     echo 'Cannot open file.';   } ?> 

This code creates the file usage.csv with the following contents:

 April 2005,"19,720,597","1,310,181" January 2005,"18,455,683","1,317,871" December 2004,"17,826,404","1,318,739"