Worked CSV Problems and the Patterns Behind Them Explained

Most CSV files that cause trouble are not exotic. They are ordinary exports from ordinary systems, with one thing wrong: dates written two ways, a comma where nobody expected it, a note that spans four lines, a percentage written as text. Each problem is solvable in a few lines, and the solution is easier to find once you recognize which of a small number of patterns you are looking at. This guide takes eight such files, each of which I built as a test case for this series, reproducing a shape that exports commonly have, and solves each end to end with real output. It then pulls out the six patterns and the four steps that every solution shares, and ends with where cleaning should stop and a conversation with the source should start. The deeper background on dialects is in CSV dialects and variants you will run into.

The method: diagnose, state the rule, transform, prove

The same four steps work every time, and the order is the point. Diagnose: look at the file as bytes and as a table, count the fields on each line, list the distinct shapes of values in each column. Do not clean anything yet. State the rule: write, in one sentence per column, what the correct value is and how you recognize each wrong variant. "A date is either YYYY-MM-DD or DD/MM/YYYY, and a slash date is day-first because the file contains 13/03/2026." The sentence is the specification, and if you cannot write it, you do not yet understand the file. Transform: implement the rule in SQL or code that reads the raw file and writes a new one, so it can be rerun and reviewed. Prove: check a property that must hold if the transformation is right, the row count is unchanged, the totals match, the dates are in order, no value remains in a bad shape.

A worked problem is incomplete without the last step. A cleaning script that runs without an error has told you only that it ran. The proof is what tells you it did the right thing, and it is the part that fails loudly when next month's file has a new quirk.

The eight problems at a glance

Pattern one: ambiguity needs evidence

The bank statement has dates in two shapes. Some are ISO (2026-03-04), and some are slash dates (05/03/2026), which could be 5 March or 3 May. A parser that guesses will guess per row or per file, and either guess can be wrong. The way out is evidence inside the file. One row reads 13/03/2026, and there is no thirteenth month, so that row is day-first. A test confirms it: the slash dates that fail to parse as month-first are the proof of the order, and in the file, one of three failed %m/%d/%Y and none failed %d/%m/%Y. If the style is consistent within the file, every slash date is day-first, and 03/04/2026 is 3 April. The check that the result is right is that the statement is in date order after conversion: row 6 became 2026-04-03, after 2026-03-14, which is where it sits in the file. Had it been read as 4 March, the order would have broken.

When the file has no proving row, you cannot decide from the data. Ask the source, or reject the file, rather than pick. Never let a library choose silently. The full walk-through is in a bank CSV with dates in two formats.

Pattern two: structure the parser cannot see

Three problems share a shape: the file has a structure that a standard CSV parser misreads, because the writer broke or bent the rules. Unquoted commas in a product name, as in A100,Cable, USB-C, 2 m,9.90,120, give six fields where the header has four. The fix is to anchor on the fields whose shape you trust: the SKU first, price and stock last, and treat everything in between as the name. The script rejoined Cable, USB-C and 2 m into Cable, USB-C, 2 m and wrote it back quoted, with the field counts of the input being [4, 6, 4, 6, 4] and of the output all 4 (commas inside product names).

Multi-line notes are legal CSV that line-oriented tools mishandle. A file of three customers had wc -l report 7 lines, and a real CSV parser counted 3 records (multi-line notes in a CRM export). And unescaped quotes inside a quoted field are not legal at all: for "/search?q="red shoes" size 9", Python's csv module returned /search?q=red shoes" size 9", mangling the field, while DuckDB with strict_mode=false returned /search?q="red shoes" size 9 (unescaped quotes in a log export). Different tools, different repairs, and the one to use is the one whose output you verified on your own bad lines.

Pattern three: display formats instead of values

The analytics export wrote 12,430 for visits, 42.5% for a bounce rate, $1,204.50 for revenue, (4.1%) for a change, and -, --, n/a and — for missing. Each is a way of presenting a number to a person. The fix is a small function that maps placeholders to NULL, treats parentheses as negative, strips the symbols and converts. A single DuckDB macro did all of it for the four rows: /home became 12,430 visits, a bounce rate of 0.425 and a change of 0.082, and /pricing got NULL for the dash and -0.041 for (4.1%). The proof is that every non-placeholder value parsed, so the count of values that are neither a known placeholder nor a number is zero. Placeholders must be listed, not guessed: a tool that turns anything unparseable into NULL hides new junk (percent signs and dashes in an analytics export).

