How to audit CSV data quality

Auditing a CSV means working through five layers in order: structure, completeness, uniqueness, consistency and validity. Settle the shape of the table first, then measure what is missing, then find out which column identifies a row, then look for values that disagree with their neighbors, then check the ones that are impossible. Rank what you find, write it into a contract, and re-run it next month.

Run the checklist on your file

Free, in your browser, nothing uploaded.

Why the order of the checks matters

Most people audit a file by opening it in a spreadsheet, scrolling for a while, and forming an impression. The impression is usually about the values, because that is what the eye lands on, and it skips the layer underneath entirely. That is the wrong way round. If a row has one field too many, every value after that field is in the wrong column, and a completeness percentage computed over that table is a number about nothing. Structure first, then content, in that order, every time.

The second reason for the order is that later checks depend on earlier ones being clean. A duplicate header name means two columns collapse into one in most readers, so a distinct count on that column is measuring a merge. A wrongly detected delimiter means the whole file is one column and every check reports nonsense with great confidence. Work upward through the layers and each check gets to assume the ones below it have already been argued out.

What follows is the checklist itself. Every item is something a tool on this site actually performs, and where the exact wording of a finding matters it is quoted. You can work down the list by hand in a text editor and a spreadsheet, and you will get there. Or you can drop the file into the profiler and read the ranked list it produces, which is the same list with the arithmetic already done.

Layer one: structure

Six questions, all about the file as text rather than as a table. Answer them before you look at a single value.

Does every row have the header's number of fields? This is the ragged-row check and it is the one that invalidates everything else. The validator reports it with the line number and the count found, in the form "3 rows do not have the header's 6 columns: line 4 has 7, line 9 has 5, line 22 has 7". Line numbers are the real ones in your editor, which sounds obvious until you meet a tool that counts records instead and is off by every quoted newline in the file. A field containing a newline inside quotes is data, not a row break, and the scan tracks quote state character by character so those lines are not miscounted.
Is the delimiter what you think it is? A file exported by a European spreadsheet is often semicolon separated, and a tab-separated file saved with a .csv extension is common enough to be unremarkable. Guessing wrong produces a single-column table and a confident audit of nothing. The parser counts every candidate separator outside quoted fields so the guess has evidence behind it, and if the file opens with Excel's sep=; directive that line is treated as metadata rather than as the header, with the reported line numbers shifted back so they still point where you would look.
Are the line endings consistent? CRLF, LF and CR are counted separately during the scan. Mixed endings in one file get reported, because some readers treat the odd ones out as part of the value, which produces a trailing carriage return glued to the last column of some rows and not others. That is a defect you can stare straight at in a spreadsheet and never see.
Was the encoding damaged on the way here? A U+FFFD replacement character in the text means characters were lost when the file was decoded, so the original was probably not UTF-8. The scan counts them and names the lines they appear on. This is not cosmetic: the accented half of a customer name has already been destroyed by the time you see the diamond, and no downstream fix recovers it. Go back and re-export with the encoding set.
Is there a byte-order mark? A UTF-8 BOM at the start of the file is invisible in most editors and shows up as stray characters in front of the first header name in some readers, which is why a column that looks like order_id refuses to match the string "order_id". It is reported as a warning and stripped before parsing, so the audit is not derailed by it, but you want to know it is there.
Are any header cells blank or repeated? Both are problems and they are different problems. A blank header means the column has no name to refer to, and it gets a placeholder of column_N using its one-based position so it survives into the output. A repeated name is worse, because most readers keep only one of them and the other column is simply lost; the finding names the header and the column positions it occupies, and later duplicates are given a numeric suffix so nothing disappears. Both are reported rather than applied silently.

Layer two: completeness

Now the table is trustworthy, ask what is not in it. Three checks, and the third is the one people skip.

Missing per column, not just overall. A file that is 4% blank overall sounds fine and might be one column that is 90% blank, which is a completely different situation. The profiler reports each column separately, as a percentage to one decimal place with the raw counts beside it: "region" is 22.5% missing: 45 of 200 rows are blank. Anything at or above 20 per cent is raised to HIGH rather than reported flatly, because a column that empty cannot support a group-by and any chart built on it is a chart of the rows that happened to be filled in.
Columns that are entirely empty. A column with a name and no data at all is treated separately from a column that is merely very sparse, and once it is found no other check runs on it, because there is nothing to check. It usually means an upstream field was renamed, or a join produced nothing, or an export template has a column the source system stopped populating. It is quiet, it survives every schema, and it is often the first visible symptom of a pipeline that broke weeks ago.
Blank cells and placeholder strings are not the same thing. Only a truly empty cell, or one holding nothing but whitespace, counts toward the missing percentage. N/A, NULL, unknown, - and none are values, and they behave like values: they inflate the distinct count, they appear as their own category in a group-by, they can make a numeric column read as text, and they never once show up in a completeness figure. Treating them as blanks would be worse, because it would hide the fact that somebody chose to write them. Instead they surface through the consistency layer, where a column of digits carrying three cells of N/A has those three reported as a shape that does not match the rest.

Layer three: uniqueness

Every table has a grain: the thing one row represents. If you cannot name it, you cannot safely join, count or deduplicate the file. Three checks find it, or prove it is not there.

Candidate keys. A single column qualifies when all of its non-blank values are distinct and it is filled in every row. Both conditions are needed. A column populated in three rows out of five thousand is trivially unique and is not a key, and a check that forgot the coverage half would name it as one. The finding reads like "order_id" is a candidate key: all 5,000 of its values are distinct and none is blank. It is ranked NOTE, because it is not a problem, it is the answer to the most useful question you can ask about a file.
Composite keys. When no single column qualifies, pairs are searched, and the smallest ones that identify a row are reported: "store_id" + "week" together identify a row: 1,040 distinct combinations across 1,040 rows. The search is capped, both in the number of columns it will consider and at three suggestions, because on a sixty-column file there are 1,770 pairs and twenty suggestions is noise rather than insight.
Exact duplicate rows. Rows where every single column matches an earlier row. The finding names the count and where the first repeat is, by data row number. Severity depends on scale: past roughly five per cent of the file it is HIGH, below that it is REVIEW, because a handful of repeats is usually a re-run of an export and a fifth of the file repeating is a broken join upstream that is inflating every total you compute.

Note the gap these three leave open on purpose. A row that is duplicated in its key but differs in one column is not an exact duplicate and will not be reported as one. It shows up instead as a candidate key that failed to appear, or as a contract key rule that fails on next month's file. That is the right place for it, because a near-duplicate is a business question and not a text-processing one.

Layer four: consistency

This is where an audit earns its keep, and where the design of the check decides whether it finds anything at all.

Mixed shapes, checked against the dominant shape

A value's shape is what you get when you collapse every run of digits to a single 9 and every run of letters to a single A, leaving punctuation alone. So 2024-01-05 has the shape 9-9-9, 05/06/2024 has 9/9/9, and both ORD-1024 and ORD-99 have A-9. Runs collapse rather than repeat so that length differences do not masquerade as form differences; a value being shorter than its neighbors is a separate question.

The check finds the shape that most non-blank values in the column share and reports everything that does not match it, with a count and up to three examples. Crucially, it compares against the shape, not against an inferred column type. That distinction sounds academic and is the whole check. Consider a column holding 32, abc, a blank, 41 and 29. Infer a type first and the answer is text, because one value is not a number. Then ask "does every value match the inferred type?" and the answer is yes, every value is text, and the check reports nothing. The inference defeated the check exactly when there was something to find. The shape check has no such loop: the dominant shape is 9, four values out of four non-blank share it apart from abc, and abc is named.

Two guards keep this useful. The finding only fires when the dominant shape covers at least 60% of the non-blank values, because a free-text notes column has hundreds of shapes and no dominant one worth naming. And a column that reads mostly as dates is routed to the date checks instead, so the same problem is never reported twice in different words.

