How to test a CSV import

CSV looks too simple to need testing. In practice it is a family of slightly different formats, and an importer that only ever sees files exported by your own code will fail on the first file a customer exports from Excel. The files below each isolate one difference.

Start with a file that should just work

Begin with sample-customers.csv: 100 records in 9 columns, comma-delimited, UTF-8 without a byte order mark, with a header row. If this file fails, the problem is in the importer, not the data. Check that exactly 100 records arrive, that the header is not imported as a record, and that signup_date and balance arrive as a date and a number rather than as text.

Encoding and the byte order mark

Excel's “CSV UTF-8” option and many Windows tools write a byte order mark (BOM): the three bytes EF BB BF at the start of the file. An importer that does not expect it reads the first column name as \ufeffid instead of id. Lookups by column name then fail for that one column, which is easy to miss.

sample-csv-utf8-bom.csv starts with a BOM and contains Latin, Greek, Cyrillic, Japanese and emoji text. After import, the first column must be named exactly id, and every name must display correctly. To see the BOM yourself:

head -c 3 sample-csv-utf8-bom.csv | od -An -tx1
# prints:  ef bb bf

In Python, opening the file with encoding="utf-8-sig" removes the BOM if it is present and does nothing if it is not, so it is a safe default for files from users:

import csv

with open("sample-csv-utf8-bom.csv", newline="", encoding="utf-8-sig") as f:
    rows = list(csv.DictReader(f))

assert list(rows[0].keys())[0] == "id"   # no invisible character in front
assert len(rows) == 6

Run the round trip as well: export the imported data and compare. Text that comes back as José instead of José has been decoded or encoded twice.

Quoting rules

RFC 4180 lets a field contain commas, quotation marks and line breaks, as long as the field is wrapped in double quotes and inner quotes are doubled. Importers that split each line on commas, or the file on newlines, get these wrong.

sample-csv-quoted-fields.csv has six records, one with a comma inside the name, one with doubled quotes, one with a line break inside a field, an empty field, leading and trailing spaces, and an explicitly empty quoted value. A correct parser returns six records, not seven:

import csv

with open("sample-csv-quoted-fields.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))

assert len(rows) == 7            # header + 6 records
assert rows[1][1] == "Abbott, Ava"
assert rows[2][2] == 'She said "hello" and left'

The newline="" argument matters: without it, Python's text layer interferes with line breaks inside quoted fields. Rows 4 and 6 also show whether your importer tells a missing value from an empty string; decide which you want, and test that it is consistent.

Delimiters and decimal commas

Where the comma is the decimal separator, as in much of Europe and South America, spreadsheet programs save CSV files with semicolons between fields. sample-csv-semicolon-delimited.csv has 25 records in 9 columns, separated by semicolons, with balances written as 2769,84.

Two things must work: the file must not load as a single column, and 2769,84 must become the number 2769.84, not text and not two fields. Either detect the delimiter or let the user choose it:

import csv

with open("sample-csv-semicolon-delimited.csv", newline="", encoding="utf-8") as f:
    dialect = csv.Sniffer().sniff(f.read(4096), delimiters=",;\t")
    f.seek(0)
    rows = list(csv.DictReader(f, dialect=dialect))

assert dialect.delimiter == ";"
balance = float(rows[0]["balance"].replace(",", "."))   # 2769.84

Delimiter sniffing is a guess. Keep a manual override in the interface for the files it gets wrong.

Row counts and larger files

sample-csv-1000-rows.csv has exactly 1,000 records. An import that reports 1,001 counted the header as data. A grid that shows 25 rows per page should have 40 full pages, and sorting the id column must be numeric: sorted as text, 1000 lands straight after 100.

sample-csv-10000-rows.csv (about 815 KB) shows how the import behaves under load. Check that record 10000 arrives, so nothing was cut off by a row limit, timeout or request-size cap; that memory stays flat while the file is read, which means it is streamed rather than loaded whole; and that importing it twice does not leave 20,000 records. For limits beyond that, the 10 MB CSV sample and larger sizes are generated in your browser.

The empty file

A zero-byte file is a common accident: an export that failed half-way, or the wrong file picked. empty-file.txt has no content at all. The importer must refuse it with a clear message, or accept it on purpose as “no records”, without a server error and without leaving an empty dataset that looks like a successful import.

Exports and formula injection

If your application also exports CSV, check what happens to a value such as =1+1 or @SUM(A1:A2) typed by a user. Spreadsheet programs treat a cell that starts with =, +, - or @ as a formula. OWASP's guidance on CSV injection is to prefix such cells with a single quote when exporting. Add a record with one of these values to a test account, export, and open the file in a spreadsheet: it should show the text, not calculate it.

A checklist