n8n CSV Import Workflow: Clean Messy Files Before They Land

Most CSV import tutorials assume the file is clean. Real files never are. Headers read First Name in one export and first_name in the next, half the phone numbers have spaces, a few rows are blank, and one cell holds the word "N/A" where a number should be. An n8n CSV import workflow that only appends rows to a sheet will happily load every one of those problems straight into the system reports read from.

The top-ranking guides lean on a paid uploader or show a happy-path append into Google Sheets. The cleanup, the part that actually earns the automation, gets a sentence. This walkthrough builds the cleanup as the spine: normalize, coerce, score, quarantine, then load.

What you can automate in a CSV pipeline

The same import-and-clean shape handles a surprising range of jobs:

• Customer lists from a CRM export, deduped and normalized before re-import
• Sales leads from a conference scanner app dropped into a sheet
• Product catalogs synced from a supplier's weekly CSV
• Survey exports that need type coercion before analysis
• Bank or expense CSVs prepped for a reconciliation flow
• Any "someone emails me a spreadsheet" recurring chore

If a human currently opens the file, fixes the obvious junk, and pastes it somewhere, that's the workflow.

The CSV Import Pipeline

Trigger (file/email/manual) → Extract from File → Normalize (Code) → Score → Route → Load
                                                                        │
                                                                        └─ low score → quarantine sheet

The branch is the whole idea. You don't want a single malformed row to fail the import. You want the 4,999 good rows in and the one bad row flagged with a reason.

1. Read the file

Use the Extract from File node (it replaced the older Spreadsheet File node, so copying configs from a 2023 tutorial will reference a node that's moved). Point it at the binary CSV input. It returns one item per row, headers as keys. If your CSV uses semicolons or a non-UTF-8 encoding, set the delimiter and encoding here; that's where silent garbling starts.

2. Normalize headers and values

A single Code node does the heavy lifting. Standardize keys, trim whitespace, lowercase emails, drop empty rows:

const clean = items
  .map(({ json }) => {
    const out = {};
    for (const [k, v] of Object.entries(json)) {
      const key = k.trim().toLowerCase().replace(/\s+/g, '_');
      out[key] = typeof v === 'string' ? v.trim() : v;
    }
    if (out.email) out.email = String(out.email).toLowerCase();
    return out;
  })
  .filter(r => Object.values(r).some(v => v !== '' && v != null));
return clean.map(json => ({ json }));

First Name becomes first_name, blank rows vanish, emails are consistent. Type coercion comes next: a number column that arrives as "1,200" or "N/A" needs a deliberate parse, not an implicit cast that turns it into NaN three nodes downstream.

3. Score each row

Give every row a quality score so the routing decision is data, not a guess:

const score = (r) => {
  let s = 100;
  if (!r.email || !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(r.email)) s -= 40;
  if (!r.first_name) s -= 20;
  if (r.amount != null && Number.isNaN(Number(String(r.amount).replace(/,/g,'')))) s -= 30;
  return s;
};

A row scoring 100 is clean. A row at 60 is missing a name but otherwise usable. A row at 30 has a broken email and a non-numeric amount, so quarantine it.

4. Route by threshold

An IF node splits on score >= 70. Pass rows go to the destination. Fail rows go to a quarantine sheet with _score and a reason column, so whoever owns the data can fix the source. The opinionated take here: resist the urge to "fix" bad rows automatically. Guessing a missing email or inventing a default amount hides the data problem instead of surfacing it. Quarantine and alert beats silent repair every time.

5. Load

Clean rows append or upsert to the target: Google Sheets, Postgres, Airtable. If you're loading to a database, upsert on a unique key so a re-run of the same file doesn't double the rows. That dedupe discipline is the same one behind the webhook to database workflow.

Implementation patterns

Pattern 1: Email-attachment import. A Gmail trigger catches the weekly supplier CSV, Extract from File parses it, the clean rows load, and a confirmation email reports the row count and quarantine count.

Pattern 2: Watched-folder import. A Schedule trigger checks a Drive or local folder, processes any new CSV, and moves the file to a processed/ folder so it isn't re-imported.

Pattern 3: One file, many destinations. Parse once, then fan out: clean rows to Postgres for analytics, a flagged subset to Slack, and the quarantine to a review sheet. One read, three loads.

For the loading half against a spreadsheet specifically, the Google Sheets ETL workflow covers the extract-transform-load mechanics in depth.

n8n nodes you'll use most

Getting started

1. Pick a trigger: a manual run, a Gmail attachment, or a scheduled folder check.
2. Add an Extract from File node and set delimiter and encoding to match your source.
3. Add a Code node to normalize headers, trim values, and coerce types.
4. Add a scoring Code node and an IF node on score >= 70.
5. Send pass rows to the destination, fail rows to a quarantine sheet with reasons.
6. Add a confirmation step reporting loaded vs quarantined counts.
7. Run it on a deliberately messy test file and read the quarantine sheet.

Building normalization, scoring, and the quarantine branch by hand is real work. A template that already wires the validate-and-route logic skips the tedious part.

A CSV import that cleans before it loads is the cheapest data-quality insurance you'll buy. The file gets messier, not cleaner, the longer humans touch it. Automate the normalization once and the next thousand imports inherit it. Pair this with the data validation workflow for a hard preflight gate, then explore the rest of the data-quality templates when the imports start arriving on a schedule.