How to Reconcile a CSV or Excel File Before It Hits Your System of Record
Summary
- Poor data quality costs the US approximately $3.1 trillion annually, and 88% of spreadsheets contain at least one error.
- CSV and Excel files fail silently because they carry no type metadata, and tools like pandas, MySQL, and PostgreSQL can coerce, truncate, or drop values without raising an error.
- Read files raw with
dtype='object', validate every row against a declarative CSV Schema, and route rejects to a structured file and specific alert before any record writes to the database. - Define a rejection policy per import (skip bad rows, reject the batch, or quarantine for review) and publish an acceptable reject rate so the workflow stays auditable.
- Jinba Flow lets teams build schema validation and reject-routing workflows in plain language and deploy them on-premise in days.
A CSV import can complete without visible error while the file contains corrupted values. A pipeline fails weeks later because a phone number column contains email addresses, or a batch of new user records lands in the database with a blank age field. The error was present at import time and the import did not catch it.
Research from IBM, cited by filefeed.io, puts the annual cost of poor data quality in the US at approximately $3.1 trillion. Panko's research, also cited by filefeed.io, found that 88% of spreadsheets contain at least one error. These numbers are large, but the mechanism behind them is straightforward: bad data enters without error and surfaces later at higher cost.
The solution is a three-step reconciliation workflow applied to every file before it touches the system of record. Read the file without corrupting it. Validate every row against a declared schema. Route rejects to a structured alert before a single bad record writes to the database. This article covers each step in sequence to help teams automate Excel CSV reconciliation and stop treating data cleanup as a post-import task.
Why CSV and Excel Files Fail Silently
The CSV format carries no type metadata. The file itself does not record whether a column is a date, an integer, or a string. That ambiguity creates a direct conflict with any schema-enforced database, and dromo.io notes that skipping validation before a database import is the single biggest mistake teams make.
RFC 4180 exists as a nominal standard, but exporters routinely ignore it. filefeed.io documents the practical consequences: mismatched delimiters (commas versus semicolons), inconsistent quoting rules, encoding differences, and varying line endings. Any one of these causes a parser to misread the file without raising an error.
Excel adds its own class of silent corruption. Opening and re-saving a CSV in Excel can:
- Strip leading zeros from identifiers and phone numbers
- Convert long numeric strings to scientific notation (e.g.,
1234567890123456becomes1.23E+15) - Reformat dates to the user's locale setting
- Prepend a UTF-8 BOM that corrupts the first column header when downstream parsers read it
Native database loaders compound the problem. MySQL's LOAD DATA INFILE and PostgreSQL's COPY offer minimal validation and according to dromo.io, both silently truncate values, insert defaults for missing fields, or drop rows without reporting the loss. SQLite's dynamic typing accepts mistyped data without complaint.
Libraries like pandas introduce a subtler trap. By default, pandas coerces strings such as 'NA', 'NULL', and 'None' into NaN. This corrupts valid data: the ISO country code 'NA' for Namibia, for example, becomes a null value with no warning.
These tools share one outcome: the import succeeds, the error is invisible, and the cost of finding it later is approximately ten times higher than catching it at ingestion.

