When you use Microsoft Excel to save CSV files, the output will look something like this:

Field 1, Field 2, "Field, with, commas", Field 4
Field 1, Field 2, "Field, with, commas", Field 4
Field 1, Field 2, "Field, with, commas", Field 4
...

As you can probably tell, only the fields containing commas are wrapped in quotes. Although this is acceptable most of the time, in some applications the lack of consistency will cause parsing errors. One, in particular, is phpMyAdmin.

If the data were stored consistently, it would look something like this:

"Field 1", "Field 2", "Field, with, commas", "Field 4"
"Field 1", "Field 2", "Field, with, commas", "Field 4"
"Field 1", "Field 2", "Field, with, commas", "Field 4"
...

In this format, it can be imported into phpMyAdmin and other applications without a problem. Fortunately, we can use PHP’s fgetcsv() function to parse it and reformat it consistently.

Using PHP to Convert an Excel CSV File to a Consistent CSV File

This function would be almost half the size if fputcsv() had an option to force encapsulation, but it currently doesn’t. No worries, a couple extra lines of code and the problem is solved:

function excel_csv_to_csv($in_file, $out_file) {

    if( !$fr = @fopen($in_file, "r") ) die("Failed to open file");
    $fw = fopen($out_file, "w");

    while( ($data = fgetcsv($fr, 1000, ",")) !== FALSE ) {
        foreach( array_keys($data) as $key )
            $data[$key] = '"' . str_replace('"', '""', $data[$key]) . '"';
        $line = implode(",", $data) . "n";
        fwrite($fw, $line);
    }

    fclose($fr);
    fclose($fw);

}

The CSV format hasn’t been formally standardized, but it typically follows a few basic guidelines. Note that some applications may import and export CSV formatted data a bit differently.

As an alternative, you can use OpenOffice.org‘s Writer. Using the Save as CSV option, the data will be formatted consistently.