ConvertBox Logo
ConvertBox

CSV problems in Excel: encoding, delimiters, lost zeros

A CSV file states neither its encoding, its delimiter nor its column types, so Excel guesses all three when you double-click. Importing through the Data tab, or converting to .xlsx with text cells, removes the guessing.

ConvertBoxPublished

A CSV file carries no metadata, so Excel guesses

CSV is plain text: lines of values separated by a delimiter. Nothing in the file says which character encoding was used, whether the delimiter is a comma or a semicolon, or whether 00123 is a number or a code. RFC 4180 describes the common form but is not a binding standard, and real exports vary. When you double-click a .csv, Excel resolves each open question with a guess based on your Windows regional settings and the look of each value, without asking. Almost every CSV complaint traces back to one of those three guesses.

Garbled characters: encoding and the byte order mark

Most systems now export CSV as UTF-8. Excel on Windows, opening a file by double-click, assumes the regional legacy code page instead, Windows-1252 in Western Europe and the Americas, unless the file begins with a byte order mark (BOM), the three bytes EF BB BF. Without it, every non-ASCII character is misread: Γ© is stored in UTF-8 as the two bytes C3 A9, and Windows-1252 shows them as two characters, so cafΓ© appears as café.

The reverse failure also happens. A CSV saved by an older Excel in the regional code page is not valid UTF-8, so tools that expect UTF-8 show replacement symbols. The site's CSV viewer and CSV to Excel converter read files as UTF-8 only; re-save a legacy file as CSV UTF-8 first. Their CSV downloads are UTF-8 without a BOM, so opening one by double-click in Excel on Windows shows the same garbling, while the .xlsx output avoids the issue because the workbook format declares its own encoding.

Everything in column A: delimiter versus list separator

Excel does not assume a comma. It splits on the list separator from the Windows regional settings, and in regions where the comma is the decimal mark, including Germany, France and Spain, that separator is a semicolon. A comma-separated file opened there lands entirely in column A; a semicolon file opened on a US or UK machine does the same. Decimal marks follow the same setting, so 1,5 is one and a half on one computer and two fields on another.

An Excel-specific workaround is a first line reading sep=; or sep=, which tells Excel which delimiter to use; other programs treat that line as data, so keep it out of files meant for scripts. The site's CSV viewer detects comma, tab, semicolon and pipe automatically by sampling the first lines, and its download is always comma-separated. The CSV to JSON converter leaves the choice to you, with the same four options.

Values Excel rewrites: leading zeros, long numbers and dates

With the default General format, Excel converts anything that looks like a number or a date. The cell then holds the converted value, and saving the file as CSV again writes that value, so the original text is gone for good.

The site's CSV viewer does no type conversion and shows each cell's exact text, which makes it a quick way to check what a file really contains. CSV to Excel writes every cell as text, so 00123 and 16-digit numbers survive; the trade-off is that numeric columns must be changed with Convert to Number before SUM will count them. CSV to JSON goes the other way by default: its automatic type conversion turns 007 into 7 even when the value is quoted, so switch it off for codes and long IDs.

In the fileAfter double-clicking in ExcelWhy
00123123Read as a number; leading zeros carry no value
12345678901234561.23457E+15, stored as 1234567890123450Numbers keep 15 significant digits
1E51.00E+05Read as scientific notation for 100000
1-2A date such as 2-Jan or 1-FebLooks like a day and month of the current year
MAR1A date (1-Mar)Month abbreviation followed by a number
03/04/20243 April or 4 MarchDay and month order follows regional settings

Quotes, commas and line breaks inside a field

A field that contains the delimiter, a double quote or a line break must be enclosed in double quotes, and a quote inside it is written twice. The record 7,"Smith, Ann","She said ""hi""" has three fields: 7, then Smith, Ann, then She said "hi". Files built by joining values with commas, without quoting, shift columns the first time an address or a comment contains a comma.

A quoted line break is legal, and Excel produces one whenever a cell contains a line break entered with Alt+Enter. The record then spans two physical lines, so anything that works line by line, such as counting lines or splitting on newlines in a script, sees a broken row. The site's CSV viewer keeps such a field in one cell. Its CSV to JSON converter does not: it splits the text into lines before looking at quotes, so multi-line cells come out as misaligned rows.

Importing safely instead of double-clicking

Two routes avoid the guessing. One is converting the CSV to an .xlsx in which every cell is text. The other is Excel's own import dialog, which asks the three questions that double-clicking answers silently. In current Excel for Windows the steps are:

  • Open a blank workbook and choose Data > From Text/CSV rather than opening the file directly.
  • Set File Origin to 65001: Unicode (UTF-8) and check that accented or non-Latin characters look right in the preview.
  • Pick the delimiter the file actually uses.
  • Set Data Type Detection to Do not detect data types, or choose Transform Data and mark code, ID and phone columns as Text, then load.
  • Keep the original CSV untouched and save your work as .xlsx; saving back to CSV writes whatever Excel now holds in each cell.

Producing CSV that survives other people's software

If you generate the file, a few choices prevent most of the trouble downstream. Clean the data first: stray spaces around values, completely empty rows and exact duplicate rows are easier to remove before import than after. The site's data cleaner does those three things in a fixed order and compares whole rows when it removes duplicates.

  • Encode as UTF-8. Add a BOM only when recipients will double-click the file in Excel; some other parsers read the BOM as an invisible character at the start of the first column name.
  • Write dates as ISO 8601 (2024-03-09), which no regional setting can read in the wrong order.
  • Write numbers without thousands separators and with a period as the decimal mark, and document that choice.
  • Quote every field that may contain a delimiter, a quote or a line break, and give every row the same number of fields.
  • When the recipients are Excel users rather than programs, send .xlsx instead; it stores types and encoding explicitly.

Tools mentioned in this guide

Related guides

All guides