How to fix "tabs" in Excel Export (PHP)?

Question

I applied this solution to solve an encoding problem I got exporting files to .CSV and Excel and it worked successfully, but now another problem emerged:

The tabs ("\t") I use in PHP as the delimiter for Excel files stopped working. It was working before I solve the previous problem. When I open the excel file, it display likes:

"ColumnAColumbBColumnC" (all together).

If a export it like csv ('Content-type: text/csv; charset=UTF-8') I have success, but not as Excel (Content-type: application/vnd.ms-excel; charset=UTF-8).

Any solution for this?

Note: using "," as a separator is not a good solution for me, because I have values with "," in some fields.

score 10 · Accepted Answer · 2014-03-26T12:55:39.530

The tabs ("\t") I use in PHP as the delimiter for Excel files stopped working.

Yes, that is correct.

Despite this there is still a simple way to export to Excel.

Use a table with <td> and <tr>, this does excel okay.

An example:

<?php
    header("Content-Type: text/plain");
    echo "<table border='1'>";
    echo "<tr>";
    echo "<th>Name</th>";
    echo "<th>First Name</th>";
    echo "<th>Department</th>";
    echo "<th>Date</th>";
    echo "<th>Topic</th>";
    echo "<th>State</th>";
    echo "<th>E-mail</th>";
    echo "<th>Place</th>";
    echo "<th>Registration fee</th>";
    echo '</tr>';
    echo '</table>';
    header("Content-disposition: attachment; filename=example_export_to_excel.xls");
    }
?>

This works perfectly for export to excel. There are no libraries needed, why look complicated things simple as it can.

score 2 · Answer 2 · answered Oct 18 '12 at 22:48

If you have a comma in a string field and want to use a comma as a separator, then you enclose the string in quotes... that's how CSV files work.

So my assumption is that you aren't using PHP's built-in fputcsv() function to create your csv file, else you'd already know this... why not consider using it, because it handles all the little intricacies that you don't seem to be aware of.

Note that Excel csv files do use a tab, and are normally encoded as UTF-16LE with a BOM rather than as UTF-8; and there is no need to identify the charset in the content type header.

How to fix "tabs" in Excel Export (PHP)?

2 Answers2