StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To quote <a href="http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/mac-excel-converts-utf-8-characters-to-underlines/7c4cdaa7-bfa3-41a2-8482-554ae235227b?msgId=c8295574-a053-48a6-b419-51523ce2a247" rel="noreferrer">a Microsoft support engineer</a>,</p> <blockquote> <p>Excel for Mac does not currently support UTF-8</p> </blockquote> <p><strong>Update, 2017</strong>: This is true of all versions of Microsoft Excel for Mac before <em>Office 2016</em>. Newer versions (from Office 365) do now support UTF-8.</p> <p>In order to output UTF-8 content that Excel both on Windows and OS X will be able to successfully read, you will need to do two things:</p> <ol> <li><p>Make sure that you convert your UTF-8 text to UTF-16LE</p> <pre><code>mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8'); </code></pre></li> <li><p>Make sure that you add the UTF-16LE byte order mark</p> <pre><code>chr(255) . chr(254) </code></pre></li> </ol> <p>The next problem that appears only with Excel on OS X (but <em>not</em> Windows) will be when viewing a CSV file with comma separated values, Excel will render rows only with one row and all of the text along with the commas in the first row.</p> <p>The way to avoid this is to use tabs as your separated value.</p> <p>I used <a href="http://php.net/manual/en/function.fputcsv.php#84783" rel="noreferrer">this function from the PHP comments</a> (using tabs "\t" instead of commas) and it worked perfectly on OS X and Windows Excel.</p> <p>Note that to fix an issue with an empty column as the end of a row, that I did have to change the line of code that says:</p> <pre><code> $field_cnt = count($fields); </code></pre> <p>to</p> <pre><code> $field_cnt = count($fields)-1; </code></pre> <p>As some of the other comments on this page say, other spreadsheet apps like OpenOffice Calc, Apple's own Numbers and Google Doc's Spreadsheet have no issues with UTF-8 files with commas.</p> <p>See <a href="https://stackoverflow.com/questions/6588068/which-encoding-opens-csv-files-correctly-with-excel-on-both-mac-and-windows">the table in this question</a> for what works and doesn't work for Unicode CSV files in Excel</p> <hr> <p>As a side note, I might add that if you are using <a href="//getcomposer.org" rel="noreferrer">Composer</a>, you should have a look at adding <a href="http://csv.thephpleague.com/" rel="noreferrer"><code>League\Csv</code></a> to your requires. <code>League\Csv</code> has <a href="http://csv.thephpleague.com/examples/" rel="noreferrer">a really nice API for building CSV files</a>.</p> <p>To use <code>League\Csv</code> with this method of creating CSV files, check out <a href="https://github.com/thephpleague/csv/blob/8.x/examples/bom_excel_macos.php" rel="noreferrer">this example</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload