DEV Community

Camille Hodoul
Camille Hodoul

Posted on • Originally published at camillehdl.dev

UTF-8 csv fix for Excel

Using utf-8 for your CSV files sounds obvious and works well in most software processing them.

Most software except Micrososft's Excel, which, considering its number of users, can sadden your day.

Excel seems to assume windows-1252 unless a Byte order mark is provided.

To fix this without asking your users to navigate a maze of hidden menus, you can add a BOM to a string before saving it to a file or triggering a download.

In JavaScript:

let csvString = ["a,b,c", "1,2,3"].join("\n");
csvString = "\ufeff" + csvString;
Enter fullscreen mode Exit fullscreen mode

or in PHP:

$csvString = implode("\n", ["a,b,c", "1,2,3"]);
$csvString = chr(0xEF) . chr(0xBB) . chr(0xBF) . $csvString;
Enter fullscreen mode Exit fullscreen mode

If you look at the hex dump, you can check for the presence of this byte sequence at the very beginning: EF BB BF.

Top comments (0)

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay