Export CSV for Excel

Question

I'm writing a CSV file in PHP using fputcsv($file, $data). It all works, however I can't just open it in Excel but have to import it and specify the encoding and which delimiter to use (in a wizard). I've seen exports from other websites that open correctly just by clicking on them and now would like to know what I should do to my file to achieve that.

I tried using this library: http://code.google.com/p/parsecsv-for-php/ But I couldn't even get it to run and am not really confident if it would really help me...

Answer 1

This is how I make Excel readable CSV files from PHP :

• Add BOM to fix UTF-8 in Excel
• Set semi-colon (;) as delimeter
• Set correct header ("Content-Type: text/csv; charset=utf-8")

For exemple :

$headers = array('Lastname :', 'Firstname :');
$rows = array(
    array('Doe', 'John'),
    array('Schlüter', 'Rudy'),
    array('Alvarez', 'Niño')
);

// Create file and make it writable

$file = fopen('file.csv', 'w');

// Add BOM to fix UTF-8 in Excel

fputs($file, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

// Headers
// Set ";" as delimiter

fputcsv($file, $headers, ";");

// Rows
// Set ";" as delimiter

foreach ($rows as $row) {

    fputcsv($file, $row, ";");
}

// Close file

fclose($file);

// Send file to browser for download

$dest_file = 'file.csv';
$file_size = filesize($dest_file);

header("Content-Type: text/csv; charset=utf-8");
header("Content-disposition: attachment; filename=\"file.csv\"");
header("Content-Length: " . $file_size);
readfile($dest_file);

Works with Excel 2013.

Answer 2

this is really a mess. You surely can use the sep=; or sep=, or sep=\t or whatever to make Excel aware of a separator used in your CSV. Just put this string at the beginning of your CSV contents. E.g.:

fwrite($handle, "sep=,\n");

fputcsv($handle,$yourcsvcontent);

This works smoothly. BUT, it doesn't work in combination with a BOM which is required to make Excel aware of UTF-8 in case you need to support special characters or MB respectively.

In the end to make it bullet-proof you need to read out users locale and set the Separator accordingly, as mentioned above. Put a BOM ("\xEF\xBB\xBF") at the begining of your CSV content, then write the CSV like e.g.: fputcsv($handle, $fields, $user_locale_seperator); where $user_locale_seperator is the separtator you retrieved by checking the user's locale. Not comfortable but it works...