Case variants

ACME, Acme and acme are one customer in every reader's head and three in a group-by. The check groups values by their lowercased form and reports any group holding more than one spelling, with the variants shown together and sorted so the most frequent collision comes first. It is ranked REVIEW rather than HIGH because it does not produce a wrong number so much as a split one, and because the fix is a decision about which spelling is canonical rather than a repair.

Leading and trailing whitespace

A value of " Ada Lovelace " will not match "Ada Lovelace" in a join, a filter or a lookup, and most tools keep the spaces as part of the value rather than helpfully removing them. The check counts affected values per column and shows examples with the quotes included, because that is the only way to see a space on a page. A cell holding nothing but whitespace is not counted here; it belongs to the completeness layer as a blank.

Constant columns

A column holding the same value in every non-blank row cannot tell any two rows apart. It is ranked NOTE, not a problem, but it is a signal: often a filter was applied upstream and the column records the filter rather than the data, or a multi-tenant export was scoped to one tenant. If you were planning to group by it, this is the finding that saves you the confusion.

Layer five: validity

Dates that do not exist

2024-02-30 is the most dangerous value in data work, because it does not fail. February 30 is not a day, but new Date("2024-02-30") rolls silently forward to March 1 and returns a perfectly valid date object. No exception, no warning, no log line. The load succeeds, the dashboard renders, and one row has moved to the wrong month, where it will stay until somebody reconciles a total by hand. 2024-13-45 is the same family and slightly more obvious.

The check does not ask the Date constructor. It parses the token itself, verifies the day exists in that month of that year, and separates two outcomes that look identical from the outside: a value that is not a date at all, which is ignored, and a value that looks like a date and is not one, which is reported. That distinction is what stops a notes column from generating a hundred false findings. The finding is ranked HIGH and says so plainly, that a day which does not exist in that month rolls silently over in most tools, so nothing else will tell you.

Mixed date formats in one column

A column holding both 2024-01-05 and 05/06/2024 is a HIGH finding for a simple reason: whatever reads it next will pick one format and misread the rest. And the misreading is invisible, because 05/06/2024 parses cleanly under both the day-first and the month-first reading, landing in June under one and May under the other. The check counts the formats it saw, names them in the finding, and orders them by frequency so you can see which one the file mostly uses and which one leaked in.

Date coverage

For any column that reads as dates, the profile reports the real range from earliest to latest and how many whole days inside that range carry no row at all. That is ranked NOTE, and it answers a question people usually discover too late: whether the export you are analyzing actually covers the period you think it does. The gap count is only computed on a reasonably dense series, because a decade of month-end values would report thousands of gaps and mean nothing by it.

Outliers, with the fences written out

On a genuinely numeric column with at least eight usable values and a non-zero interquartile range, the audit reports Tukey outliers at the standard 1.5x fences. The finding names all four numbers: the fences, and the Q1 and Q3 they were built from, plus up to three of the offending values. "amount" has 2 IQR outliers: values outside -102.5 to 421.5, the 1.5x fences around Q1 87.5 and Q3 231.5 (9,900, 8,750). It is ranked NOTE, because an outlier is a statement about arithmetic and not about correctness, and deleting the interesting rows is how an analysis ends up saying nothing.

Two details are deliberate. The check refuses to run on a column that reads as dates, which is the classic false finding: a date column has quartiles like any other sorted numbers, and reporting an outlier on it is meaningless. And no number in the fence description is ever abbreviated. A tool that renders two different bounds as "2k to 2k" has told you the bounds are identical when they are years apart, which is worse than printing nothing. If you want to explore this further, the outlier finder offers four methods and lets you flag, keep or remove.

Ranking: HIGH, REVIEW, NOTE

A list of thirty findings is a data dump. The same thirty findings sorted by consequence is an answer. Every finding carries one of three levels, and the rule for which one is a single question.

