How to Validate a CSV File Before You Import It

To validate a CSV file before an import, run five checks in this order: the header row, the width of every row, the encoding, the blanks in each column, and the type of each column. The order is the method. Each check assumes the one before it passed, so a result from step five means nothing if step two failed.

Before you start: looking can change the file

The natural first move is to double-click the file and scan it in Excel. That is the one check that can damage the thing it checks. Microsoft's support page on keeping leading zeros and large numbers (opens in a new tab) says it plainly: Excel automatically removes leading zeros and converts large numbers to scientific notation, like 1.23E+15. A ZIP code of 02134 shows as 2134. A sixteen-digit account number is cut to 15 digits of precision and shown in scientific notation.

If you then save, the changed values are the file. So look in a plain text editor, or open a copy, and never save over the export you are about to import.

The five checks

1. The header row. Confirm it exists, that no two columns share a name, and that no header cell is blank. Then confirm every column your import needs is there under the name it expects. A renamed column is a missing column as far as the import can tell.
2. The width of every row. Every row should have as many fields as the header. Check this with a parser. Counting commas per line gives wrong answers, because "Acme, Inc." is one field with a comma inside it, and a quoted note can run across two lines and still be one row.
3. The encoding. Confirm the file reads as UTF-8 with no damaged characters. A name like café that shows up as café was written in one encoding and read in another.
4. The blanks in each column. Decide how many blanks each column may have before you look. An order ID column should have none. A notes column can be half empty and be fine. A column that is blank from top to bottom is a different matter: the export dropped it, and that is a structural fault.
5. The type of each column. Whole numbers in the ID column, decimals in the amount column, one date format in the date column, only the allowed words in the status column.

Why the order holds

Headers go first because every later check is "does column X look right," and that question needs a column X. Row width goes second because a row with one field too many has shifted everything after the break. Checking types on a shifted row grades the wrong cells against the wrong rule.

Encoding goes third because it changes what the characters are. Blanks and types go last. They are the finest checks, and they describe the data only once the coarser three have passed.

Steps one to three are yes or no. Steps four and five are a score.

This split is how the gate turns five checks into one verdict. A header, width or encoding failure is BLOCK, with no partial credit. So is a required column that is absent, or any column that is empty from top to bottom. Blanks and types are graded per column from 0 to 100, with type match worth 55 points and the blank allowance worth 25.

A person reads 05/03/2026 as a date without blinking. A strict import does not, and it cannot tell whether that is March 5 or May 3. FIX is the right call at 89.

What passing does and does not tell you

READY means the file lines up and each column matches the schema you wrote. It is a statement about shape. A phone number can be well formed and belong to someone else. A date can parse and be a typo. Those are record-level questions, and they come after this one.

The verdict is also only as good as the schema. Type every column as text and nothing can mismatch. The tool cannot write the strict schema for you, and how strict is strict enough depends on what the destination does with a bad value: reject it, or quietly store it.

By hand, or with a gate

For one file, by hand is fine. A text editor and some care cover it. It stops working when exports arrive on a schedule from more than one system, because nobody reads the forty-first weekly file as closely as the first.

The CSV / Data-Export Schema & Quality Gate is a script you point at the file and a small JSON schema. It gives the same answer for the same file every time, and when a structural check fails, the output names that check as the thing to fix first. It does not repair the file. That part is still yours.

Pairs well with

Once a file is READY, the Document Field Validator ($79) checks each record against a rule pack and returns PASS, REVIEW or REJECT. If the export is one of several that must agree, the Multi-Source Data Reconciliation Engine ($89) reconciles three or more sources on one match key. And if the file carries customer details, PII Detection for Structured Data ($79) classifies each column by the personal data it holds and returns HANDLED, MASK FIRST or EXPOSED per column. It flags what to handle and does not mask anything itself.