Clean a Messy CSV in DuckDB and Account for Every Row

A CSV can load successfully and still contain unusable invoices. “Pending” is not an amount, February 30 is not a date, and a negative number may violate the rules even though it parses perfectly.

We will keep the original text, convert it deliberately, and split the results into accepted records and a review queue. The goal is a useful clean result with an explanation for everything excluded.

Explore the idea

A number can parse and still be wrong

Choose how much of the invoice contract to check.

rows pass the numeric check

Five amounts can become numbers

That still admits an impossible invoice date and a negative amount. Parsing answers only part of the question.

Start with the export’s conventions

Our fictional invoice export uses ISO calendar dates, a period for decimal fractions, and optional commas as thousands separators. Amounts must be nonnegative. These are invoice rules; refunds would need a different policy.

raw_invoices7 rows

The CSV as text

Seven records from a real CSV created by the setup. Commas are thousands separators under this export’s explicit formatting convention.

id	billed_on	amount
1	2026-01-01	42.50
2	2026-01-02	19.95
3	2026-01-02	pending
4	2026-01-03	NULL
5	2026-02-30	10.00
6	2026-01-03	-5.00
7	2026-01-04	1,200.00

Run along, with the data included

Each Run example button opens an editable SQL shell with its own copy of this sample data. Run the examples in any order. The engine downloads on your first run; sample queries execute in your browser.

Create these tables yourself: full setup SQL

CREATE OR REPLACE TABLE invoice_source(id VARCHAR, billed_on VARCHAR, amount VARCHAR);
INSERT INTO invoice_source VALUES
  ('1','2026-01-01','42.50'), ('2','2026-01-02',' 19.95 '),
  ('3','2026-01-02','pending'), ('4','2026-01-03',NULL),
  ('5','2026-02-30','10.00'), ('6','2026-01-03','-5.00'),
  ('7','2026-01-04','1,200.00');
SET VARIABLE invoice_csv = 'invoices_' || uuid()::VARCHAR || '.csv';
COPY invoice_source TO (getvariable('invoice_csv')) (FORMAT CSV, HEADER);
CREATE OR REPLACE VIEW raw_invoices AS
SELECT * FROM read_csv(getvariable('invoice_csv'), all_varchar = true);
CREATE OR REPLACE VIEW parsed_invoices AS
SELECT id, billed_on, amount,
       try_strptime(trim(billed_on), '%Y-%m-%d')::DATE AS parsed_date,
       TRY_CAST(replace(trim(amount), ',', '') AS DECIMAL(12,2)) AS parsed_amount
FROM raw_invoices;
CREATE OR REPLACE VIEW reviewed_invoices AS
SELECT *, CASE
  WHEN parsed_date IS NULL THEN 'invalid date'
  WHEN amount IS NULL OR trim(amount) = '' THEN 'missing amount'
  WHEN parsed_amount IS NULL THEN 'invalid amount'
  WHEN parsed_amount < 0 THEN 'negative invoice'
  ELSE NULL END AS issue
FROM parsed_invoices;

Download the complete SQL walkthrough

The setup writes a real CSV, then reads it with all_varchar = true. That preserves troublesome values as text instead of asking type inference to decide what they mean. The downloaded CSV contains the same seven records. Its 1,200.00 field is quoted so the comma belongs to the amount, not the column separator.

Separate parsing from acceptance

Trim whitespace, remove the thousands separators permitted by this export, and try the conversions:

Convert values without losing the source text

SELECT id,
       try_strptime(trim(billed_on), '%Y-%m-%d')::DATE AS billed_on,
       TRY_CAST(replace(trim(amount), ',', '') AS DECIMAL(12,2)) AS amount
FROM raw_invoices ORDER BY id;

Expected result · sample data7 rows

id	billed_on	amount
1	2026-01-01	42.5
2	2026-01-02	19.95
3	2026-01-02	NULL
4	2026-01-03	NULL
5	NULL	10
6	2026-01-03	-5
7	2026-01-04	1200

TRY_CAST returns NULL for an amount it cannot convert. try_strptime does the same for a date that does not match the chosen format or calendar. We use DECIMAL(12,2) for the invoice amount so the representation has a declared precision and scale.

The original strings remain in raw_invoices; parsed_invoices adds the converted columns. This matters because a NULL conversion result could mean a missing value or an invalid value. If you overwrite the raw text, you lose that distinction.

Removing commas is appropriate only because the export convention is known. In a decimal-comma locale, 19,95 could mean 19.95. Blindly removing punctuation would turn it into 1995. Likewise, values with more than two fractional digits need an explicit rounding or rejection rule before this pattern is used for them.

Give excluded rows a reason

The reviewed_invoices view in the setup assigns the first applicable issue: invalid date, missing amount, invalid amount, or negative invoice. A row with no issue is accepted.

Keep the rejected rows and their first issue

SELECT id, billed_on, amount, issue
FROM reviewed_invoices WHERE issue IS NOT NULL ORDER BY id;

Expected result · sample data4 rows

id	billed_on	amount	issue
3	2026-01-02	pending	invalid amount
4	2026-01-03	NULL	missing amount
5	2026-02-30	10.00	invalid date
6	2026-01-03	-5.00	negative invoice

Scroll for more columns →

Record 5 has a perfectly parseable amount but an impossible date. Record 6 has a valid negative decimal but breaks our nonnegative-invoice contract. Merely filtering out NULL amounts would admit both.

A first-issue label keeps this example readable. In a fuller quality report, use separate boolean checks or a list of issues when one row can fail several independent rules. Keep the raw ID and fields so someone can investigate or correct it.

Reconcile the counts

A cleaning query should explain its exclusions:

Account for every source row

SELECT count(*) AS input_rows,
       count(*) FILTER (WHERE issue IS NULL) AS accepted,
       count(*) FILTER (WHERE issue IS NOT NULL) AS rejected
FROM reviewed_invoices;

Expected result · sample data1 row

input_rows	accepted	rejected
7	3	4

Seven input records become three accepted and four rejected records. Every row belongs to exactly one branch because acceptance is defined by whether issue is NULL.

Now use that complete acceptance rule in an aggregate:

Your turn · check your answer

Total only accepted invoices

Return one column, accepted_total, summing parsed_amount only for records with no issue.

See the expected result

Your target result1 row

accepted_total
1262.45

Your SQL

Sample data is loaded for you.

Preparing the editor…

Need a hint?

A numeric amount can still belong to an invalid date or violate the nonnegative-invoice rule. Use the complete issue flag.

Show a solution

SELECT sum(parsed_amount) AS accepted_total
FROM reviewed_invoices
WHERE issue IS NULL;

The accepted values are 42.50, 19.95, and 1200.00. The review rows remain available; they have not silently disappeared from the import history.

Distinguish malformed CSV from invalid values

Our file has valid CSV structure. A broken quote or an unexpected number of columns is a different failure, encountered while reading the file. DuckDB’s CSV reject tables can retain information about rejected lines when store_rejects is enabled. Inspect those separately rather than assuming the value-validation view received every physical line.

For a recurring import, record the source filename and batch identifier alongside both accepted and review rows. Count parser rejects as well as value rejects. The next useful step is choosing the latest record per key when a later export contains corrected invoices or repeated customer updates.