How to Validate and Clean CSV Data Before Import

Parse CSV files, validate the data, fix common issues, and load clean records into your database.

What This Integration Does

Most production data imports fail because a single row has a bad email, a duplicate primary key, or a date in the wrong format. Doing the validation by hand is slow and inconsistent; doing it after the import means rolling back. This workflow runs the checks upfront: it parses the file, validates every row against your rules, cleans the easy fixes, and routes only clean rows to the database. Bad rows land in a quarantine collection where someone can review them.

The workflow runs on whatever cadence makes sense for your source: hourly FTP polling, on-arrival via the Email trigger, or push via the Webhook trigger. Each file is processed as a single batch, with a summary report at the end so you know exactly what was imported, what was fixed automatically, and what needs human attention. If you mainly need to reshape rather than validate, see the related Spojit tutorial on building a CSV-to-MySQL ETL pipeline.

Prerequisites

A source for the file: FTP/SFTP, inbound email, or webhook.
A target database: MySQL or MongoDB, with the destination table or collection created and a unique index on the natural key.
A second collection or table for quarantined rows (e.g. import_quarantine) so bad rows have somewhere to live.
A clear list of validation rules: required fields, expected formats, and any business constraints (e.g. "country must be ISO-2").

Step 1: Trigger - Receive the File

Drop a Trigger node and pick the source. For FTP, schedule a poll then use the ftp list-directory and download-file tools. For email-attached CSVs use the Email trigger. For partner uploads use the Webhook trigger.

Step 2: Parse and Inspect

Add a Connector node for the csv connector and call info first to capture row count, column count, and detected encoding. Then call to-json to convert to an array of objects. Configure to-json:

Has header: true
Trim values: true
Encoding: explicit (UTF-8 or whatever the source uses)

Pair the parse with a Condition node that fails the run loudly if expected columns are missing - schema drift caught at minute one beats schema drift discovered three hours into a load.

Step 3: Dedupe and Pre-Clean the Batch

Before the per-row loop, drop a couple of cheap batch-level cleanups. Use the csv dedupe tool against the natural key column to remove duplicate rows. Use the text connector's trim and case tools on common offender columns (names, emails) via a Transform node. This means the per-row validator only sees genuinely problematic rows.

Step 4: Validate Per Row

Wrap the next steps in a Loop over the deduped array. Inside the loop, run validation checks using the validation connector:

email on the email field
phone on the phone field
is-empty on each required field
numeric or iso-date on typed columns

Accumulate validation results into a single errors array per row. If errors.length === 0, the row continues; otherwise it routes to quarantine.

Step 5: Clean Then Insert Valid Rows

On the valid branch, apply final touch-ups with a Transform node:

Lowercase email.
Strip non-digits from phone, then re-format to E.164.
Standardize country to ISO-2.

Then insert. For SQL targets, use the mysql connector with insert-rows in batches of 500. For document targets, use mongodb with insert-documents. For AI-assisted cleaning of edge cases (e.g. detecting near-duplicates, fixing misspelled city names), switch a Connector node to Agent mode inside the loop before the insert and prompt it with the row and your cleaning rules. The Spojit tutorial on cleaning database records with AI walks through that pattern in detail.

Step 6: Quarantine and Summary

On the invalid branch, insert each row plus its errors array into the quarantine table or collection so reviewers can see what failed and why. After the loop, send one summary message via slack send-message:

Import "customers-2025-05-28.csv": 450 imported, 12 failed validation, 3 duplicates removed.
Quarantine batch ID: 2025-05-28-customers (query the quarantine table to review).

Move the source file to an archive/ folder with ftp rename so it isn't reprocessed on the next poll.

Tips

Validate before cleaning, clean before inserting - in that order. Cleaning a row that's going to be rejected anyway wastes time, and inserting an uncleaned-but-valid row leaves the cleanup half-done.
Tag the batch - stamp every inserted and quarantined row with a batch_id derived from the filename. Makes it trivial to roll back a bad import with a single delete query.
Loud, structured errors - the quarantine row's errors field should look like [{ field: "email", code: "INVALID_FORMAT" }], not a free-text string. Reviewers (and dashboards) can filter on it.

Common Pitfalls

Headers change silently - a partner renaming "Email" to "EmailAddress" passes the parse but silently produces all-null rows downstream. Assert expected headers in Step 2.
Hidden whitespace - emails imported with trailing newlines or non-breaking spaces validate as malformed. trim is non-negotiable, and don't forget to strip the non-breaking space character (HTML entity  ) specifically.
Quarantine grows forever - decide a retention policy. A weekly pruning workflow deletes quarantine rows older than 30 days, or moves them to cold storage.
Re-import of fixed rows - if a reviewer corrects a quarantined row and resends the file, dedupe-on-natural-key plus a find-documents/insert-documents upsert pattern (or the mysql update-rows tool keyed on the natural key) handles it. Without that, you get duplicates the second time around.

Testing

Build a tiny test CSV with 5 rows: 3 valid, 1 with a bad email, 1 duplicate. Run the workflow manually. Confirm: 3 rows land in the destination (or 2 plus the dedupe), 1 row lands in quarantine with a clear error, the source file moved to archive/, and the Slack summary reads correctly. Then run it on a realistic file size before turning the trigger on.