{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"loading-a-file","title":"Getting a file in without losing a row","url":"https://addaly.com/learn/data-and-sql/loading-a-file","position":45,"of":66,"minutes":9,"answer":"Count lines before and rows after every load, and state the delimiter, quoting, header and encoding rather than letting the loader guess, because a wrong guess drops or splits rows without an error.","keyIdea":"Count lines before and rows after every load, and state the delimiter, quoting, header and encoding rather than letting the loader guess, because a wrong guess drops or splits rows without an error."},"body":{"format":"markdown","text":"## The row count is the first test, and it usually fails\n\nA 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.\n\nCount before you load, count after, and do not proceed until you can explain the difference.\n\n```bash\nwc -l payments.csv        # 12401 lines, one of them the header\n```\n\n```sql\nSELECT count(*) FROM raw_payments;   -- must be 12400, or you know why not\n```\n\n## The tools, and where each puts the file\n\nPostgres 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.\n\n## Where the rows go missing\n\n**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.\n\n**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.\n\n**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.\n\n## The characters that are not what they look like\n\nText 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.\n\nOn 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.\n\nOne 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.\n\n## Numbers that are text\n\n`₹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.\n\n## The habit\n\nLook 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."},"check":{"question":"A CSV shows 12,401 lines in `wc -l`, and after loading with `HEADER true` the table has 12,358 rows. No errors were reported. What is the most likely explanation?","options":[{"text":"The loader deduplicated 42 identical rows during the import, which is standard behaviour for CSV readers.","correct":false,"tempting":"No mainstream loader removes duplicates on import; deduplication is a deliberate query step, not a side effect of COPY."},{"text":"Some quoted fields contain line breaks, so 42 rows span two lines each; the line count is inflated and the load is probably correct.","correct":true},{"text":"The file uses semicolons as delimiters, so the loader merged some adjacent lines into single rows to fill the expected number of columns.","correct":false,"tempting":"A delimiter mismatch produces one wide column per line and the full line count, not merged rows."},{"text":"The file is Windows-1252 encoded and rows containing accented characters were rejected by the UTF-8 loader as malformed and dropped without notice.","correct":false,"tempting":"An encoding mismatch mangles characters or raises an error; it does not silently discard whole rows."}]},"prev":{"slug":"messy-data","title":"Cleaning real data without destroying it","url":"https://addaly.com/learn/data-and-sql/messy-data"},"next":{"slug":"everything-as-text-first","title":"Stage as text, then cast in a query you can re-run","url":"https://addaly.com/learn/data-and-sql/everything-as-text-first"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Getting a file in without losing a row', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/loading-a-file (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Getting a file in without losing a row","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Count lines before and rows after every load, and state the delimiter, quoting, header and encoding rather than letting the loader guess, because a wrong guess drops or splits rows without an error.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-row-count-is-the-first-test-and-it-usually-fails","runs":[{"text":"The row count is the first test, and it usually fails"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Count before you load, count after, and do not proceed until you can explain the difference."}]},{"t":"code","lang":"bash","label":"Bash","text":"wc -l payments.csv        # 12401 lines, one of them the header"},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*) FROM raw_payments;   -- must be 12400, or you know why not"},{"t":"h2","id":"the-tools-and-where-each-puts-the-file","runs":[{"text":"The tools, and where each puts the file"}]},{"t":"p","runs":[{"text":"Postgres has "},{"text":"COPY raw_payments FROM '/path/payments.csv' WITH (FORMAT csv, HEADER true)","code":true},{"text":", which reads a file on the "},{"text":"server’s","i":true},{"text":" disk, and "},{"text":"\\copy","code":true},{"text":" in psql, which reads a file on "},{"text":"your","i":true},{"text":" machine and streams it. On a laptop they are the same disk; against a hosted database only "},{"text":"\\copy","code":true},{"text":" will find your file. DuckDB reads the file in place: "},{"text":"SELECT * FROM read_csv('payments.csv')","code":true},{"text":", and "},{"text":"CREATE TABLE raw_payments AS SELECT * FROM read_csv(...)","code":true},{"text":" keeps it. SQLite has "},{"text":".import --csv payments.csv raw_payments","code":true},{"text":" in its shell. MySQL has "},{"text":"LOAD DATA LOCAL INFILE","code":true},{"text":". Every one of them takes options for delimiter, quoting, header and encoding, and every one guesses when you do not say."}]},{"t":"h2","id":"where-the-rows-go-missing","runs":[{"text":"Where the rows go missing"}]},{"t":"p","runs":[{"text":"Embedded newlines.","b":true},{"text":" A quoted field may contain a line break: an address, a comment, a product description. "},{"text":"wc -l","code":true},{"text":" 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 "},{"text":"not","i":true},{"text":" told the field is quoted, the reverse happens: one row becomes two broken ones, and the second half lands in the wrong columns."}]},{"t":"p","runs":[{"text":"The wrong delimiter.","b":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Ragged rows.","b":true},{"text":" A line with fewer fields than the header. Postgres stops with "},{"text":"missing data for column \"amount\"","code":true},{"text":" and loads nothing, which is the honest behaviour. DuckDB can be told "},{"text":"ignore_errors = true","code":true},{"text":" and will skip the row silently, or "},{"text":"store_rejects = true","code":true},{"text":" 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."}]},{"t":"h2","id":"the-characters-that-are-not-what-they-look-like","runs":[{"text":"The characters that are not what they look like"}]},{"t":"p","runs":[{"text":"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 "},{"text":"Renée","code":true},{"text":" or "},{"text":"Ren√©e","code":true},{"text":". The mechanism is worth knowing once: in UTF-8 the letter "},{"text":"é","code":true},{"text":" is two bytes, "},{"text":"C3 A9","code":true},{"text":". Read those two bytes as Windows-1252 and they decode as "},{"text":"Ã","code":true},{"text":" and "},{"text":"©","code":true},{"text":"; read them with the old Mac Roman table and they come out as "},{"text":"√","code":true},{"text":" and "},{"text":"©","code":true},{"text":". The bytes were fine. The table used to interpret them was wrong."}]},{"t":"p","runs":[{"text":"On a Mac or Linux, "},{"text":"file -I payments.csv","code":true},{"text":" reports the encoding it detects. Tell the loader: "},{"text":"COPY ... WITH (ENCODING 'WIN1252')","code":true},{"text":" in Postgres, or convert first with "},{"text":"iconv -f WINDOWS-1252 -t UTF-8","code":true},{"text":". 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."}]},{"t":"p","runs":[{"text":"One more invisible character: Excel writes a byte-order mark, "},{"text":"EF BB BF","code":true},{"text":", at the start of UTF-8 files. Loaders that do not strip it produce a first column named "},{"text":"id","code":true},{"text":", which looks like "},{"text":"id","code":true},{"text":" and cannot be referenced as "},{"text":"id","code":true},{"text":". If your first column is oddly unqueryable, this is why."}]},{"t":"h2","id":"numbers-that-are-text","runs":[{"text":"Numbers that are text"}]},{"t":"p","runs":[{"text":"₹12,500.00","code":true},{"text":", "},{"text":"12,500","code":true},{"text":", "},{"text":"(1,200)","code":true},{"text":": 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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A CSV shows 12,401 lines in "},{"text":"wc -l","code":true},{"text":", and after loading with "},{"text":"HEADER true","code":true},{"text":" the table has 12,358 rows. No errors were reported. What is the most likely explanation?"}],"options":[{"text":[{"text":"The loader deduplicated 42 identical rows during the import, which is standard behaviour for CSV readers."}],"correct":false,"tempting":[{"text":"No mainstream loader removes duplicates on import; deduplication is a deliberate query step, not a side effect of COPY."}]},{"text":[{"text":"Some quoted fields contain line breaks, so 42 rows span two lines each; the line count is inflated and the load is probably correct."}],"correct":true,"tempting":null},{"text":[{"text":"The file uses semicolons as delimiters, so the loader merged some adjacent lines into single rows to fill the expected number of columns."}],"correct":false,"tempting":[{"text":"A delimiter mismatch produces one wide column per line and the full line count, not merged rows."}]},{"text":[{"text":"The file is Windows-1252 encoded and rows containing accented characters were rejected by the UTF-8 loader as malformed and dropped without notice."}],"correct":false,"tempting":[{"text":"An encoding mismatch mangles characters or raises an error; it does not silently discard whole rows."}]}],"order":[0,1,3,2],"why":null},"prev":{"slug":"messy-data","title":"Cleaning real data without destroying it","minutes":9},"next":{"slug":"everything-as-text-first","title":"Stage as text, then cast in a query you can re-run","minutes":8}}}