Dev Basics · 4 min read
Working with CSV Files: Delimiters, Quotes, and Encodings
CSV is the most universal data format there is, and also one of the least standardised. Most CSV problems come down to four things: quoting, delimiters, encoding, and size.
Try it yourself with the related tool.
Open a CSV file →Advertisement
Quoting rules
A field containing a comma, a quote, or a line break must be wrapped in double quotes, and a double quote inside it is written twice: "She said ""hi""". Splitting lines on commas by hand breaks on exactly these cases — use a proper CSV parser.
Not always commas
In many European locales, Excel uses semicolons because the comma is the decimal separator. Tab-separated files are common too. If everything lands in one column, the delimiter is wrong.
Encoding and Excel
Save CSV as UTF-8 so accented characters and non-Latin scripts survive. Excel on Windows may misread UTF-8 unless the file starts with a byte-order mark, which is why exports sometimes show é instead of é. Excel also reformats long numbers and anything that looks like a date — import the data as text when that matters.
Large files
Spreadsheets struggle beyond about a million rows. For larger files, use a viewer that streams the data, a database import, or command-line tools. Sorting and filtering in a browser-based viewer keeps sensitive data on your own machine.