Quillfold

CSV opens garbled in Excel, or numbers turn into something else

Published by Quillfold, which makes TableHarvest.

You export a table to CSV, double-click the file, and Excel shows it wrong. There are two separate problems, and they have different fixes:

  1. Garbled text. Accented letters and Japanese or Chinese text turn into strings of other symbols. For example, the two bytes UTF-8 uses for “ü” read as Western European text appear as “ü”. This is an encoding problem.
  2. Changed values. Product code 00123 becomes 123, a 20-digit ID becomes 1.23457E+19, JAN1 becomes a date. This is Excel converting values it thinks it recognises.

Both happen because a CSV file is plain text with no information about what each column contains. Excel has to guess.

Garbled text: the encoding

A CSV file is a series of bytes. To show “ü” or “東”, Excel has to know which encoding the bytes use. Most web data is UTF-8. A UTF-8 file can start with three marker bytes, called a byte order mark (BOM), that say “this file is UTF-8”.

Microsoft’s support article “Opening CSV UTF-8 files correctly in Excel” (dated 2026-05-01, read 2026-10-07) says: “You can open a CSV file encoded with UTF-8 normally if it was saved with BOM (Byte Order Mark). Otherwise, you can open it through either of the following ways.” The ways it lists:

  • Data > Get Data > From File > From Text/CSV, which lets Excel read the file as UTF-8 before loading it.
  • The legacy Text Import Wizard (Get Data From Text).

So there are two fixes:

  • When you make the file: save CSV as UTF-8 with a BOM. If your export tool has a BOM option, turn it on.
  • When you open someone else’s file: don’t double-click it. Use Data > From Text/CSV instead.

Changed values: Excel’s automatic conversions

Microsoft’s support page “Import or export text (.txt or .csv) files” says: “When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data.”

In Microsoft 365 and Excel 2024 (Windows and Mac), four of these conversions can be switched off. Microsoft’s page “Set automatic data conversions” (read 2026-10-07) lists them with these examples:

OptionExample from Microsoft’s page
Remove leading zeros00123 becomes 123
Keep the first 15 digits of long numbers and use scientific notation12345678901234567890 becomes 1.23457E+19
Convert digits around the letter “E” to scientific notation123E5 becomes 1.23E+07
Convert continuous letters and numbers to a dateJAN1 becomes January 1

Where to find them:

  • Windows: File > Options > Data > Automatic Data Conversion.
  • Mac: Excel > Preferences > Edit > Automatic Data Conversion.

The Microsoft 365 Insider blog gives the first versions with these settings as Version 2309 (Build 16808.10000) on Windows and Version 16.77 (Build 23091003) on Mac. Microsoft’s “Set automatic data conversions” page says the conversions apply when “opening a .csv or .txt file, data entry or typing, copy and paste operations from external sources”, so pasting a copied table into Excel is affected in the same way as opening a CSV.

Two limits apply whatever you choose: Excel holds 1,048,576 rows per sheet and 32,767 characters per cell (“Excel specifications and limits”, dated 2026-04-13).

Cells that start with = + - @

A CSV cell that starts with =, +, - or @ can be read by a spreadsheet as a formula. When the data comes from a web page you don’t control, that is a risk: the OWASP project describes it as CSV injection (“occurs when websites embed untrusted input inside CSV files”) and lists those characters, plus tab and carriage return, as the ones that start a formula.

The usual defence is to put an apostrophe in front of such cells so the spreadsheet treats them as text.

The simplest fix: don’t use CSV for Excel

An XLSX file stores each cell with its type: this one is a number, that one is text. Excel opens it without guessing, so there is no encoding to detect and nothing to convert. If the data is going into Excel, export XLSX when you can, and keep CSV for other programs.

What each method does (as of October 2026)

  • Copy and paste: Excel applies its automatic conversions to pasted data (Microsoft, above).
  • Excel, Data > From Text/CSV: the route Microsoft gives for UTF-8 files without a BOM (above).
  • Google Sheets: has its own rules for recognising numbers and dates, which this guide doesn’t cover.
  • Instant Data Scraper: its listing says it saves “to Excel or CSV file (XLS, XLSX, CSV)” (read 2026-10-07).
  • Table Capture: its listing puts “Download tables directly as an Excel spreadsheet or as a CSV file” in its paid tier; the free tier lists copying to the clipboard and exporting to Google Sheets (read 2026-10-07).

Side-by-side comparisons, with the cases where the other extension is the better choice: Instant Data Scraper or TableHarvest? and Table Capture or TableHarvest?

What TableHarvest does

TableHarvest is our extension. These are the defaults in the current build, covered by its automated tests:

  • XLSX on the free plan. Every format is free: XLSX, CSV, TSV, JSON, Markdown and copy.
  • Only clear numbers become numbers in XLSX. A value is written as a number only when it is plainly one: up to 15 digits, no leading zero, no exponent, no 1.000-style grouping. Everything else, including 00123, 20-digit IDs, 123E5 and JAN1, is written as text, exactly as the page shows it. XLSX never contains formulas. Cells longer than Excel’s 32,767-character limit are cut with ”…”.
  • CSV with a BOM. “Excel-friendly (BOM)” is on by default, so a double-clicked CSV opens as UTF-8.
  • Formula guard. On by default for CSV, TSV and copy: cells starting with =, +, -, @, tab or carriage return get an apostrophe in front. Plain signed numbers and phone numbers such as -12.5 or +81 3 1234 5678 are left alone.
  • Copy for pasting. When the format is CSV or XLSX, Copy puts tab-separated text on the clipboard, which spreadsheets paste into columns. Excel’s own conversions still apply to pasted data, so for values like 00123, download XLSX instead.
  • Cleanup in Pro. Pro can turn values into real numbers (removing currency signs and thousands separators, reading (1,234) as negative, % as a fraction, full-width digits) and dates into ISO format (2026-10-07). Ambiguous dates such as 05/10/2026 are left as they are unless the rest of the column shows which order is used.

TableHarvest does not write Shift_JIS or other legacy encodings; CSV is always UTF-8.

When you don’t need any of this

  • You already have a CSV and only need to open it: use Data > From Text/CSV in Excel, or switch off the conversions you don’t want in the Automatic Data Conversion settings.
  • The data is going into Google Sheets, a database or a script, not Excel: plain UTF-8 CSV is fine.

Sources

Read on 7 October 2026.

All guides