HIGH: the file produces a wrong answer if used as it is. Impossible dates. Mixed date formats. A column missing beyond the high threshold. Duplicate rows past about five per cent of the file. These are not stylistic. Ship the file unchanged and a number somewhere downstream is wrong, and probably wrong quietly.
REVIEW: the answer will be surprising, or badly joined. Case variants that split a category. Whitespace that stops a match. Mixed value shapes. A moderate share of blanks. An entirely empty column. Nothing here is catastrophic on its own, and every one of them will cost somebody an afternoon.
NOTE: a fact worth knowing that is not a problem. Candidate and composite keys. Constant columns. Date ranges and coverage gaps. IQR outliers. These are the findings you read to understand the file rather than to fix it, and they are the ones that most often change what you decide to do with it.

Within a level, findings are ordered by how many rows each one affects, so the biggest problem of a given severity is first. That ordering is why the ranked list is worth reading top to bottom and stopping when you reach the notes: by then you have seen everything that changes an answer.

Turning findings into a contract

An audit you run once is a report. An audit you can re-run is a control. The bridge between the two is a data contract: the rules your findings imply, saved in a form you can apply to next month's file.

The contract is generated from the file's facts, and the rule kinds are deliberately few. A column filled in every row becomes required. A column whose values are all integers, all numbers, all dates or all booleans gets a type rule. A column with more than three values, all distinct, gets a unique rule. A numeric column gets a range. A text column with few enough distinct values gets an allowed list; a text column with many gets a pattern rule holding the shape at least 95% of its values share. And the first candidate key becomes a key rule.

The suggestions lean conservative on purpose, because a rule that fires on next month's perfectly good file trains people to ignore the report, and a report people ignore is worse than no report. So a column that is 99% filled does not become required. A range is widened by ten per cent of the observed span before it becomes a rule, so a file whose amounts happen to top out at 4,910 this month does not fail next month at 4,950, and a column that never went negative is not allowed to go negative just because the margin says so. A pattern rule ships with a tolerance, letting a small percentage differ. Every rule is a suggestion you are expected to edit, not a law.

The rule that governs the whole format: a saved contract contains no data. Column names, types, thresholds and patterns only. No values, no filename, no row count, no sample rows. That is what makes it safe to commit to a repository, attach to a ticket, or send to the team that produces the file, and it is asserted in a unit test so it cannot quietly stop being true as the format grows. The one apparent exception proves the rule: an allowed-values list has to name the values, because the values are the rule, and that is a categorical vocabulary you would put in a schema anyway.

Next month, load the new file, pick the check mode and the saved contract, and you get a pass or a fail. Not a vibe: each rule reports whether it passed, how many rows broke it out of how many checked, and the row numbers, capped, that caused the failure. A key rule that fails names the first data row where a combination repeats. The check also compares the column list both ways, reporting columns the contract expects and the file does not have, and columns the file has that the contract has never heard of, which is how a silently added column gets noticed on the day it appears rather than in a quarter's time.

What to do with what you found

An audit ends in one of four actions, and choosing between them is the actual work. Fix upstream whenever the cause is a system rather than a file: encoding damage, mixed date formats and duplicate headers are all export settings, and cleaning them by hand every month is a subscription to a problem you could cancel once. Fix in place for whitespace, case variants and the handful of impossible dates you can resolve from context. Accept and document for the findings that are real and tolerable, which is what a contract with an edited threshold is for. Refuse the file when a HIGH finding affects the column your whole analysis rests on, and say why, with the row numbers attached.

That last one needs evidence, and the evidence is row level. The audit produces several exports, and one of them is different from the rest. The issue CSV lists the rows that failed, the column, the kind of issue, the severity, the detail, and the raw value that failed, copied out of your file. It is the one export that carries your data, and because of that it is gated behind a confirmation that says so in those words before it is produced. Every other export on the page, the plain report, the standalone HTML, the Markdown, the profile CSV and the profile JSON, carries counts and column names only. If you decline, you get the plain-text report instead, and the page tells you that is what happened.

