Talking business: How I Learned to Love CSV

A CSV file is a comma separated list of data; each data set is on a new line. The first line contains the headers, explaining what the following elements are. In short, a CSV is a really easy table.

Saved as employees.csv, this is:

Name,Surname,Department,Room

Bob,Houston,Problems,214

Stephen,Seagull,Cleaning,231

Arnold,Egger,Security,102

Eliza,Ihaye,Communications,207

Back in the days of old, when we used Perl or ancient versions of PHP, we tackled CSV files with Regular Expressions, first splitting the data into an array at the linebreak, and then splitting the lines into data at the comma.

<?php

$data=load('employees.csv');

if($data!='')

{

            $lines=preg_split("/\n/",$data);

            $headers=preg_split("/,/",$lines[0]);

            echo '<table>';

            echo '<thead><tr>';

            foreach($headers as $h)

            {

                        echo '<th scope="col">'.$h.'</th>';

            }

            echo '</tr></thead>';

            echo '<tbody>';

            for($i=1;$i<sizeof($lines);$i++)

            {

                        $linedata=preg_split("/,/",$lines[$i]);

                        $class=$i%2==0?' class="odd"':'';

                        if($linedata[0]!='')

                        {

                                    echo '<tr'.$class.'>';

                                    foreach($linedata as $l)

                                    {

                                                echo '<td>'.$l.'</td>';

                                    }

                                    echo '</tr>';

                       }

            }

            echo '</tbody>';

            echo '</table>';

}

function load($filelocation)

{

            if (file_exists($filelocation))

            {

                        $newfile = fopen($filelocation,"r");

                        $file_content = fread($newfile, filesize($filelocation));

                        fclose($newfile);

                        return $file_content;

            }

}

?>

That works wonders, but soon reaches its limits, as there are dangers:

• Unix and Windows environments do have different ways of defining a line break.
  
• When an editor enters a comma in one of the fields, Excel will add quotation marks around that field data. We need to alter our regular expression to take care of that.
  
• Excel offers various versions of CSV to save (Windows, DOS, Macintosh), each with different settings.

Lucky for us, the PHP developers showed mercy and created fgetcsv(), which tackles most of these issues. On Macintosh systems there might be some trouble still, but there is a workaround – setting the auto_detect_line_endings in the PHP configuration to true .

Our previous example looks a lot easier when we use this function.

echo '<table>';

$row=0;

$handle = fopen("employees.csv", "r");

while (($data = fgetcsv($handle, 1000)) != FALSE)

            {

            if($row==0)

            {

                        echo '<thead><tr>';

                        foreach ($data as $c)

                        {

                                    echo '<th scope="col">'.$c.'</th>';

                        }

                        echo '</tr></thead><tbody>';

            } else {

                        $class=$row%2==0?' class="odd"':'';

                        echo '<tr'.$class.'>';

                        foreach ($data as $c)

                        {

                                    echo '<td>'.$c.'</td>';

                        }

                        echo '</tr>';

            }

            $row++;

}

fclose($handle);

echo '</tbody></table>';

For storing data or exporting it as a CSV, we can use the fputcsv() function. In connection with a proper header (content-type and content-disposition) it will prompt the user to either open our script output in Excel or save it.