CSV looks simple because a small file can be read as plain text. Real exports are harder: producers disagree about delimiters, newline conventions, encodings, quoting, headers, and empty values. A reliable import begins by recording the dialect rather than assuming one.
Establish the dialect
RFC 4180 documents a widely used comma-separated convention and registers the text/csv media type. It describes records separated by line breaks, optional headers, and double-quoted fields. A field containing a comma, double quote, or line break needs quoting, and a literal double quote inside a quoted field is represented twice.
For example, this is one header plus two data records, even though the final field spans a physical line break:
id,name,note
1,Ada,"Uses commas, safely"
2,Lin,"First line
Second line"
Counting text lines would report the wrong number of records. Use a CSV parser with an explicit delimiter and quote character.
Detect encoding before changing the file
UTF-8 is the safest default for a new interchange, but exports may contain a UTF-8 byte-order mark or originate in a legacy encoding. If the first header unexpectedly appears as id, a BOM was probably treated as data. Decode bytes once, then parse records; repeated encode/decode cycles can corrupt non-ASCII names.
Profile before import
Before writing anything to a database, calculate:
- Parsed record count and rejected record count.
- Minimum and maximum field count per record.
- Duplicate and blank header names.
- Percentage of blank values per column.
- Values that fail the intended date, number, or identifier type.
- Maximum field length and total decoded size.
Keep an import quarantine for rejected rows. Silently shifting fields to make a malformed row fit is more dangerous than stopping the import.
Separate empty, missing, and null
An empty CSV field is an empty string at the format level. Whether it becomes a database null is an application decision. Document that mapping for every nullable column. The text NULL, N/A, and 0 should not be converted by guesswork.
Protect spreadsheet exports
Values beginning with =, +, -, or @ may be interpreted as formulas by spreadsheet software. If exported data can contain untrusted text, apply the receiving spreadsheet’s recommended neutralization strategy and test it in the actual spreadsheet application. Parser validation alone does not address this downstream execution risk.
A safe import sequence
- Preserve the original bytes and calculate a checksum.
- Select encoding, delimiter, quote, escape, newline, and header rules explicitly.
- Parse into a staging structure with size and record limits.
- Validate the header contract and each typed column.
- Review rejected rows and formula-like cells.
- Import transactionally or in restartable batches.
- Reconcile source, accepted, rejected, and stored record counts.