CSV and Excel pitfalls, and how to avoid them

What a CSV file actually contains

A CSV file is plain text. Each line is a row and each value is separated by a delimiter, usually a comma. A value that contains the delimiter, a double quote or a line break is wrapped in double quotes, and a quote inside such a value is written twice. That is almost the whole of RFC 4180, the closest thing CSV has to a standard:

name,note,addr.city
"Smith, J.","said ""hi""",Paris

What a CSV file does not contain is just as important. There are no types — a CSV cannot say that 02134 is a postcode rather than the number 2134. There is no declared text encoding, no declared delimiter and no formatting. Every program that opens the file has to guess all of that, and most of the problems below are wrong guesses.

Pitfall 1: garbled text (mojibake)

You open a CSV and "café" appears as "café", or Chinese text turns into a wall of symbols. The bytes in the file are fine; the program read them in the wrong encoding.

Modern systems write UTF-8. Excel on Windows, when you double-click a CSV, has long read it in the computer's legacy code page — Windows-1252 in Western Europe and the Americas, GBK on a Chinese system — unless the file starts with a byte order mark (BOM) that announces UTF-8. The reverse also happens: Excel's plain "CSV (Comma delimited)" save option writes the legacy code page, so a file saved that way on a Chinese PC looks garbled in any tool that expects UTF-8. The "CSV UTF-8" option adds the BOM and avoids the problem.

The CSV ↔ JSON converter on this site tries strict UTF-8 first and, if the bytes are not valid UTF-8, falls back to a legacy encoding you can choose (Windows-1252, GB18030 for GBK files, or Shift_JIS). When you export for Excel, tick the BOM option. If a file has already been mangled by being opened, re-saved and re-opened, the damage is usually permanent — go back to the original export.

Pitfall 2: everything in one column

Every row lands in column A, with the commas still visible. In much of continental Europe the comma is the decimal separator, so Excel there both writes and expects semicolons: "Tea;1,50" is a row with two values, the second being one and a half. A comma-separated file opened on such a system, or a semicolon file opened elsewhere, is not split.

The converter here detects the delimiter by looking at the first rows for a character — comma, semicolon, tab or vertical bar — that appears the same number of times on every line, ignoring anything inside quotes. For a file like "name;price", "Tea;1,50", "Cake;3,20" it picks the semicolon, even though commas also appear. In Excel itself, use Data → From Text/CSV rather than double-clicking; the import dialog lets you set the delimiter and encoding explicitly.

Pitfall 3: lost leading zeros and rounded IDs

Postcodes like 02134, product codes like 00731 and phone numbers with a leading zero lose it, because Excel treats anything that looks like a number as a number. Worse, Excel keeps only 15 significant digits. An 18-digit identity number such as 110101199003071234 is stored as 110101199003071000 and displayed as 1.10101E+17. The last three digits are gone, and saving the file writes the damaged value back.

When this site converts CSV to JSON with type detection turned on, values with leading zeros and integers beyond JavaScript's safe range stay as text, so 02134 and 110101199003071234 come through unchanged. In Excel, import with Data → From Text/CSV and set those columns to Text before loading, or prefix them in the source system. Never let Excel open and re-save a file that contains long identifiers.

Pitfall 4: values that turn into dates

Excel converts anything that resembles a date. "1-2" becomes 2 January (or 1 February, depending on the locale), "3/4" becomes a date, and some text codes do too. The best-known victims are human gene symbols: SEPT1 and MARCH1 used to become 1-Sep and 1-Mar, often enough that the HUGO Gene Nomenclature Committee renamed those genes (to SEPTIN1 and MARCHF1) in 2020. The fix is the same as for leading zeros: import the column as Text.

Dates stored as numbers

Real dates in an Excel file are stored as serial numbers — the count of days since the end of 1899. Serial 45000 is 15 March 2023, and a serial of 45000.75 is 15 March 2023 at 18:00. Two quirks matter when converting. First, Excel deliberately treats 1900 as a leap year, a bug inherited from Lotus 1-2-3 for compatibility, so serials before 1 March 1900 are off by a day. Second, workbooks created in the old Mac "1904 date system" count from 1904: the same serial 45000 means 16 March 2027, 1,462 days later. The Excel to CSV tool on this site reads which system each workbook uses and converts both correctly.

Pitfall 5: formulas hiding in data

A value that starts with =, +, - or @ may be treated as a formula when a CSV is opened in a spreadsheet. If your system exports data that users typed in — names, comments, addresses — someone can enter a formula that runs when a colleague opens the export. OWASP calls this CSV injection. The usual defence is to prefix such values with a single quote or a tab when exporting for spreadsheets; for data that goes to other programs, leave it untouched and treat the spreadsheet as the risk.

Getting data out of Excel cleanly

The most reliable way to get data out of a workbook is not to let Excel write the CSV at all. The Excel to CSV tool reads the .xlsx file directly in your browser, which avoids the code page and number-display problems: it takes the stored value of each cell, writes dates as ISO dates (2023-03-15), keeps long numbers as they are stored, and writes UTF-8. Choose the sheet, check the preview, and download CSV or JSON.

It reads the modern .xlsx format only. Old .xls files from Excel 97–2003 use a different binary format; open them in Excel or LibreOffice and save as .xlsx first. Formulas are exported as their last calculated value, which is what a CSV can hold.

Sharing a table as text

For a README, an issue or a chat message, a Markdown table is often better than an attachment. Paste cells copied from Excel or Google Sheets into the Markdown table tool — copying from a spreadsheet produces tab-separated text, which it recognises — choose alignment for each column, and copy the result:

| 城市 |  Pop |
| :--- | ---: |
| 北京 | 21.9 |
| Oslo |  0.7 |

The columns line up in a monospaced editor even with Chinese text, because each Chinese character is counted as two columns wide. Numbers are best right-aligned so their decimal points line up.

A checklist for moving data

1. Export as UTF-8. If the file will be opened in Excel, include a BOM.
2. Know the delimiter, and import with an explicit delimiter rather than a double-click.
3. Mark identifier columns — IDs, postcodes, phone numbers, codes — as text before any spreadsheet touches them.
4. Use ISO dates (YYYY-MM-DD) in exports; they are unambiguous in every locale.
5. Compare row counts before and after. A line break inside a value that was not quoted properly shows up as extra rows.
6. Keep the original export untouched, and work on a copy.

Limits

• Detection is a guess. Delimiter and encoding detection work on typical files; a one-column file or a very short sample can be misread, so the tools let you set both by hand.
• Spreadsheet behaviour varies by version and locale. The Excel behaviour described here is long-standing, but newer versions add options to turn some conversions off; check your own version's import settings.
• Only .xlsx is read. Legacy .xls and .ods files need converting first.
• Everything runs in your browser, so very large files are limited by your device's memory.

Sources and further reading

• RFC 4180: Common Format and MIME Type for CSV Files
• Microsoft: Excel incorrectly assumes that the year 1900 is a leap year
• Microsoft: Excel specifications and limits (15-digit precision)
• OWASP: CSV Injection