Fix CSV for Excel

About Fix CSV for Excel

You double-click a CSV file and Excel shows it wrong. Maybe every row sits in column A, with all the commas or semicolons still in it. Maybe the names show “Müller” instead of “Müller”, or “???” and strange signs instead of Japanese or Korean.

The data is fine. Excel only reads it the wrong way. There are two causes, and this tool fixes both:

• The separator. Excel in the US, the UK and Asia splits a CSV at commas. Excel in Germany, France, Spain, Italy, the Netherlands and Brazil splits it at semicolons. A file made for one opens in one column in the other.
• The encoding. A CSV file does not say which encoding it uses. Excel guesses, and for a UTF-8 file without a BOM it often guesses wrong.

Open the file here, choose the separator and the encoding your Excel needs, look at the table, and download the fixed CSV. Everything happens in your browser. The file is never uploaded.

Features

• Comma ⇄ semicolon ⇄ tab — change the separator, with correct quoting
• Finds the separator for you — comma, semicolon, tab or vertical bar, and Excel's own “sep=” line
• Finds the encoding for you — UTF-8, UTF-16, Shift_JIS, EUC-KR, Windows-1252, GBK, Big5 and more
• UTF-8 with BOM — the one file Excel opens right in every language
• Old Excel encodings out — Shift_JIS for Japan, EUC-KR / CP949 for Korea, Windows-1252 for Western Europe and the Americas
• Preview table — see the rows and columns before you download; rows that do not fit are marked
• Numbers stay as they are — a decimal comma stays a comma, dates are not touched
• One click to download — the new file is ready before you click

Why a CSV opens wrong in Excel

Everything is in column A. Excel uses the *list separator* of Windows to split a CSV. In countries where the decimal mark is a comma (1,5), the list separator is a semicolon. So a German Excel opens Name,Price,City as one long cell, and a US Excel opens Name;Price;City as one long cell. The fix is to give each Excel the separator it expects.

Letters are broken. A CSV file is only bytes. The same letter is stored with different bytes in different encodings. When Excel opens a CSV, it reads it in the old code page of your Windows (Windows-1252 in the US and Western Europe, Shift_JIS in Japan, CP949 in Korea) — unless the file starts with a BOM. A BOM is three bytes (EF BB BF) at the start of the file that say “this is UTF-8”. With a BOM, Excel reads the file as UTF-8 and every language shows right.

Which separator and encoding your Excel expects

This is what Excel on Windows uses when you double-click a CSV. A file saved as UTF-8 with BOM works in all of them; only the separator is different.

The list separator can be changed in the Windows settings, so a single computer can be different. If you are not sure, choose Tab — every Excel splits a tab-separated file.

The same letters in different encodings

This is why the encoding matters. The same text is stored with other bytes:

A dash (—) means that encoding has no place for the letter. When you save to such an encoding, the tool writes “?” for the missing letters and tells you how many there are. UTF-8 has a place for every letter, so with UTF-8 with BOM nothing is lost.

When UTF-8 bytes are read as Windows-1252, C3 A9 shows as “é”. That is the classic broken text. If your file already contains “é” as text, tick Repair “é” style broken characters under More options.

What the tool changes, and what it leaves alone

The tool reads the CSV into rows and cells, then writes it again with the new separator. A cell gets quotes only when it needs them: when it holds the separator, a quote or a line break. A quote inside a cell is written twice (""), the way Excel does it.

Example — a German file with semicolons:

Name;Preis;Stadt

Müller;1,5;Köln

saved with comma becomes:

Name,Preis,Stadt

Müller,"1,5",Köln

The decimal comma in 1,5 is not changed to a dot. The cell is put in quotes, so Excel keeps it in one column. The tool never changes numbers, dates or text — only the separator, the quotes it needs, the line endings and the encoding. If a US Excel then shows 1,5 as text, change the number format in Excel, or keep the semicolon and change the list separator of Windows.

Frequently asked questions

Why does my CSV open in one column in Excel?

The separator in the file is not the one your Excel uses. US and UK Excel splits at commas; German, French, Spanish, Italian, Dutch and Brazilian Excel splits at semicolons. Choose the other separator here and download the file again.

What is a BOM, and why does Excel need it?

A BOM (byte order mark) is three bytes at the very start of a file. For UTF-8 they are EF BB BF. Excel reads a CSV with this mark as UTF-8. Without it, Excel uses the old code page of your Windows, and letters like é, ü, 日本 or 한글 come out broken.

How do I fix Japanese characters in a CSV (CSV 文字化け)?

Open the file here. The tool finds out if it is UTF-8 or Shift_JIS. Save it as UTF-8 with BOM for a new Excel, or as Shift_JIS for an old system that needs it. Check the table first: if the letters look right there, they will look right in Excel.

How do I fix Korean characters in a CSV (엑셀 CSV 한글 깨짐)?

The same way: open the file, check the table, and save as UTF-8 with BOM. If a program needs the old Korean encoding, choose EUC-KR / CP949.

Does it change my numbers or dates?

No. Every cell keeps its text exactly. A decimal comma stays a comma, and a date stays as it was written. Only the separator, the quotes, the line endings and the encoding change.

What is the “sep=” line?

Some files start with a line like sep=;. It tells Excel which separator to use. The tool reads it, uses it, and leaves it out of the new file, because other programs would show it as a data row.

Is my file uploaded?

No. The file is read and written by your browser. Nothing is sent to a server.

Can it open an Excel file (.xlsx)?

No, this tool is for CSV and other text files. To turn an .xlsx file into CSV, use the Excel to CSV converter.

Related tools

• File Encoding Converter — change the encoding of any text file, not only CSV
• Excel to CSV Converter — save every sheet of an .xlsx file as CSV
• CSV to JSON — turn a CSV table into JSON and back
• Remove Duplicate Rows — clean a list or a table
• CSV to VCF — turn a contact list into a phone contacts file

How to use it

• Open your CSV — drop it on the box, or paste the text. The tool finds the separator and the encoding
• Look at the table. If the columns or the letters are wrong, change Separator in your file, or Read the file as under More options
• Choose the separator for your Excel — comma for US, UK and Asian Excel, semicolon for most of Europe and Brazil, or tab for any Excel
• Choose how to save it — UTF-8 with BOM is right for every new Excel. Pick Shift_JIS, EUC-KR or Windows-1252 only for an old program that needs it
• Press Download — the encoding and the separator are in the file name, for example orders-UTF-8-BOM-comma-AppAndTools.csv