---
title: The Five CSV Files That Break Every Importer
description: Ambiguous dates, mixed units, combined columns, invalid identifiers and messy free text — and how to survive each one
date: 2026-08-12
icon: document
---

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](/docs/transformers) converts Excel serial dates and Unix timestamps into `YYYY-MM-DD`, and the [`date` validator](/docs/validators) rejects anything that can't be matched against the formats you explicitly accept:

```json

```

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](https://llis.nasa.gov/llis_lib/pdf/1009464main1_0641-mr.pdf) 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](/docs/transformers#custom-transformers) makes the rule explicit and repeatable:

```javascript

      return value;
    }
  }
}
```

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](/docs/transformers#merging-and-splitting-fields), so one small function handles the whole file:

```javascript

  }
}
```

And if you'd rather not write the function at all, [AI mappings](/docs/mapping-strategy) 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](/docs/validators#custom-validators) are for:

```javascript

      return digits.length > 0 && sum % 10 === 0;
    }
  }
}
```

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](https://owasp.org/www-community/attacks/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](/docs/transformers) that auto-heal data, [validators](/docs/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](/docs/get-started) and see how your worst file fares.