Skip to content
toolsdocks

Working with CSV files without breaking them

2 min read · Updated 3 October 2026

CSV ("comma-separated values") looks simple: one record per line, fields separated by commas. In practice there are several variations, and most data problems come from programs guessing differently about them.

Quotes and commas inside fields

A field that contains a comma, a quote or a line break must be wrapped in double quotes, and quotes inside it are doubled:

name,comment
Ann,"Said ""hello"", then left"

Splitting such a line on every comma produces the wrong number of columns. Use a real CSV parser; every tool here does.

Not every CSV uses commas

In many European locales, spreadsheet programs write semicolons because the comma is the decimal separator (1,5 means one and a half). Tab-separated files (TSV) are also common. The CSV editor detects the delimiter from the content; check the preview if the columns look merged.

Encodings

Modern files should be UTF-8. Older exports may be Windows-1252, which turns names like "José" into "José" when read as UTF-8 (or the other way round). Some programs add an invisible byte-order mark (BOM) at the start of UTF-8 files; it is harmless but can appear as a strange character in the first column name if a program does not expect it.

What Excel changes when you open a CSV

Opening a CSV directly in a spreadsheet program can silently convert data:

  • Leading zeros vanish: ZIP code 02134 becomes 2134, product code 007 becomes 7.
  • Long numbers lose digits: a 16-digit card or ID number is shown in scientific notation and its last digits become zeros.
  • Things become dates: 3-4 or MARCH1 can turn into dates.

If you then save, the changes are permanent. To be safe, edit CSVs in a tool that treats every field as text (like the CSV editor), or import into a spreadsheet with the column type set to Text.

Mixed date formats

Is 03/04/2026 3 April or 4 March? It depends on who typed it. A column that also contains 25/12/2026 must be day-first, because there is no 25th month. The date-column normalizer uses that kind of evidence across the whole column and flags the rows it cannot decide.

Converting

Need Excel or JSON instead? The CSV, Excel and JSON converter converts in any direction. Formulas in spreadsheets are exported as their last calculated values, because CSV and JSON have no concept of formulas.