Validate CSV Before Import: Catch Every Database Error First (2026)

What's happening: Your database import is failing — or worse, silently truncating data — because one or more CSV rows violate the target table's expectations. Why it matters: PostgreSQL's COPY is atomic, so a single bad value at row 4 million rolls back the entire import; MySQL's defaults can quietly mangle values instead of stopping. The fix: Run the CSV through a local pre-flight — Data Validator checks every row against 7 rule types and 18 data-type formats in your browser, and its postgres-copy and mysql-load presets mirror what the database will actually reject. Root cause: CSVs carry no schema, so nothing enforces types, encodings, or column counts until the database does — at the worst possible moment.

You ran the import at 6 p.m. so it would finish overnight. At 11:40 p.m., COPY hit a date written as 31/02/2025 on row 4,183,207 — and because COPY is all-or-nothing, PostgreSQL rolled back every one of the four million rows it had already accepted.

Nothing is in the table. The window you booked with the DBA is gone. And the file that caused it looks completely fine in a spreadsheet preview, because previews show you the first 100 rows and the poison was 4 million rows deep.

This guide does two things. First, it maps the exact PostgreSQL and MySQL error messages you're seeing to their CSV-side causes and fixes. Second, it gives you a pre-import validation workflow that catches every one of those errors locally — full file, not a sample — before the database ever sees a byte.

Capabilities and limits described here were verified against the Data Validator codebase in July 2026; throughput and scale figures come from its June 2026 benchmark runs (i5-12600KF, 64 GB RAM, Chrome, Windows 11). Your hardware will vary.

Database Error Messages, Decoded

The fastest diagnosis is matching the exact string your database printed. These are the six that account for most failed CSV imports.

The pattern across all six: the database is the first thing in your pipeline that enforces a schema, so it becomes your error reporter by default. The rest of this guide moves that enforcement to your machine, before the import.

Table of Contents

• Why CSV Imports Fail: No Schema Until It's Too Late
• The 7 Checks That Catch Database Rejections
• The Pre-Import Workflow: Validate Against Your Target Table
• Large Files: Why Sampling the First 100 Rows Isn't Validation
• Excel to Database: The Three Silent Corruptions
• Encoding: The Errors That Don't Look Like Errors
• Why Validation Should Never Mean Uploading
• Additional Resources
• FAQ

Why CSV Imports Fail: No Schema Until It's Too Late

CSV files fail database imports because the format carries no type information, no encoding declaration, and no structural guarantees — RFC 4180 defines quoting and delimiters, but nothing enforces it, and nothing at all constrains what a "date" or "integer" looks like. Your target table has strict expectations; the file has none. The import command is the first moment those two realities meet, which is why errors surface mid-import rather than before it. Moving validation upstream — checking the file against the table's rules before running COPY or LOAD DATA INFILE — is the entire game.

The failure economics differ by database, and it's worth knowing which one you're playing against.

PostgreSQL's COPY runs inside a single transaction. One rejected row rolls back everything — clean, but brutal at scale, because a 5-hour import can die at hour 4 with nothing to show. There is no built-in skip-bad-rows option.

MySQL's LOAD DATA INFILE defaults the other way: under non-strict SQL modes it coerces and truncates bad values, records a warning, and keeps going. Your import "succeeds" — with 2024-13-05 stored as 0000-00-00 and a 300-character note silently cut to 255. That's arguably worse, because nobody is paged for a warning.

Either way, the file was the problem, and the file is what you can check in advance.

The 7 Checks That Catch Database Rejections

Seven rule types cover the failure modes databases actually reject on. Each maps to a specific class of import error, so a clean pass across all seven is a strong predictor that COPY will run to completion. Data Validator implements exactly these seven — required, dataType, length, range, regex, enum, and uniqueness — and reports every violation with its original row number, so the fix is a targeted edit rather than a hunt.

1. Required — catches the empty cells that become "missing data for column" or NOT NULL violations.
2. DataType — the workhorse. 18 data-type checks (string, integer, decimal, date, email, phone, URL, boolean, ZIP, SSN, and more) catch the "invalid input syntax" family before PostgreSQL does. Healthcare formats (NPI via Luhn checksum, ICD-10, CPT, taxonomy codes) are validated as format checks — pattern and checksum correctness, not registry lookups.
3. Length — MySQL's "Data truncated" is a length check you didn't run. Match your VARCHAR limits.
4. Range — numeric bounds; catches the negative quantity or year-3024 date that passes type checks but violates business logic and CHECK constraints.
5. Regex — custom patterns for anything domain-specific: SKUs, account codes, locale-specific postal formats.
6. Enum — status columns and category fields that must match a fixed list; the difference between active and Active is a foreign-key failure waiting to happen.
7. Uniqueness — duplicate keys are the errors that survive every visual inspection. A full-file uniqueness pass catches the collision on rows 8,204 and 6,911,332 that no preview would ever show side by side. For heavy dedup work beyond validation, Remove Duplicates handles the removal itself.

If a column is failing checks you didn't expect, the 60-second import diagnosis guide walks the triage order.

The Pre-Import Workflow: Validate Against Your Target Table

The operational version of "validate first" is a five-step pre-flight that mirrors your target table's rules, run locally, on the full file. With presets doing the setup, the whole pass takes minutes even on files that would take an hour to fail in the database.

