Start with the destination, not the file
Write down the required columns, allowed values, date format, decimal separator, and maximum field lengths before editing the export. A technically valid CSV can still fail when its shape does not match the importer.
- Keep the original export unchanged.
- Record the source row count.
- Choose a stable column or group of columns that identifies a record.
Protect values that spreadsheets like to change
Account numbers, ZIP codes, product codes, and large integers often need to stay as text. Spreadsheet software may remove leading zeroes, convert values to dates, or round long numbers before the CSV is saved.
- Open a sample in a plain-text viewer.
- Check leading zeroes and long IDs.
- Use an unambiguous date format such as YYYY-MM-DD when the destination supports it.
Validate quoting, delimiters, and line endings
Fields containing a delimiter, quote, or line break must be quoted correctly. A reliable parser should preserve embedded line breaks and double an internal quote rather than splitting the record.
Deduplicate with an explicit key
Whole-row matching misses records whose non-key fields changed. Define the columns that make a record unique, decide whether the first or last occurrence wins, and export removed rows for review when the data matters.
Common CSV import failures and their fix
| Symptom | Likely cause | Fix |
|---|---|---|
| Row count changed unexpectedly | An embedded line break split one quoted record into two | Confirm the parser preserves quoted line breaks before counting rows |
| Leading zeroes disappeared | Spreadsheet software reformatted the column as a number | Reopen the source as plain text or force the column to text before saving |
| Import rejects a date column | A locale-specific date format does not match the destination | Convert to an unambiguous format such as YYYY-MM-DD |
| Fewer duplicates were removed than expected | The dedupe key included a column that legitimately varies per record | Recheck which columns actually define a unique record |