Data and structured data
Why are my CSV columns misaligned?
Tools to use
A two-column export imports as three columns, or one record seems to spill onto another line. Do not immediately delete commas or newlines: they may be part of an address or note. Quoting determines whether a comma is a delimiter or data.
Use CSV Editor's table and JSON/CSV conversion to see which cell receives each string. The examples are small fictional UTF-8 data. Diagnose garbled character encoding separately. The goal is correct field boundaries, not making the source look tidy by changing values.
Keep an address comma inside one cell
id,address
001,North, Building AThe header has two fields but the row has two commas. With a comma delimiter, the field counts disagree. Building A is not necessarily a third field: if the whole address is North, Building A, quote that complete value. Do not silently discard extra fields before establishing the intended record shape.
id,address
001,"North, Building A"The table should show 001 and North, Building A as two values, without delimiter quotes inside the address. Convert the same input to JSON and check that 001 remains a string. Another spreadsheet may still infer a number, so verify column types at the next handoff too.
Three visible lines can contain one record
id,note
001,"first line
second line"This is a header and one data record. The note's quotes span a physical newline, keeping both lines in one cell. The table shows one note; JSON represents its newline inside a string. Source line count and record count are different measurements.
Line deduplication or blank-line removal can delete part of a quoted note. Use record-aware CSV operations for CSV data, not techniques meant for line-based logs. If the receiver forbids embedded newlines, agree on a replacement rule and explicitly treat it as a value change.
Double a quote that belongs to the value
id,name
001,"Notebook ""mini"""The name should read Notebook "mini". Outer quotes delimit the field; doubled internal quotes encode one literal quote. JSON's backslash convention is not a substitute for CSV quoting. Keep the intended value separate from the representation used by each format.
Quoting all fields changes source spelling even for simple values. If reparsing yields the same cells, only the representation changed. Different source strings do not automatically mean data loss, while losing a literal quotation mark does. Compare parsed values rather than appearance alone.
If everything becomes one column, check the delimiter
id;amount
001;12.50Read with commas, id;amount may become one header. Select semicolon and check for separate id and amount fields. A parse that returns one column can still be wrong for your data: verify expected names, counts and first/last records. Do not globally replace punctuation inside values to compensate for the wrong delimiter.
The editor interprets input using its selected delimiter; do not assume independent source and destination delimiter controls. Establish a correct parse first, optionally using JSON to inspect values before another conversion. Decimal commas and field delimiters have different roles.
Check blanks, zeroes and formula-like text
A blank cell is not an explicit zero, and CSV may not preserve the distinction between an empty string and JSON null. Replacing every blank with 0 changes missing answers into numeric values. The producer/consumer contract defines those meanings; quoting repairs should not silently invent them.
Before opening output in a spreadsheet, consider text starting with = and similar characters. Formula neutralization prefixes affected values, changing their data. Distinguish a human-facing spreadsheet export from a machine-facing exact-value export. CSV quoting alone is not a universal formula-execution defense.
Keep tiny fixtures for an address comma, multiline note, quoted name and leading-zero ID, with expected record counts and cell values. Two or three fictional records often reproduce a production import problem while making the cause clearer and limiting shared information.