Disclosing that is not friction, it is the point. A data-quality tool that quietly writes your customer names into a download has taught you nothing about whether to trust it. Everything described on this page runs in your browser tab, on your machine, with no upload endpoint involved, and the one export that carries values asks first.

Frequently Asked Questions

What is the first thing to check in a CSV?

Whether every row has the same number of fields as the header. A ragged row is the only defect that moves every value after it into the wrong column, so nothing you measure afterwards is trustworthy until it is resolved. Read the line numbers, look at those lines in a text editor, and work out whether the cause is an unescaped quote, a delimiter inside an unquoted field, or a newline in the middle of a value. Everything else in an audit can wait until the shape of the table is settled.

Why is 2024-02-30 dangerous when a broken value is not?

Because it does not announce itself. February 30 does not exist, but the JavaScript Date constructor, and most date parsers built on similar rules, roll it forward to March 1 without an error. The file loads, the report renders, and one row is silently in the wrong month. A value that fails outright gets noticed within minutes. A value that succeeds incorrectly can sit in a monthly total for a year. The profiler checks the calendar rather than the constructor, so an impossible day is reported as HIGH rather than absorbed.

Why check value shapes instead of an inferred column type?

Because inferring the type first disables the check exactly when it matters. Take a column holding 32, abc, a blank, 41 and 29. Infer a type and the answer is text, because one value is not a number, and a check that asks whether every value matches the inferred type then passes with nothing to say. The shape check has no such loop: it collapses digit runs to 9 and letter runs to A, finds the shape most non-blank values share, and reports everything that differs. On that column the dominant shape is 9 and abc is reported.

Is a blank cell the same as N/A?

No, and the difference is worth being strict about. Only a truly empty cell, or one holding nothing but whitespace, counts toward the missing percentage. A cell holding N/A, NULL, unknown or a hyphen is a value as far as the file is concerned, so it inflates the distinct count, appears in group-by results, and never shows up in a completeness figure. It gets caught by a different check instead: the dominant-shape check reports it as an outlier of form in a column that is otherwise all digits. Two findings, one cause, and you should read both.

How do I rank what an audit finds?

Three levels, with one question each. HIGH means the file produces a wrong answer if used as it is: impossible dates, mixed date formats, a column a column at or past 20 per cent missing, duplicate rows across a real share of the file. REVIEW means the answer will be surprising or badly joined: case variants, stray whitespace, mixed shapes, moderate gaps. NOTE is a fact worth knowing that is not a problem: a candidate key, a constant column, a date range, an IQR outlier. Findings are sorted by level and then by how many rows each affects.

What is a data contract, and why does it hold no data?

A contract is the rule set an audit produces: which columns exist, which are never blank, which hold integers or dates, which are unique, which stay inside a numeric range, which match a shape, which come from a fixed list. A saved contract carries column names, types, thresholds and patterns and nothing else. No values, no filename, no row count, no sample. That is what makes it safe to commit to a repository or paste into a ticket, and it is asserted in a unit test so it cannot quietly stop being true.

How often should I re-run the audit?

Every time the file arrives, which is the whole point of turning the audit into a contract. A profile tells you what this month's file looks like. A contract tells you whether next month's file is still the same file, and it answers with a pass or a fail plus the rows that caused it. The check also reports columns the contract expects that the file does not have, and columns the file has that the contract does not mention, which is how a silently added column gets noticed on the day it appears.

Does auditing a file mean uploading it?

Not here. The profiler and the validator both run in your browser tab, so the file never leaves the machine. One export is different and says so: the issue CSV lists the rows that failed and the raw values that failed, copied out of your file, and it is gated behind a confirmation that tells you exactly that before it is produced. Every other export on the page carries counts and column names only.

Work the checklist on your own file

Free, no account, no upload. Ranked findings, a contract you can save without any data in it, and row-level evidence when you need it.

Open the CSV profiler