Before this: Import everything as text first
The idea
A comma inside "Smith, Jane" is protected by quotes; a quote inside a value is doubled (""). Line breaks can also sit inside quoted values.
Use a real CSV parser and declare the delimiter and quote character. Splitting lines on commas breaks every such row.
After loading, check that every row has the expected number of fields.
Worked example
Input: 1,"Smith, Jane","said ""hi"""
- Visual
Import settings → Delimiter: comma → Quote: double quote
- SQL (DuckDB)
read_csv('f.csv', delim = ',', quote = '"', all_varchar = true)- pandas
pd.read_csv("f.csv", sep=",", quotechar='"', dtype=str)
Result: 3 fields: 1 | Smith, Jane | said "hi"
Common mistakes
- Splitting on commas.
- Assuming the delimiter.
- Not counting fields per row.
Check yourself
How is a quote written inside a quoted CSV value?
As two double quotes.
Practice it
Clean an order-line export.
Next: Encodings and mojibake