Getting a file in without losing a row

The row count is the first test, and it usually fails

A CSV arrives with 12,400 lines. You load it and the table has 12,317 rows. Or 12,912. Nothing errored. Both numbers mean the loader disagreed with you about where a row starts and ends, and everything downstream is now computed on a different file from the one you were sent.

Count before you load, count after, and do not proceed until you can explain the difference.

wc -l payments.csv        # 12401 lines, one of them the header

SELECT count(*) FROM raw_payments;   -- must be 12400, or you know why not

The tools, and where each puts the file

Postgres has COPY raw_payments FROM '/path/payments.csv' WITH (FORMAT csv, HEADER true), which reads a file on the server’s disk, and \copy in psql, which reads a file on your machine and streams it. On a laptop they are the same disk; against a hosted database only \copy will find your file. DuckDB reads the file in place: SELECT * FROM read_csv('payments.csv'), and CREATE TABLE raw_payments AS SELECT * FROM read_csv(...) keeps it. SQLite has .import --csv payments.csv raw_payments in its shell. MySQL has LOAD DATA LOCAL INFILE. Every one of them takes options for delimiter, quoting, header and encoding, and every one guesses when you do not say.

Where the rows go missing

Embedded newlines. A quoted field may contain a line break: an address, a comment, a product description. wc -l counts that as two lines; a CSV parser counts it as one row. When the line count exceeds the row count by a few dozen, this is usually why, and it is not a fault. When the parser is not told the field is quoted, the reverse happens: one row becomes two broken ones, and the second half lands in the wrong columns.

The wrong delimiter. Excel in a German, French or Brazilian locale writes semicolons between fields, because the comma is the decimal separator there. A loader expecting commas reads each line as a single wide column and reports one column and the right row count, which is the one case where the row count passes and the load is still useless. Look at the first line of the file before loading it.

Ragged rows. A line with fewer fields than the header. Postgres stops with missing data for column "amount" and loads nothing, which is the honest behaviour. DuckDB can be told ignore_errors = true and will skip the row silently, or store_rejects = true to put the rejected lines in a table you can count and read. Skipping silently is how 83 rows vanish; use the version that keeps them.

The characters that are not what they look like

Text files carry no label saying which encoding they use, and the loader has to guess. The symptom of a wrong guess is a name like Renée or Renée. The mechanism is worth knowing once: in UTF-8 the letter é is two bytes, C3 A9. Read those two bytes as Windows-1252 and they decode as Ã and ©; read them with the old Mac Roman table and they come out as √ and ©. The bytes were fine. The table used to interpret them was wrong.

On a Mac or Linux, file -I payments.csv reports the encoding it detects. Tell the loader: COPY ... WITH (ENCODING 'WIN1252') in Postgres, or convert first with iconv -f WINDOWS-1252 -t UTF-8. Loading with the wrong encoding and “fixing” the names afterwards with find-and-replace is the path to a table where some names are fixed and some are doubly broken.

One more invisible character: Excel writes a byte-order mark, EF BB BF, at the start of UTF-8 files. Loaders that do not strip it produce a first column named id, which looks like id and cannot be referenced as id. If your first column is oddly unqueryable, this is why.

Numbers that are text

₹12,500.00, 12,500, (1,200): none of these is a number to a loader. Let them arrive as text. The next lesson is about turning them into numbers in a query you can re-run, rather than in the loader, where a failed cast on row 9,000 either aborts the load or, worse, drops the row.

The habit

Look at the first three lines of the file with your own eyes. Count the lines. Load with the delimiter, quoting, header and encoding stated rather than guessed. Count the rows. Explain the difference. Only then write a query.