Pattern four: decoration around the data

The supplier price list had a title line, a note about VAT, a blank line, a header on line 4, category names written once on the first row of each group and blank beneath, a blank line and a footer. The structure is in the layout and not the cells. Skip the first four lines, forward fill the category down, drop blank rows and the footer. In DuckDB, last_value(... IGNORE NULLS) OVER (ORDER BY n) carries the category down, and the file produced five items, three cables and two adapters, each with its category and a decimal price. The proof is that the item count equals the count you see in the source sheet (merged header rows in a supplier price list).

Pattern five: flags glued to values

Statistical offices write 1,204,300 (p) for a provisional population, 5.3 e for an estimate and .. for suppressed. The value and its quality flag share a cell. Split them with a regex into a number and a flag column, and keep both: the flag is information. The checks: every flag belongs to a known list, and the parts sum to the stated total where the data allows it. In the test file, the three regions that had values summed to 3,335,050, and the stated total was 4,100,000, so the suppressed region accounted for 764,950, a figure the source meant to hide, and a reminder to treat such arithmetic with care (footnotes in a government dataset).

Pattern six: too wide to read

A survey export with 300 question columns is a wide table that wants to be long. UNPIVOT ... ON COLUMNS('^Q\\d+$') in DuckDB turned five respondents by 300 questions into rows of respondent, question and answer, dropped the blanks and left 1,057 answers across 299 questions. The proof is that 1,057 equals the number of non-blank cells in the original, counted a different way (a survey export with hundreds of columns).

Look at the shape of the values first

Before writing any rule, find out what shapes the values in each column take. The trick is to replace every digit with 9 and every letter with a, then count the distinct results. A column with one shape is clean, and a column with four shapes is four problems, each of which you can see:

SELECT regexp_replace(regexp_replace("Date", '[0-9]', '9', 'g'), '[A-Za-z]', 'a', 'g') AS shape,
       count(*) AS n, min("Date") AS example
FROM raw GROUP BY 1 ORDER BY n DESC;

shape       n  example
9999-99-99  3  2026-03-04
99/99/9999  3  03/04/2026

Two shapes, three rows each, and the diagnosis of the bank file is done in one query: dates come in two styles, split evenly. Run the same query on the analytics file's revenue column and it lists $9,999.99, $9.99, $999.99 and a lone em dash, which shows that the money has a symbol and thousands separators and that one value is a placeholder. On change it lists (9.9%), +9.9% and -9.9%: parentheses, an explicit plus sign and a minus sign all in one column. That is the specification of the cleaning rule, extracted from the data, without opening the file in an editor. For a file with a hundred columns, run it on each and read only the columns with more than one shape.

Combine it with a count of fields per line, which is the other first look: awk -F, '{print NF}' file | sort | uniq -c on the product file shows lines with 4 fields and lines with 6, which says that an unquoted comma is in play. Look at the bytes too, with xxd file | head, for a byte order mark, carriage returns or a stray tab. These three, shape, field count and bytes, take a minute and decide which pattern you have.

Where the fix belongs: SQL or a script

Either works, and the choice follows the pattern. Value-level rules, parsing a date, stripping a symbol, splitting a flag, are most natural in SQL, where each is one expression on a column, reads left to right and runs fast on large files. Structure-level problems, a line that must be rejoined from its pieces, are easier in a short script with a loop and an explicit rule, since SQL would need to see the structure before it can parse it. The unquoted-comma file is the clearest case: its fix is a ten-line Python function that anchors on the ends of each row, not a query.

A good compromise is a script for the structure and SQL for the values: the script turns the file into a valid CSV, and DuckDB reads that and applies the value rules. The valid CSV in between is useful on its own, because you can open it, count it and give it to someone else. Whichever tool, the output should be a new file or table and never an edit of the original.

Testing a cleaning script

A cleaning script is code that processes data you do not control, and it deserves tests. Keep a small fixture of the awkward rows, a dozen lines that include each quirk, next to the script, and a file of the expected output. A test runs the script on the fixture and compares the result with the expected file, byte for byte. That test is how you notice that a refactor changed the handling of parentheses, or that a library upgrade changed what a parser does with a bare quote, which happens: the behavior of lenient CSV parsers is the part most likely to change between versions.