Step 1: Read the File Without Modifying It
The goal at this stage is to load raw values into memory exactly as they appear in the file. Any automatic type inference at this point destroys the data the validation step needs to inspect.
When using pandas, the critical parameter is dtype='object'. This forces the library to read every column as a string, which preserves leading zeros, long numeric identifiers, and date strings in their original form. The pandas.read_excel documentation confirms that type coercion is a primary silent-corruption source and that dtype='object' prevents it.
Two additional parameters matter for Excel files specifically:
sheet_name=None: By default,pandas.read_excelreads only the first sheet. Passingsheet_name=Nonereturns all sheets as a dictionary of DataFrames.na_values: Set this explicitly to control which strings are treated as null, so that valid codes like'N/A'or'NA'are not misread.
import pandas as pd
df = pd.read_excel(
"new_users_2024-10-26.xlsx",
sheet_name=None, # Read all sheets
dtype='object', # Preserve raw strings
na_values=['', ' '] # Only treat blanks as null
)
For CSV files, the same dtype='object' argument applies to pd.read_csv. Specify the encoding explicitly (encoding='utf-8-sig' handles BOM-prefixed files exported from Excel) and set the sep parameter if the source uses a non-comma delimiter.
Step 2: Validate Against a Declarative Schema
Validation embedded in application code has a structural weakness: it lives in the same codebase as the import logic, which makes it harder to update, share, or audit independently. A declarative schema file solves this. The rules live in a separate, version-controlled file that can be shared with data suppliers and run as a standalone check.
dromo.io identifies three ordered layers that effective pre-import validation must apply in sequence:
- Type validation: Are dates recognisable as dates? Are values in numeric columns actually numeric? Do email fields match an expected pattern?
- Constraint validation: Are required fields present and non-empty? Are values within permitted ranges? Are identifiers unique across the file?
- Format normalisation: Trim whitespace, standardise date formats to ISO 8601, and resolve character encoding. This step runs only after the row has passed the first two layers.
Defining Rules with CSV Schema
CSV Schema is an open-source, text-based, declarative language for describing validation rules for CSV files. It is released under the Mozilla Public License v2.0 and defined in the CSV Schema Language 1.1 specification.
A schema file begins with a version declaration and an optional global directive such as @totalColumns, which asserts the exact number of columns the file must contain. Each subsequent block defines the rules for one column. The rule vocabulary includes:
notEmpty: the field must be present and not blankinteger/range(min, max): the value must be numeric and within boundsis("value1") or is("value2"): the value must match one of a defined setunique: no two rows may share the same value in this columnisEmail: the value must match an email address pattern
A schema for a standard user import file looks like this:
version 1.1
@totalColumns 4
user_id: unique, integer
email: notEmpty, isEmail
age: integer, range(18, 120)
status: is("active") or is("inactive") or is("pending")
This file is the contract. It is readable by a non-developer, storable in version control alongside the import code, and sendable to any external supplier who needs to know what the import requires.
Running Validation
The CSV Validator takes a schema file and a CSV file, verifies the schema itself, and then asserts each rule against every row. Errors are reported with exact row and column coordinates, for example: row 5, column 2: Value 'N/A' is not a valid integer. This specificity is what makes the error report actionable.
The National Archives uses this exact pattern in a regulated setting. They publish their required metadata formats as shareable CSV Schema files, and suppliers run the same validator before submission. The result is fewer errors on both sides because the contract is explicit before data is exchanged.
Platforms that process files at volume can run the same check using the equivalent validation layer in their own stack. The declarative approach holds regardless of the underlying tool: the rules file defines what is accepted, the validator enforces it, and the output separates accepted rows from rejected ones.
Step 3: Route Rejects Before They Reach Your Database
Once validation runs, every row has a status: accepted or rejected. The pipeline must act on that status explicitly. Defaulting to silent insertion of whatever passed, while discarding what did not, recreates the same problem the validation step was designed to prevent.
dromo.io defines three defensible rejection policies:
- Skip the bad row: Accepted rows proceed to the database. Rejected rows are written to a separate output file. This is appropriate when partial imports are operationally acceptable.
- Reject the entire batch: If any row fails, the whole file is held and nothing writes to the database. This is the correct policy for financial data, regulated records, or any context where partial state is unsafe.
- Pause for human review: The file is quarantined and a team member is notified to decide how to proceed. This works when the volume of rejects is low and the data is high-value.
Generating an Actionable Rejects File
The rejects output must be specific enough for the sender to fix the problem without needing to contact the operations team for clarification. A row in the rejects file should include the original data alongside a plain-English error message that names the column and the rule that failed.
A well-formed rejects file contains:
- The original row number from the source file
- The column name and the value that was present
- The rule that was violated (e.g.,
notEmpty,range(18, 120),isEmail) - A human-readable description: "row 47, column email: value 'john.smith@' does not match isEmail"
This format lets the original data owner open the file, sort by error type, and correct in bulk rather than hunting through the original spreadsheet manually.
Sending a Structured Alert
Alert fatigue is a real operational cost. When the same failure mode fires repeatedly with a generic "Import Failed" message, engineers stop reading the alerts. A useful alert is specific enough to require no follow-up question.
When routing to a Slack or Teams channel, the message should include:
- File name:
new_users_2024-10-26.csv - Status:
VALIDATION FAILED - Summary:
1,500 rows accepted. 25 rows rejected. - Top errors:
15 rows: invalid email format. 10 rows: missing required field 'age'. - Next action: Link directly to the rejects file with the instruction to correct and re-upload.
This structure means a single glance at the alert tells the recipient what failed, how many rows are affected, and what to do. No channel search or follow-up is required.
What to Watch for Next
The three steps above form a repeatable pipeline: read raw, validate against a schema, route rejects with a structured output. Together, they automate Excel CSV reconciliation at the boundary between external files and internal systems.
The areas that break this pipeline most often are encoding mismatches on files sourced from non-English locales, schema drift when upstream suppliers add or rename columns without notice, and batch-level rejection policies that are too strict for high-volume imports where a small percentage of rejects is operationally normal.
Set the encoding parameter explicitly for every source. Version-control the schema file and treat a column rename from a supplier as a breaking change. Define the acceptable reject rate for each import job, and configure the policy accordingly.

