CSV import problems: delimiters, quotes, headers and leading zeros

A table-shaped file still needs parsing

CSV is text with delimiter and quoting rules. If each row lands in one cell, inspect the delimiter first. If only some rows shift columns, examine quotes and embedded line breaks before moving cells manually.

A field can contain a comma as data. Replacing every comma with a semicolon changes those values too. Delimiter conversion should parse and write the CSV structure.

Headers become field names

When converting CSV to JSON, headers commonly become object keys. Duplicate, blank or inconsistent headings can make later mappings ambiguous, so review them before conversion.

Postal codes, order identifiers and values with leading zeros may look numeric but belong as text. Neatbo retains numeric strings as text in CSV-to-JSON output; still check whether the destination app automatically converts them to numbers or dates.

Define a duplicate before removing one

Matching names do not necessarily identify the same person. Multiple products on one order do not necessarily indicate duplicate orders. Choose matching columns based on what identifies a record in your data.

Review a small sample before processing the full table. Comparing input and output row counts can reveal excessive removal, though a plausible count alone does not prove correctness.

Changing format does not sanitize formulas

Converting delimiters or exporting JSON does not automatically remove formulas. Spreadsheet software may interpret cells beginning with characters such as an equals sign as formulas; inspect how the destination handles untrusted content.

Check headers, quoted values, leading zeros and representative records, then try an import in the target app. Retain the original CSV instead of relying on a repeatedly repaired copy.

A comma inside a field is still data

In the example below, “Portland, OR” is one city field, and the doubled quotes inside the note represent quotation marks in that field. A parser can preserve those distinctions; a global text replacement cannot know which commas separate columns.

After parsing, confirm that both records have the same three columns. Check the identifier 00123 as text, not as the number 123. If the receiving application reformats it, change the import settings or choose a format with explicit string cells instead of trying to repair the display afterward.

Treat duplicate removal as a separate business decision. An ID plus a line-item number may identify a row better than an order ID alone. Record the matching rule and review the removed sample so a cleaner-looking spreadsheet does not silently discard legitimate rows.

id,city,note
00123,"Portland, OR","He said ""hello"""
00456,Taipei,review

Before you finish

• Keep a copy before delimiter conversion or filtering.
• Check headers, record count and representative quoted fields.
• Distinguish an empty string from a missing business value.
• Review formula-like values separately from structural correctness.

Tools used in this article