Excel에서 CSV 깨짐 없이 열기

Quick answer

To make a CSV open correctly in Microsoft Excel, match your operating system’s regional list separator (semicolons ; in Europe, commas , in the US/UK), prepend a UTF-8 Byte-Order Mark (BOM) to preserve international characters, and write CRLF line endings. For large numeric identifiers (over 15 digits), export a genuine .xlsx spreadsheet instead of CSV using our JSON to Excel converter to prevent Excel from rounding values into scientific notation.

---

A CSV that opens correctly in Excel and a CSV that is valid are two different files. Excel has opinions about the format that predate the specification, and it applies them without asking. Every symptom below is Excel’s behaviour rather than a broken export — but all of them can be pre-empted when the file is written.

Every row is in column A

Excel does not look at your file to work out the delimiter. It uses the list separator from your operating system’s regional settings, which is a comma in en-US and en-GB and a semicolon across most of continental Europe. A comma-delimited file opened on a German Windows install has no semicolons in it, so Excel finds no columns and puts each line into column A.

Two fixes. Export with semicolons if you know the file is going to a European Excel — that is what the Excel (European) preset does. Or keep commas and open the file via Data → From Text/CSV instead of double-clicking it, which brings up an import dialog where the delimiter is a dropdown.

There is a third option that is worth knowing about because you will see it in other people’s files: a first line reading sep=; tells Excel which delimiter to use. It works, and it is a Microsoft extension that makes the file invalid for almost every other CSV parser, which is why it is not the default here.

Accented characters arrive as é or ’

The file is UTF-8. Excel, opening a .csv by double-click, assumes the legacy ANSI code page unless the file starts with a byte-order mark — three bytes that identify the encoding. é is two bytes in UTF-8; read as Windows-1252 those two bytes are Ã and ©.

Adding a BOM fixes it, and both Excel presets do. The reason a BOM is not on by default is that it breaks other things: some database importers read it as part of the first column name, and a few older parsers choke outright. It is an Excel-specific accommodation, so it belongs on an Excel-specific setting.

Numbers with a decimal comma read as integers

In a locale that uses the comma as its decimal separator, 1.5 is not one and a half — it is either fifteen or text, depending on Excel’s mood. The Excel (European) preset writes numbers as 1,5 and switches the delimiter to a semicolon so the two do not collide.

Note that this makes the file locale-specific on purpose. Do not use it for a file that is also going into a script.

Long ids become 1.23457E+14

Excel stores numbers as IEEE 754 doubles and displays at most 15 significant digits. An 18-digit order id is silently rounded, and the last three digits are gone — not hidden, gone, because the value in the cell is now a different number.

This is the single most destructive thing Excel does to an export, and it is irreversible once the file is saved. The only reliable fix is to make sure the value is not treated as a number at all.

In a CSV you have very little control over this: Excel decides per cell, and quoting does not stop it. If the ids matter, export a real .xlsx instead, where the cell type is written into the file and a long id stays text. That is the main reason the JSON to Excel workbook converter exists.

Leading zeros disappear

Same mechanism. A zip code of 01234 and a phone number of +441234567890 are strings that look numeric; Excel converts both and drops the leading zero or the plus. Again, unfixable in CSV and fixable in xlsx.

The file looks fine but a script rejects it

The reverse problem. Excel’s CSV output uses CRLF line endings and quotes inconsistently, and a strict parser downstream may want RFC 4180 exactly. Export with the RFC 4180 preset — every field quoted, CRLF endings, no BOM — and the file will be readable by anything, including Excel, at the cost of being a bit larger.

Symptom table

Symptom	Cause	Fix
Everything in column A	Delimiter is not the locale’s list separator	Excel (European) preset, or Data → From Text/CSV
é instead of é	No BOM, so Excel assumed ANSI	Either Excel preset
1.5 shows as 15	Locale uses a decimal comma	Excel (European) preset
1.23457E+14	Excel’s 15-digit numeric precision	Export .xlsx instead
Leading zero missing	Value treated as a number	Export .xlsx instead
Downstream parser rejects the file	Excel-flavoured quoting	RFC 4180 preset

When to skip CSV entirely

If the file is for a person to open in Excel, CSV is the wrong format. It cannot carry a cell type, so every one of the problems above is a coin flip. A real .xlsx file carries types, a frozen header row and column widths, and opens without the “the file format and extension don’t match” warning that a renamed CSV produces.

Use CSV when something is going to parse it. Use xlsx when someone is going to look at it.