How to validate CSV & Excel before database import

A file can be perfectly readable and still be dangerous to import. The useful question is not only “can the parser open it?” but “does this file still match the structure and rules the target expects?”

Many import failures are easy to spot: a malformed delimiter, a broken workbook, or a value that cannot be parsed. The harder cases are the ones that look valid. A column was renamed. Two fields swapped position. A key became duplicated. A date format changed. The load completes, but the target now contains the wrong data.

1. Validate the structure before validating the values

Start with the shape of the file. Check whether the expected columns are present, whether unexpected columns appeared, and whether mappings still point to the intended target fields.

This matters especially when a pipeline maps fields by position. A structurally valid CSV with a changed column order can still load successfully while assigning values to the wrong target columns. Mapping by explicit column name is generally safer, but the mapping itself should still be reviewed when the source changes.

2. Check required fields before the database does

Database constraints are an important final line of defense, but they are a poor first diagnostic tool for an import file. Validate required values before generation or loading so you can identify the affected rows and explain the problem in the context of the source file.

Typical checks include required identifiers, names or codes that drive downstream logic, and fields that are technically nullable in the database but mandatory for the business process.

3. Detect duplicate keys early

Duplicate business or primary keys can turn a simple import into an ambiguous update. Before generating INSERT, MERGE or UPSERT statements, verify the uniqueness of the field used to identify a row.

This is particularly important for recurring imports. A source that was unique last month may not remain unique forever, and duplicate records can create misleading row counts even when the overall number of rows still looks reasonable.

4. Validate data types and dates explicitly

Implicit conversions are convenient until they are not. Numbers may arrive with different decimal conventions, dates may switch format, and text may contain values that only look numeric.

Prefer explicit, conservative checks for the formats your import process actually supports. For dates, a small set of known formats is usually safer than aggressive guessing. For numeric values, decide whether signs, decimal separators, thousands separators and empty values are allowed.

5. Separate invalid rows from valid rows

An import workflow is easier to review when problematic rows are isolated instead of mixed into the generated output. Keep a clean set of rows that passed validation and a separate error set containing the source rows that need attention.

This makes remediation easier and reduces the risk of accidentally including known-invalid rows in generated SQL.

6. Generate SQL only after validation and mapping

SQL generation should be the final step, not the validation mechanism. By the time an INSERT, MERGE or UPSERT statement is produced, the source-to-target mapping, key selection and validation rules should already be explicit.

Generated SQL should still be reviewed and tested before execution against a production database. Validation reduces avoidable errors; it does not replace database backups, transaction controls or application-specific checks.

Database-specific considerations

Oracle: pay particular attention to date formatting, target character set assumptions and MERGE key semantics.

SQL Server: review target types, date literals and whether Unicode text needs explicit handling in your final SQL workflow.

PostgreSQL: review identifier casing, target types and the conflict key used for UPSERT behavior.

A practical pre-flight checklist

• Expected columns are present.
• Unexpected or renamed columns are reviewed.
• Source columns map to the intended target columns.
• Required values are present.
• Key values are unique where required.
• Dates, numbers and other typed values match accepted formats.
• Invalid rows are separated from valid rows.
• Generated SQL excludes known-invalid rows by default.
• Generated statements are reviewed before production use.