EVERYDAY FILE TOOLS / FIELD GUIDE

When CSV imports go wrong

A CSV file is text arranged into records and fields. A spreadsheet adds its own interpretation when it opens that text. A file can therefore have correct structure and still produce an unexpected date, shortened product code or changed price in the next application. Start by separating those two problems.

Start with the symptom. A whole row in one column usually calls for a separator check. A description split across columns calls for a quote check. A product code that lost its zeros calls for a text-import setting. Keep the original file open in a text editor so you can tell whether the problem is in the file or only in the spreadsheet.

1. Check the separator before changing your data

If an entire row appears in one column, the receiving application may expect a different separator. A semicolon-separated export needs the semicolon option here; a tab-separated export needs Tabs. The output of this tool always uses commas. It does not automatically guess your input separator.

Try this example in the CSV checker. Paste both lines, select Semicolons (;), keep First row contains column names checked, then choose Check & tidy:

product;description
00123;"Blue pencils, pack of 12"

The comma inside the description belongs to the value. Quotation marks keep that description together when the output is converted to comma-separated CSV.

The result should show two columns and one data record. The converted file contains:

"product","description"
"00123","Blue pencils, pack of 12"

The separator and quoting changed; the product code and description did not. If your result has one column, check the selected separator before editing the values.

2. Treat quotation marks as structure

A quoted field can contain its separator or a line break. A quotation mark inside a quoted field is represented by two quotation marks. Removing every quote with Find and Replace can break a valid file. This checker reports malformed quotation marks instead of guessing where a field ends.

item,description
00123,"Notebook labelled ""Travel"""

That description contains the text Notebook labelled "Travel". The repeated quotation marks are the CSV representation of a single literal quote; they are not duplicate content to remove.

3. Count records, not lines on the screen

A postal address or note can include a line break inside a quoted value. That is still one field in one record. The checker reports record and column positions. Those positions may differ from the line numbers in a text editor. It also checks that records have a consistent field count; an extra separator can create an unintended empty field.

4. Preserve product codes and other text identifiers

The checker preserves values such as 00123, 12.50 and spaces inside fields. Your spreadsheet may subsequently convert them. A CSV has no column-type metadata, so quoting a value cannot guarantee every spreadsheet will treat it as text.

For Excel versions with Power Query, use the import controls to keep identifiers as text:

  1. Start in Excel and choose Data → From Text/CSV. Select the original file.
  2. Open the query editor from the preview, using Transform Data or Edit, depending on your version.
  3. Select the identifier column and set its Data Type to Text. If asked, choose Replace Current so the earlier automatic type conversion is replaced.
  4. Check that 00123 still appears with both zeros, then choose Close & Load.

If the source file itself now contains 123, the checker cannot know how many zeros were lost. Return to an untouched export or re-export from the original system. Changing a damaged value's cell format does not establish its original contents. See Microsoft's instructions for leading zeros and large numbers for version-specific options.

5. Review formula-like values

Some spreadsheet applications interpret a value starting with symbols such as = as a formula. The checker warns about suspicious values but leaves them unchanged; it does not execute them. A structure check is not a security clearance. Import untrusted columns as text, and use the original source to investigate unexpected formulas.

6. Keep the original and compare a small sample

Download creates a separate file called checked-data.csv. Keep your original, import a small sample into the destination and compare identifiers, prices, row counts and a multiline note before replacing a working dataset. If the file fails the encoding check, export UTF-8 from its original application. Renaming a file's extension does not change its encoding.

Open the CSV checker

CSV quoting reference: RFC 4180. The worked examples describe this checker's output. Excel import steps were checked against Microsoft's documentation on 7 September 2026.