The parser follows the CSV convention properly rather than splitting on commas: a field wrapped in quotes may contain the delimiter, line breaks and doubled quote marks, and all three survive. That distinction is why a naive split fails on the first address field it meets, and it is the single most common reason a CSV conversion produces the wrong number of columns.
The delimiter is detected by trying comma, semicolon, tab and pipe, parsing the first rows with each, and keeping whichever produces the most consistent column count — which is what handles the European convention of semicolons without asking. Values can be coerced, so numbers and booleans arrive as numbers and booleans rather than as strings, and a quoted field is left as a string because quoting it was a statement of intent.
Where CSV files go wrong
-
An unclosed quote swallows the rest of the file:
the parser reports it rather than failing silently, since the symptom otherwise is one enormous final value and no obvious cause.
-
Leading zeros are the classic coercion casualty:
a postcode or an identifier such as 01234 becomes 1234 as a number. Quote those fields in the source, or turn coercion off.
-
Duplicate headers collide:
two columns with the same name produce one key in the output. Rename them in the source before converting.
-
CSV has no encoding declaration:
a file saved as Latin-1 and read as UTF-8 produces mangled accents, and nothing in the format says which it is. Fix that at export.
Why a naive comma split fails on the first address field
A properly quoted CSV field can legally contain the delimiter, a line break, and doubled quote marks, and all three have to survive intact through the conversion. The [msm_tool slug="csv-to-json"] follows that convention rather than splitting on every comma it sees, which is the difference between handling a quoted address correctly and having it fracture into extra columns the moment a comma appears inside it.
The delimiter is detected, not assumed
Comma, semicolon, tab and pipe are each tried against the first rows of the file, and whichever produces the most consistent column count across those rows is the one used for the whole conversion. That is what makes a semicolon-delimited European export load correctly without anyone having to say in advance which character the file actually uses.
Leading zeros are the classic casualty of type coercion
Values are coerced where they look like numbers or booleans, so a genuine number arrives as one instead of as text — useful almost everywhere except an identifier like a postcode that happens to start with a zero, which becomes a shorter number with the leading digit gone. Quoting that field in the source file, or turning coercion off entirely, is what keeps it as the text it actually is.
Duplicate column headers collapse into one key
An object cannot hold the same key twice, so two source columns sharing a header name end up as a single key in the output, with one value overwriting the other. Renaming the columns in the source before converting is the only fix, since nothing in the conversion itself can recover data that collided on the way in.
An unclosed quote swallows everything after it
A quote mark opened but never closed does not fail quietly — without the fix, everything from that point to the end of the file would be read as a single enormous value with no obvious cause. The parser reports the unclosed quote directly instead, which is considerably faster to act on than reverse-engineering one huge field back to the row where it went wrong.
CSV carries no encoding of its own
Nothing in the format states what character encoding a file was saved in, so a file written as Latin-1 and read as UTF-8 produces mangled accented characters with no signal anywhere in the file explaining why. That mismatch has to be fixed at the point the file is exported, since there is nothing in the CSV itself for a converter to detect and correct.