Bad data does not surface at import time. A reconciliation step before the database write is the only point in the pipeline where catching an error costs less than carrying it.
Frequently Asked Questions
How do I validate a CSV file before importing it into a database?
Validation happens in three steps before a single row reaches the system of record: read the file without type inference, check every row against a declared schema, and route rejects to a structured error report. In practice that means loading the file with dtype='object' so raw strings survive, running a validator such as CSV Validator against a CSV Schema file, and writing failed rows to a rejects file that names the row, column, and rule. Skipping any one of the three lets bad records through silently.
What are the most common CSV import errors?
The most damaging CSV import errors are the ones that raise no exception at all: delimiter mismatches such as commas versus semicolons, inconsistent quoting, encoding and line-ending differences covered by RFC 4180 but routinely ignored by exporters, and Excel auto-converting long digits to scientific notation. Native database loaders add their own failures: MySQL's LOAD DATA INFILE and PostgreSQL's COPY can truncate values, insert defaults for missing fields, or drop rows without reporting the loss. The error only becomes visible when a downstream report or pipeline breaks.
Why does Excel corrupt CSV files, and how do I prevent it?
Excel applies its own locale and formatting rules when it opens and re-saves a CSV, so a simple open-and-save can strip leading zeros, convert 1234567890123456 into 1.23E+15, reformat dates to the local setting, and prepend a UTF-8 BOM that corrupts the first column header downstream. Prevent it by never using Excel as a conversion step: read the original file directly in code, set encoding='utf-8-sig' to strip the BOM, and request exports from the source system rather than round-tripping through a spreadsheet.
How do I stop Excel or pandas from stripping leading zeros in IDs and phone numbers?
Load the column as text rather than as a number. In pandas, pass dtype='object' globally or dtype={'phone': 'str'} for a single column, which forces the raw string to be preserved along with its leading zeros. The root cause in hand-edited files is Excel's own type inference, so the durable solution is to export CSV straight from the source system and validate it programmatically, rather than opening, editing, and re-saving the file in Excel.
Why does pandas read 'NA' as NaN, and how do I fix it?
Because pandas ships with a default list of null tokens that includes 'NA', 'N/A', 'NULL', and 'None', so the ISO country code for Namibia silently becomes a null value. Fix it by declaring the null tokens explicitly (for example na_values=['', ' ']) and adding keep_default_na=False if no automatic coercion is desired. Decide which strings mean "missing" for the data, then record that decision in the schema so the rule is enforced consistently across every file.
What is CSV Schema and how does it work?
CSV Schema is an open-source, text-based, declarative language for describing the rules a CSV file must satisfy. A plain-text schema opens with a version declaration and a directive such as @totalColumns, then lists per-column rules like notEmpty, integer, range(18, 120), unique, and isEmail. A validator then checks every row against it and reports failures with exact row and column coordinates. Because the schema lives in its own version-controlled file, it can be shared with data suppliers and audited independently of the import code.
What should a rejects file contain?
A rejects file should contain the original row number, the column name, the offending value, the rule that failed, and a plain-English message the sender can act on, for example, "row 47, column email: value 'john.smith@' does not match isEmail". That level of detail lets the data owner sort by error type, correct records in bulk, and re-upload without contacting the operations team. A rejects file that only says "import failed" forces a manual re-investigation of the original spreadsheet.
Should a failed import reject the whole batch or only the bad rows?
Reject the entire batch when partial state is unsafe, such as financial records, regulated data, or any context where a half-loaded table is worse than no load at all. Skip the bad rows and let accepted records proceed when partial imports are operationally acceptable, which is usually the case for high-volume marketing or CRM lists. Quarantine the file for human review when reject volume is low and the data is high-value. Whichever policy you choose, define the acceptable reject rate per job and publish it up front.
How do I handle encoding issues in CSV files exported from Excel?
Handle encoding explicitly on every read instead of letting the parser guess. Pass encoding='utf-8-sig' for files exported from Excel, which strips the byte order mark from the first header, set sep when the source uses semicolons or tabs, and confirm line endings match what the parser expects. When a supplier changes encoding or delimiter without notice, treat it as a breaking change to the import contract, the same way a renamed column would be treated.