Add one test for each rule that failed in production. When a new file arrives with n.a. as a placeholder that the script did not know, add a row with n.a. to the fixture before fixing the script, watch the test fail and then fix it. And test the guards by breaking the input on purpose: remove a line from the fixture and check that the row-count proof fails with a useful message. A guard that has never fired is a guard you do not know works.

Files that come back every month

Many of these files are not one-offs. The same supplier sends the same mess monthly, and the second month is where the work pays off or hurts. If the rules were written down and tested, the second month takes a minute. The risk is drift: a new placeholder, a new date style, a column added. Make the script fail on anything it does not recognize, and not guess: an unknown shape of value in a date column should stop the run with the shape in the message, so a person decides what it is. Compare the shape profile of each new file with the last one's, and report any shape that did not exist before; that turns "the format changed" from a surprise in the output into a line in the log (handling a CSV provider who changes columns without warning covers the header side of the same drift).

Put the whole template in one file so it can be read in a sitting. Here is one for the bank statement, with the three stages (stage, rules, proofs) in order:

-- 1. stage: read everything as text, number the rows
CREATE TABLE raw AS SELECT row_number() OVER () AS n, * FROM read_csv('bank_dates.csv', all_varchar=true);

-- 2. rules: one expression per column
CREATE TABLE clean AS
SELECT n,
       coalesce(try_strptime("Date", '%Y-%m-%d'), try_strptime("Date", '%d/%m/%Y'))::DATE AS date,
       "Description" AS description,
       "Amount"::DECIMAL(10,2) AS amount
FROM raw;

-- 3. proofs: fail loudly if an assumption breaks
SELECT CASE WHEN (SELECT count(*) FROM clean) = (SELECT count(*) FROM raw)
            THEN 'rows kept' ELSE error('row count changed') END AS proof_rows,
       CASE WHEN (SELECT count(*) FROM clean WHERE date IS NULL) = 0
            THEN 'all dates parsed' ELSE error('unparsed dates') END AS proof_dates,
       CASE WHEN (SELECT count(*) FROM (SELECT date < lag(date) OVER (ORDER BY n) AS back FROM clean) WHERE back) = 0
            THEN 'dates in order' ELSE error('dates out of order') END AS proof_order,
       CASE WHEN (SELECT sum(amount) FROM clean) = (SELECT sum("Amount"::DECIMAL(10,2)) FROM raw)
            THEN 'total unchanged' ELSE error('total changed') END AS proof_total;

proof_rows  proof_dates       proof_order     proof_total
rows kept   all dates parsed  dates in order  total unchanged

All four proofs passed on the sample. The same shape serves the other seven files: stage as text, one rule per column, proofs that fail loudly. The details of each proof change (item counts, sums against a stated total, a flag whitelist), and the skeleton does not.

What not to repair

Resist fixing what you cannot verify. A phone number with seven digits is not yours to complete, a name in capitals is not necessarily wrong, and a value of 0 in a revenue column may be a real zero or a missing value written as a zero. Repairs that look helpful turn into silent changes of meaning. Mark such values in a flag column and leave them. Equally, do not repair what is the job of an upstream system: a customer duplicated across three IDs is a master-data problem that no CSV cleanup should resolve on the quiet. Report it, keep the raw IDs and let the owner of the data decide.

Strict parsers, lenient parsers and what each is for

Parsers differ in how they treat a file that breaks the rules, and the difference is worth understanding before you reach for a setting. A strict parser stops at the first violation and tells you where. A lenient parser carries on, guessing what the writer meant, and the guess is sometimes right. Neither is better in general. Strictness suits a feed that should be clean, because it turns damage into an error, and leniency suits a file you cannot change and have verified.

The log file with unescaped quotes shows how much the guesses differ. The line 2026-10-02T10:00:04,GET,"/search?q="red shoes" size 9",200 contains a quoted field with quote characters inside it, which RFC 4180 says must be doubled. Python's csv module, in its default mode, read the path as /search?q=red shoes" size 9", moving a quote from the front of the phrase to the end of the field, which is wrong and looks plausible. DuckDB's default sniffer gave up on quoting for the file and returned the fields with the outer quotes still attached ("/search?q="red shoes" size 9"). With strict_mode=false, DuckDB returned /search?q="red shoes" size 9, which is what the writer meant, and handled "/search?q=5" nails" as /search?q=5" nails, a measurement in inches. Three results from one line.

