Data Formats & Debugging
CSV Import Traps With Delimiters, Newlines, Encoding, and BOMs
A practical CSV guide that explains why simple-looking files still fail once they move between spreadsheets, databases, APIs, and shell scripts.
CSV looks easy because the happy path is easy. Real CSV breaks when commas appear inside fields, line breaks live inside quoted text, spreadsheets emit a BOM, or one tool assumes semicolons while another assumes commas.
Check which delimiter, quote rules and character encoding the importer expects.
In brief
What it is: CSV is a delimited text format where quoting, delimiters, line endings, and encoding choices all affect how rows and fields are parsed.
Why it matters: Import failures are often caused by format expectations at the edges rather than by bad underlying data.
Worth knowing: A file can look fine in a text editor and still parse differently across tools because of quoting rules, delimiter assumptions, or an invisible BOM.
Delimiters, quotes, and encoding shape each row
Quoting determines whether a comma is a separator or just part of a field value. The same goes for line breaks inside quoted text. That means “it has one line per record” is not always true. A record can legally span multiple visual lines when a field contains an embedded newline.
This is where copy-paste debugging goes wrong. People inspect by sight and miss the parser rules.
Encoding and BOM issues can be hard to see
A UTF-8 BOM at the start of the file can turn the first header into a subtly wrong string for downstream tools. Delimiter expectations vary by locale and export source. Spreadsheet exports are especially good at surfacing these differences because they hide them behind a friendly UI.
A trustworthy CSV tool should show the parsed columns and the raw first bytes clearly enough that the invisible stops being invisible.
- Quoted commas are field content, not separators.
- Quoted newlines can keep one logical record across multiple physical lines.
- A BOM can poison the first header name silently.
- Delimiter assumptions vary by ecosystem.
Normalize once, then move on
The quickest workflow is usually to parse the file with a tool that shows you exactly how it was interpreted, normalize the delimiter and encoding if needed, and only then feed it into the target import path.
See it in a small example
Notice: That is one record with an embedded newline inside a quoted field, not two separate records.
name,notes
"alpha","line one
line two"
What to verify
- Inspect quoting and delimiter assumptions before changing data values.
- Check for a BOM when the first header behaves strangely.
- Treat embedded newlines as a parser rule, not necessarily a corrupt file.
Common questions
Is CSV always comma-separated?
In practice, many ecosystems use semicolons or tab-like variants depending on locale and export settings.
Can a CSV field contain a newline?
Yes, if it is quoted according to the parser rules.
