The Five CSV Files That Break Every Importer

Every team that builds a CSV importer eventually meets the same five files. They arrive from legacy systems, hand-edited spreadsheets, and exports of exports. Each one looks harmless in a file preview, each one passes the happy-path tests, and each one quietly corrupts data or blows up the import at row 4,000.

Here they are — and how to survive each one without writing yet another pile of one-off cleanup scripts.

1. The ambiguous date file

Someone uploads a spreadsheet where a column contains 01/02/03. Is that January 2nd, 2003? February 1st, 2003? February 3rd, 2001? The answer depends on which country the file came from and which tool exported it — and a wrong guess silently shifts every date in the dataset.

Excel makes it worse. Depending on how the file was saved, you may not even get a date string: Excel stores dates internally as serial numbers, so the value that reaches your importer might be 45123.

The way out is to stop guessing at import time and normalize everything to one canonical format. importOK's built-in date transformer converts Excel serial dates and Unix timestamps into YYYY-MM-DD, and the date validator rejects anything that can't be matched against the formats you explicitly accept:

1{
2  "label": "Start date",
3  "transformers": "trim|date",
4  "validators": "required|date"
5}

Whatever ambiguity remains becomes a visible validation error the user resolves in the spreadsheet editor — instead of an invisible bug in your database.

2. The mixed units file

NASA lost the Mars Climate Orbiter because one team worked in metric units and another in imperial. Your users do the same thing with less fanfare: a weight column with 2.5 meaning kilograms in one row and pounds in another, prices as $1,250.00 in one export and 1.250,00€ in the next.

Two tools help here. The built-in number transformer strips currency symbols and other non-numeric noise. For genuine unit conversion, a custom transformer makes the rule explicit and repeatable:

 1{
 2  "transformers": {
 3    "to_kg": function (record, field) {
 4      const value = record.get(field);
 5      if (/lbs?$/i.test(value)) {
 6        return (parseFloat(value) * 0.453592).toFixed(2);
 7      }
 8      return value;
 9    }
10  }
11}

Then pair it with a between validator so that a value that slipped through in the wrong unit — a 400kg parcel that was clearly 400 grams — gets flagged before it lands in your system.

3. The combined-column file

The export has a single Full Name column, but your schema wants first_name and last_name. Or the entire address arrived as 123 Main St, Springfield, IL in one cell. The reverse happens too: the file has Street, City and Zip while your API expects one address line.

Splitting a column by hand across ten thousand rows is exactly the kind of work that makes users abandon onboarding. importOK transformers can read and write multiple fields on a record, so one small function handles the whole file:

1{
2  "transformers": {
3    "split_into": (record, key, ...targetFields) => {
4      const parts = (record.get(key) ?? '').split(' ');
5      targetFields.forEach((field, index) => record.set(field, parts[index] ?? ''));
6      return record.get(key);
7    }
8  }
9}

And if you'd rather not write the function at all, AI mappings recognize that Full Name maps onto your two name fields, while AI transformations accept a plain instruction like "split full names" — no code required.

4. The invalid-identifiers file

IBANs, VAT numbers, credit cards, order references: identifiers are where regex-based validation gives false confidence. GB82 WEST 1234 5698 7654 32 has the right shape whether or not its checksum is valid — and an identifier with a single mistyped digit looks exactly like a real one until a payment bounces weeks later.

Checksums need algorithms, not patterns, and that's what custom validators are for:

 1{
 2  "validators": {
 3    "luhn": function (record, field) {
 4      const digits = (record.get(field) ?? '').replace(/\D/g, '');
 5      let sum = 0;
 6      let double = false;
 7      for (let i = digits.length - 1; i >= 0; i--) {
 8        let d = parseInt(digits[i], 10);
 9        if (double && (d *= 2) > 9) d -= 9;
10        sum += d;
11        double = !double;
12      }
13      return digits.length > 0 && sum % 10 === 0;
14    }
15  }
16}

Chain it with the built-ins — required|length:8,34|luhn — and add the unique validator to catch the other classic identifier disaster: the same record pasted twice.

5. The free-text minefield

Notes, descriptions, comment columns. They hide trailing whitespace that breaks equality checks, stray encoding artifacts from three format conversions, inconsistent casing, and occasionally something worse: cells that begin with =, + or @, which spreadsheet applications will happily execute as formulas when your customer later exports the data — the well-documented CSV injection problem.

Free text can't be validated into shape, but it can be systematically cleaned. Chains like trim|capitalize and targeted replace: rules handle the mechanical part, and custom transformers can neutralize formula prefixes in one place instead of in every consumer of the data. For the genuinely messy cases — free text that needs semantic cleanup rather than mechanical fixes — AI transformations take a plain-language instruction and apply it across the column.

Since importOK processes file contents entirely in the browser, all of this cleanup happens before the data ever leaves your user's device — nothing to log, nothing to retain, nothing to explain in a security review.

An importer is judged by its worst file

Nobody notices an importer that handles a clean file. Users judge the import experience — and by extension your product — the day they upload the file with the Excel serial dates, the merged name column, and the IBAN with a typo.

The five files above aren't edge cases; over a product's lifetime they're a certainty. The choice is whether each one becomes a support ticket and a custom script, or a mapping suggestion, a transformer chain, and a validation message your user can fix on the spot.

importOK gives you the toolkit for all five: transformers that auto-heal data, validators that catch what can't be healed, and AI-powered mapping and transformations for everything in between — running entirely in the browser, with unlimited imports on every plan.

Try the free tester package and see how your worst file fares.