The lesson is not that DuckDB is right and Python is wrong. A lenient mode makes a heuristic choice, here "a quote closes the field only if followed by a delimiter or end of line", and a heuristic that works for this file fails on another whose fields contain ", for a legitimate reason. The discipline is to test the setting on the lines you know are broken, compare the result with what you expect and then check the total: the number of fields per row, and that no row swallowed its neighbors. Keep strict mode on everywhere you can, switch to lenient for the one file that needs it and write a comment saying why.

A reading order for any new file

When a file you have not seen arrives, a fixed order of questions saves time. First, bytes: is there a byte order mark, what are the line endings, is the encoding what the sender says? Second, structure: how many fields does each line have, and are there lines that differ? Third, header: is it on line one, is it one line, are the names unique and stable? Fourth, decoration: titles, notes, blank lines and footers around the data. Fifth, shapes: the digit-and-letter profile of each column, which shows dates, placeholders, symbols and attached flags. Sixth, meaning: what the placeholders stand for, what a zero means, which columns are identifiers and must stay text. Seventh, totals: what the file says about itself, a count in a footer, a sum in a total row, a number in the sender's email, that you can check after the load. Eighth, the cleaning script, with the proofs.

Written that way, it looks like a lot. In practice the first five take under ten minutes with a few commands, and they decide everything after. The files that cause long sessions are the ones where someone started writing the transformation at step three.

Cost, and when it is worth automating

One-off files do not need tests, only proofs: do the work by hand, check the totals and move on. Files that come back monthly deserve the script, the fixture and the guards, because the first hour of care is repaid by the second month. Files that come from a partner who changes them without notice deserve more: drift detection on shapes, a rejects table, and an owner who gets the alert. The cost of getting this wrong is not the time spent cleaning; it is a wrong number that someone acts on, and the proofs are what keep that from happening.

Writing the problem down for the next person

Every worked problem produces knowledge that is worth more than the cleaned file: what the quirk was, how you recognized it and what the rule is. Put it in a short note beside the script, in the same repository. Four lines are enough: the symptom (what went wrong when the file was read naively), the cause (what the writer did), the rule (what the script does) and the proof (what it checks). When the problem recurs a year later, or a colleague meets the same file from a different supplier, that note turns an afternoon into a minute.

It also helps to keep a small library of the shapes, with a sample line for each, so that a new file can be compared against a catalog: two date styles in one column, display numbers with symbols, flags glued to values, merged header cells, unquoted delimiters. Naming the pattern is half the repair, and the six patterns above cover most of what arrives in practice.

Rules that hold for all of them

Keep the raw file as it arrived and never edit it in place. Write the cleaning as a script that reads the raw file and writes a new one, and keep the script in version control with a small test file next to it. Make the script check itself. A one-line guard in SQL does it:

SELECT CASE WHEN count(*) = 6 THEN 'row count ok'
            ELSE error('row count changed: got ' || count(*)) END
FROM raw;

On the bank file it returned row count ok, and changed to 7 it raised Invalid Input Error: row count changed: expected 7, got 6. A cleaning script without such guards is a script that will quietly produce wrong output the day the source changes. Send the rows you cannot fix to a rejects file with the reason, rather than dropping them, and count them in the log (logging rejected rows from a CSV import for review). Record the assumptions in comments or a README: "slash dates are day-first because the file contains 13/03/2026" is the sentence that will save someone an afternoon.

Finally, keep types out of it until the end. Read everything as text, apply the rule, and convert at the last step, so that a failure points at a value and not at a column (why a CSV file has no types and what that costs you).

When to stop cleaning and go back to the source

Cleaning is a cost that recurs every time the file arrives, and a file that needs eight rules to read is a file whose producer should change. Fixing the export is almost always cheaper over a year than maintaining the repair: ask for ISO dates, quoted fields, numbers without symbols, a flat table with one header line and a separate column for flags. Say what you need in a sentence and send a sample. When you cannot change the source, a government dataset or a vendor with no time for you, accept the rules and encode them once, with tests.

Stop cleaning, too, when the rule is a guess you cannot check. A date that could be either order and has no proving row, a name field in which you cannot tell where the product name ends, a footnote you cannot decode: go back and ask rather than build a wrong table with confidence. And be wary of cleaning that discards information, the flag column, the original notes, the raw placeholder: if in doubt, keep it in an extra column. The standard behind all of this is thin, as RFC 4180 says nothing about any of these problems, and the tools' own documentation, for example DuckDB's CSV overview, is where the parser options live.