1. Open the file in Data Validator. CSV input streams — there's no upper size limit on the CSV path, and the file never leaves your browser.
2. Apply the preset that matches your target. The postgres-copy and mysql-load presets apply the checks those import paths reject on across every column; 12 more presets cover CRM and platform targets. Auto-detect then proposes column-specific types (it will flag an email column as email, a date column as date) which you can accept or adjust.
3. Tighten to your schema. Set length rules to your VARCHAR limits, enum rules to your status values, range rules to your business bounds. This is the "against the target table" part — two minutes with your CREATE TABLE statement open in another tab.
4. Run the full-file validation. Every row is checked — not a sample. Failures come back with original row numbers and exact reasons, and the counts are exact even when they run past what the results panel displays.
5. Export and fix. Export the failed rows (the export is complete — all failing rows, even when there are hundreds of thousands), fix them at the source or in the file, and re-run until clean. Then run your COPY knowing what the answer will be.

One honest note: manual rule creation beyond presets and auto-detect is currently marked coming soon in the tool — the preset-plus-adjust workflow above is the supported path today, and it covers the database-import cases this guide targets.

Large Files: Why Sampling the First 100 Rows Isn't Validation

Most validation advice quietly assumes small files: "open it and look," "check the first rows," "load a sample into a script." Sampling finds systematic problems — a wrong delimiter, a shifted header — but the errors that kill imports are point defects: one European-formatted date, one unescaped quote, one duplicate ID, four million rows deep. The math is unforgiving — a 100-row sample of a 10-million-row file inspects 0.001% of it, and COPY's atomicity means the other 99.999% is where the rollback lives.

Full-file validation at scale is an engineering problem — a naive approach loads everything into memory and dies around the size where you needed help most. Data Validator's June 2026 benchmarks validated 228 million rows in a 10.4 GB CSV at constant memory, sustaining roughly 265,000 rows per second on that run; format-only passes on smaller files run near 1 million rows per second, and single-column uniqueness cleared 10 million rows at about 685,000 rows per second (results vary with hardware and rule mix). The point isn't the specific figures — it's that "validate the whole file" stays practical at the sizes where sampling fails you. That constant-memory approach is the same architecture behind processing 10 million CSV rows in the browser.

If your import target has row limits of its own, CSV Splitter chunks the validated file afterward — validate once at full size, then split for delivery.

Excel to Database: The Three Silent Corruptions

A large share of "CSV" imports are Excel exports, and Excel introduces three corruptions that pass every visual check and fail in the database.

Date serials. Excel stores dates as day counts (45,123 instead of 2023-07-16). Depending on export settings, your date column arrives as integers — type-valid, semantically garbage. A date-rule pass catches the column instantly.

Leading-zero loss. ZIP codes, account numbers, and phone numbers opened in Excel get "helpfully" converted to numbers: 02134 becomes 2134. A regex or ZIP check flags the shortened values; the real fix is re-exporting with the column formatted as text.

Scientific notation. Long IDs become 1.23E+15 on export — irreversibly, because precision is already gone. Validation catches it (fails both integer and length checks); prevention means text-formatting ID columns before export.

Convert with Excel to CSV Converter and then validate the CSV — validating the post-conversion file is what catches conversion damage. Worth knowing while you're in Excel-land: worksheets cap at 1,048,576 rows, so any "million-row Excel file" has already been truncated once.

Encoding: The Errors That Don't Look Like Errors

Encoding failures produce the strangest import symptoms: Müller arriving as Müller, a "column not found" error on a header that looks identical to the one you typed, or PostgreSQL rejecting the file's very first byte.

The header case is usually a UTF-8 byte order mark — three invisible bytes (EF BB BF) that Excel prepends, making your first column name \ufeffid instead of id. The mojibake case is a Windows-1252 file read as UTF-8, or vice versa. Both are mechanical to fix once identified: declare the right encoding in your import command, or convert the file outright with Format Converter.

The accounting CSV encoding guide covers the full UTF-8 / Windows-1252 / BOM triage if encoding is your main suspect.

Why Validation Should Never Mean Uploading

Look at what you're validating before an import: customer tables, patient claims, payroll, transaction logs — by definition, the data sensitive enough to have a schema. Most online CSV validators process that file on their servers, and under standard SaaS terms, uploaded files are typically retained for 30–90 days. For personal data, that upload creates immediate GDPR exposure — data minimization under Article 5(1)(c) is hard to argue when a full customer table sat on a third-party validation server. For healthcare files, sharing PHI with a vendor generally requires a business associate agreement before the first byte moves.

SplitForge's validator runs entirely in your browser via Web Workers — the file is read locally, validated locally, and never transmitted. There is no server-side copy because there is no server in the path. That's also why file size can scale to the 10 GB range: your machine does the work, so there's no upload bottleneck and no vendor quota.

Client-side processing doesn't make a workflow compliant by itself — but it removes the single largest exposure in the pre-import pipeline, which is handing your rawest data to a third party for a formatting check. Validating before a SaaS or platform upload is its own workflow with its own checklist — the validate before upload guide covers that side.

Additional Resources

Database documentation

• PostgreSQL COPY reference — official syntax and error behavior
• MySQL LOAD DATA reference — strict mode and handling details

Format standards

• RFC 4180 — the CSV quoting and delimiter specification
• ISO 8601 at ISO.org — the date format databases expect

Regulation

• GDPR Article 5 principles at gdpr.eu — data minimization for personal-data files

Related guides

• The complete CSV import errors guide — the full pillar this post belongs to
• CRM import error codes decoded — the CRM-side error dictionary

FAQ