CSV Data Quality: Catch Bad Files Before They Break Things

CSV data quality is two questions. Can this file be read so that every value lands in the right column? And are the values any good? It is tempting to start with the second. The first decides whether the second can be answered at all, because a file whose rows do not line up hands you clean-looking values from the wrong column.

TL;DR: Check structure first (header, row width, encoding, required columns), then column quality (blanks and types), then the values themselves. A structural fault outranks any score. In the sample file that ships with the CSV / Data-Export Schema & Quality Gate ($69), all six columns read CLEAN, the quality score is 100, and the verdict is still BLOCK, because one row has seven fields under a six-column header. For the hands-on steps, start with how to validate a CSV file before you import it.

A file that scores 100 and still gets blocked

The sample is small on purpose. Five orders, six columns: order_id, email, amount, order_date, status, notes. The third order reads:

1003,leo@example.com,18.00,2026-05-04,new,gift,EXTRA

That is seven fields. Read each of the six columns on its own and nothing is wrong. Every order ID is a whole number. Every email is shaped like an email. Every date is a real date. The quality score comes out at 100, and the file verdict is BLOCK.

The reason is alignment. Once one row is the wrong width, nothing guarantees which column a cell belongs to. In the sample the extra field sits at the end, so you can see it. Move the stray comma to the middle of a row, inside a company name nobody quoted, and every field to its right slides over one. The amount column can end up holding a date for that order, and no error is raised unless something checks.

Structure is yes or no. Quality is a score.

The gate keeps the two kinds of check apart. Seven structural checks each pass or fail:

• A header row exists
• No two header cells share a name
• No header cell is blank
• Every row is the same width as the header
• The file decodes as clean UTF-8
• Every column you marked required is present
• No column is empty from top to bottom

Any one failing forces BLOCK. There is no partial credit, because a file that cannot be lined up cannot be half trusted.

Quality is graded per column from 0 to 100. Two parts carry 80 of the 100 points: whether the column's blanks stay inside the allowance you set (25), and whether its values match the type you declared (55). Type match weighs the most because one malformed value in a typed column is the thing that breaks a later step. A column reads CLEAN only when it scores 80 or more, has no type mismatch, and keeps its blanks within the allowance. Otherwise it reads DIRTY. A required column that is not in the file reads MISSING.

The file verdict follows from both. READY means no structural failure and every column CLEAN. FIX means the file parses but a column is DIRTY. BLOCK means a structural check failed, or the quality score fell below 50.

What a schema gate cannot see

A gate is only as strict as the schema behind it, and you write the schema. For each column you give a name, a type, whether it is required, and the share of blanks you will accept. Declare every column as plain text and no value can ever mismatch. The file will pass, and the pass will mean little.

Three more limits are worth knowing before you rely on a READY:

• Column order is not checked. Columns are matched by name. If your import maps by name, that is correct. If it maps by position, a reordered export passes the gate and still loads into the wrong fields.
• A well-formed wrong value passes. 2026-05-04 is a valid date whether or not the order shipped that day.
• The blank allowance is a judgment. The sample schema accepts a notes column that is half empty and an order ID column with no blanks at all. Those numbers fit that file. Nobody can hand you the right ones for yours.

That last one does not have a clean answer. Set the allowance tight and you will stop files that were fine. Set it loose and a column can go quietly empty for weeks.

Where the gate sits

The order is structure, then values, then agreement. A schema gate is the first step. It reads the raw file and tells you what is wrong. It does not clean, repair or rewrite anything, so the fix is yours: correct the export setting, re-export, or send the file back to whoever produced it. Once a file is READY, record-level checks have something solid to stand on. If the file is headed for analysis instead of an import, data profiling is the next look to take at it.

Pairs well with

The Document Field Validator ($79) is the next step down: it runs each record against a rule pack (format, numeric range, required fields, allowed values, and cross-field math such as subtotal plus tax equals total) and returns PASS, REVIEW or REJECT. The Multi-Source Data Reconciliation Engine ($89) takes three or more sources that should describe the same things and marks every key AGREED, DISPUTED or INCOMPLETE. And when the file is really a spreadsheet with formulas behind the numbers, the Spreadsheet Formula & Model Integrity Auditor ($69) reads the .xlsx and grades the formula layer instead of the values.

More in this guide

• How to Validate a CSV File Before You Import It: the five checks, in order.
• Data Schema Validation: Your First Gate on Any Export: what a schema is and what it costs to keep one.
• Common CSV Errors That Break Imports: five errors, from the loud to the silent.
• Why Data Imports Fail (and How to Catch It Upstream): where in the pipeline to put the check.

What is CSV data quality?

It is whether a file can be read so that every value lands in the right column, and then whether those values are usable. Structure comes first: header, row width, encoding and required columns. Blanks and types come second.

How can a file score 100 and still be blocked?

The score grades each column's cells. The structural checks grade whether the cells line up. In the gate's sample file, six columns read CLEAN and the score is 100, but one row has seven fields under a six-column header, so the verdict is BLOCK.

Is a schema check the same as data cleaning?

No. A schema gate reads the file and reports what is wrong. It does not repair or rewrite anything. You fix the export, or a separate cleaning step does.

Does a READY verdict mean the data is correct?

No. It means the file lines up and each column matches the type and blank allowance in your schema. A valid date can still be the wrong date. Checking values against business rules is a later, record-level step.

Where should a schema check sit in a pipeline?

First, on the raw export, before any tool reads a row. Record-level validation and cross-system reconciliation both assume the columns are where they should be.

Should I run it on every export or only once?

On every export that feeds something else. Source systems change column names and export settings between runs, so a file that was READY last month can be a different file today.