CSV Import Failing? Fix Delimiters, Quotes, Leading Zeros and Shifted Columns

In countries that write decimals with a comma, such as Germany, France and Brazil, spreadsheet programs export CSV with a semicolon between fields. The list separator comes from the operating system's regional settings, not from the file format. If you open such a file in a tool that expects commas, every row collapses into one giant column.

• ▸Look at the first line in a plain text editor. If you see semicolons or tabs between the headers, that is your delimiter.
• ▸Tab separated files often have the extension .csv or .tsv and survive copy and paste from spreadsheets, which makes them a safer choice when the data contains commas.
• ▸When you generate files for other people, state the delimiter in your documentation or avoid the question by using JSON for machine to machine transfers.
• ▸The CSV to JSON converter on this site detects comma, semicolon, tab and pipe automatically, which is a fast way to see how a file is really structured.

The widely followed rule, written down in RFC 4180, is that a field containing a comma, a double quote or a line break must be wrapped in double quotes, and a double quote inside such a field is written twice. So the text She said hello, twice becomes the field she said hello, twice with surrounding quotes, and a field containing a quotation mark shows two quotation marks in the file.

Trouble starts when software writes fields without escaping them. A single unescaped quote makes a parser think the field continues, so it swallows the next commas and even the following lines until it finds another quote. The symptom is a file where one record seems to contain half the dataset.

• ▸Find the first row whose column count differs from the header. The problem almost always sits on or just before that row.
• ▸Never build CSV by joining strings with commas. Use a real CSV library on the writing side, because it handles quoting automatically.
• ▸Multi line cells, such as addresses, are legal but break naive line by line readers that split on newlines before parsing quotes.

Spreadsheet programs guess the type of each cell. A ZIP code such as 02134 becomes the number 2134. A product code like 1E5 becomes 100000 in scientific notation. A sixteen digit card or ID number is rounded after the fifteenth digit because spreadsheets store numbers as floating point values with about fifteen digits of precision. Gene names such as SEPT1 and MARCH1 were so often converted to dates that the naming committee renamed the genes in 2020.

• ▸Import the column as text. In Excel use Data, From Text/CSV and set the column type to Text before loading, instead of double clicking the file.
• ▸If you produce the file, quote identifiers and keep them as strings in your data model. Quoting in the CSV does not stop Excel from converting them, so the receiving side must also choose text.
• ▸When you convert CSV to JSON, turn off automatic type conversion for columns that are identifiers. Otherwise 007 becomes the number 7 and the zeros are lost for good.
• ▸Dates are ambiguous: 03/04/2026 is March 4 in the United States and April 3 in most other places. Exchange dates in the ISO format 2026-04-03.

If accented letters show up as é or apostrophes as ’, you have an encoding mismatch rather than a CSV problem. The file is probably UTF-8 and the program is reading it as Windows-1252. Our guide on fixing garbled text explains how to confirm this and repair it.

A UTF-8 byte order mark at the start of the file is invisible in most editors but can end up glued to the first header name, so a column called id is read as the three characters of the mark followed by id and your lookup for id fails. If your first column is mysteriously missing, check for a BOM.

Some exports drop trailing empty fields, so a few rows are shorter than the header. Others add a trailing delimiter, so every row has one phantom column. Spaces after delimiters, trailing spaces inside cells and non-breaking spaces copied from web pages create values that look the same but do not match in lookups.

• ▸Count fields per row and list the distinct counts. More than one distinct count means ragged rows.
• ▸Trim cells during import, and normalize non-breaking spaces to normal spaces.
• ▸Decide what an empty cell means. An empty string, null and a missing field are different things in JSON and in databases, so write it down in your import rules.

Open the raw file in a text editor, not a spreadsheet. Check the first three lines for the delimiter and header. Paste a sample into the CSV to JSON converter and read the record count and the shape of the first records. If the count is not what you expect, look for the first row with a different number of fields. Only after the raw text looks right should you debate types and encodings. This order saves hours because each earlier step can invalidate everything that comes after it.