Excel to CSV without broken accents: UTF-8, BOM and separators

Most broken accents in a CSV come from an encoding mismatch: the file is saved as UTF-8 but opened as a Windows encoding. Export the sheet as CSV UTF-8, with a byte order mark (BOM) if Excel is the program that will open it, and choose the separator your locale expects. Before exporting, format columns such as ZIP codes and phone numbers as text, so the zeros survive.

Excel to CSVNeed CSV, have an Excel fileDo it free

Why "é" turns into "é"

A character in a file is stored as bytes. In UTF-8, the letter é is two bytes, C3 and A9. A program that reads those bytes as Windows-1252, a common Windows encoding, sees the character à for C3 and © for A9. So "café" appears as "café". You can see this yourself: encoding é as UTF-8 and decoding the result as Windows-1252 gives é. An ü, ñ or ö breaks in the same way.

The fix is not to edit the characters. It is to tell the program which encoding the file uses.

UTF-8 and the BOM

UTF-8 is the standard encoding for text on the web and in most modern software. A plain CSV file has no field that says which encoding it uses, so programs guess. A byte order mark is three bytes at the very start of the file, EF BB BF, which signal UTF-8 to programs that look for it. Excel on Windows often opens a UTF-8 file without a BOM using its local encoding, so the BOM is the common fix.

In recent versions of Excel, the Save As menu offers a CSV UTF-8 option that adds the BOM. You can also import the file through Data, From Text/CSV, and set the File Origin to 65001, UTF-8. The BOM has one drawback: a program that does not expect it may treat it as part of the first column name. Most parsers and spreadsheet programs handle it, but if a data importer reports a strange header, strip the BOM.

Comma or semicolon

CSV means comma-separated values, and the RFC 4180 specification describes fields separated by commas. In many countries, including those that use a decimal comma such as 1,5 for one and a half, Excel's list separator is a semicolon. When that happens, a comma-separated file opened in Excel can split badly, and a number like 1,5 becomes two fields.

Choose the separator your target program expects. The Excel to CSV tool offers comma, semicolon and tab. If the file is for a program in a decimal-comma locale, a semicolon is usually the safer choice.

Dates become serial numbers

Excel stores dates as serial numbers, counting days from a starting point. In the 1900 date system, 1 January 1900 is serial 1. Excel also treats 1900 as a leap year, so it counts a February 29, 1900 that never existed, as serial 60. That makes 1 March 1900 serial 61 and shifts every later date by one day. The bug came from Lotus 1-2-3, and Microsoft has kept the same system for compatibility. Other software can read dates before 1 March 1900 one day off.

When you export a date, you may see 44927 instead of 2023-01-01, or a day off in old dates. The tool converts dates to readable text. If you export manually, format the date column as text in the ISO form yyyy-mm-dd, which every program reads the same way.

Leading zeros disappear

A ZIP code such as 02134 is text, but Excel treats a typed number as a number, so it shows 2134. The zero is gone from the sheet, and the CSV written from it will not have it either. Phone numbers that start with 0 and long ID numbers suffer the same way. Very long numbers can also lose digits, because Excel keeps only about 15 significant digits.

The fix is to store those columns as text before you enter or import the data. When you open a CSV that already has the zeros, use Data, From Text/CSV and set the column to Text, rather than double-clicking the file.

Quoting rules

RFC 4180 defines the common CSV format. Its main quoting rules are:

• A field containing a comma, a double quote or a line break must be enclosed in double quotes.
• A double quote inside a quoted field is written as two double quotes.
• Lines end with CRLF in the specification, although most tools accept LF.

For example, the row "Müller, Anna","She said ""ok""" has two fields: Müller, Anna and She said "ok". The IANA-registered MIME type for CSV is text/csv.

RFC 4180 does not allow a double quote inside an unquoted field, and many parsers reject such a row or split it wrongly. Quote the field properly, or remove the stray character.

A checklist before you export

1. Export as CSV UTF-8, with a BOM if Excel will open the file.
2. Choose the separator your target program expects.
3. Format ID-like columns, such as ZIP codes and phone numbers, as text.
4. Write dates as yyyy-mm-dd.
5. Open the file in a plain text editor and check the first line and a sample of accented names.

Hidden characters such as non-breaking spaces can also cause import errors. The remove hidden characters tool finds and removes them, and hidden characters in text explains where they come from. Once the file is clean, CSV to JSON can convert it for a developer, as described in CSV to JSON and back.

Before you export, the cells themselves are worth a look: the Excel formula checker lists the formulas that show an error or point at another file, and those are exactly the cells that arrive empty in a CSV. After the export, compare the two spreadsheets to see what changed between versions.

Quick answers

Why does my file look right in one program and broken in Excel? When a UTF-8 file has no BOM, Excel on Windows may read it with its local encoding. Adding the BOM, or importing with UTF-8 selected, fixes that.

Do Mac versions of Excel need the BOM? Some sources say they do, so test with a file containing an accented name before you send it.

Can I fix the dates after export? Yes, if they are still serial numbers, but it is better to export them as yyyy-mm-dd from the start.

Is my spreadsheet uploaded when I convert it? The tool runs in your browser, so the file is processed on your own device. You can check this with the upload check guide.