Csv Data Cleaning And Normalization

Validate and clean recurring CSV, Excel and JSON imports before they reach your CRM, ERP, marketplace or analytics database. Built for operations and RevOps teams who import supplier or customer exports every week, this Actor maps inconsistent column names to a canonical schema, normalizes typed values, rejects bad rows with stable reason codes, removes duplicates and hands you an import-ready CSV plus a field-level audit trail. Every row keeps its source reference and source row number, so nothing disappears silently.

What it does

• Parses real CSV text and real .xlsx workbook bytes (ExcelJS), and accepts inline JSON records, JSON strings, base64 documents, approved HTTPS files and public Apify dataset IDs.
• Maps alternative headers (case-insensitive, whitespace and Unicode-normalized) onto lowercase snake_case target columns.
• Trims, changes case, applies defaults and converts integers, numbers, booleans, dates, datetimes, emails and phone numbers.
• Reads numbers in US (1,234.56) and European (1.234,56) notation. A value that means different things in each (1,234, 1.234) or a day/month date such as 03/04/2026 is never guessed. It is settled by a column option (numberFormat set to US or EU, or only one day/month order in dateFormats) or by other values in the same column that fit only one notation (for example 19,99 marks a column as European). Otherwise the row is rejected with AMBIGUOUS_VALUE. OUTPUT.settledNotation lists every column that its own data settled.
• Never rounds silently: a number with more significant digits than a JSON number stores exactly is rejected with PRECISION_LOSS, so long IDs and high-precision amounts should be imported as string. Dates never roll over (2026-02-30 is rejected).
• Checks required fields and allowed-value lists, and de-duplicates valid rows on one or more key fields.
• The default dataset holds only accepted, normalized rows. Rejected rows with reason codes, the change log, the summary and the CSV text are in the OUTPUT record; the CSV is also stored as IMPORT_READY.csv.

The Actor never verifies that a value is factually correct: an email is only checked for syntax, and a phone number only for digit count.

How to use it

The form is prefilled, so one click on Start gives a useful result. Or paste this input:

{
  "source": {
    "type": "inlineCsv",
    "csv": "Email,Name,Joined,Status\n ADA@EXAMPLE.COM ,ada lovelace,10/12/2025,ACTIVE\nbad-address,Grace Hopper,2025-11-02,pending"
  },
  "columns": [
    {"target":"email","type":"email","sources":["Email"],"required":true,"case":"lower"},
    {"target":"name","type":"string","sources":["Name"],"case":"title"},
    {"target":"joined","type":"date","sources":["Joined"],"dateFormats":["DD/MM/YYYY","YYYY-MM-DD"]},
    {"target":"status","type":"string","sources":["Status"],"case":"lower","allowedValues":["active","inactive"]}
  ],
  "deduplicationKeys": ["email"],
  "unknownColumns": "drop",
  "maximumRows": 1000
}

For a European amount column add, for example, {"target":"amount","type":"number","sources":["Amount"],"numberFormat":"EU"} to columns; 1.250,00 then becomes 1250.

Other sources: {"type":"inlineRecords","records":[...]}, {"type":"inlineJson","json":"[...]"}, {"type":"base64Document","format":"xlsx","base64":"...","worksheet":"Sheet1"} (or format: csv), {"type":"url","format":"csv","url":"https://api.apify.com/v2/key-value-stores/STORE/records/KEY"}, and {"type":"apifyDataset","datasetId":"<17-character public id>"}. Remote hosts are limited to apify.com and api.apify.com; no credentials are supported. Use the offline options for files hosted elsewhere.

Output example

Each dataset row:

{
  "outcome": "accepted",
  "sourceReference": "inlineCsv",
  "sourceRow": 2,
  "record": { "email": "alice@example.com", "name": "Alice Smith", "signup_date": "2026-01-31", "status": "active" },
  "changeCount": 5
}

OUTPUT contains outcome (status SUCCEEDED, PARTIAL, FAILED or LIMIT_REACHED, with item counts), summary (input, accepted, rejected and duplicate row counts, change count, measured acceptance rate), settledNotation, rows, rejectedRows (each with errors of code, field, message), changeLog, importReadyCsv and artifacts. Reason codes: REQUIRED, INVALID_TYPE, INVALID_FORMAT, VALUE_NOT_ALLOWED, DUPLICATE, UNKNOWN_COLUMN, ROW_PARSE_ERROR, AMBIGUOUS_COLUMN, AMBIGUOUS_VALUE, PRECISION_LOSS. If no row passes validation the run stores OUTPUT and then fails, so a failure is never shown as success. If the input is invalid or the document cannot be read, OUTPUT holds an error message and status FAILED, no row is delivered and the run fails. If the source has more rows than maximumRows, OUTPUT holds status LIMIT_REACHED with a ROW_LIMIT error, no row is delivered or charged, and the run fails so you can raise the limit or split the file.

Pricing

Pay per event. The main charge is per accepted row; everything else listed below is free.

Event	Price (USD)	Charged when
Actor start (apify-actor-start)	$0.005	The run starts.
Accepted row (apify-default-dataset-item)	$0.002	An accepted row is written to the default dataset.

Not charged:

• rejected rows with reason codes (OUTPUT)
• field-level change log (OUTPUT)
• import-ready CSV
• run summary (OUTPUT)

Examples (default 512 MB memory, one start event):

• 1,000 accepted rows: $0.005 start + 1 × $0.005 + 1000 × $0.002 = $2.01
• 100 rows, 10 rejected: $0.005 start + 1 × $0.005 + 90 × $0.002 = $0.19

Spending limit: when you set a maximum cost per run, the Actor stops before it would exceed it, keeps every accepted row it already delivered and reports limitReached in the OUTPUT record. It never delivers results beyond the limit.

The Store's Pricing tab shows the prices in force. If this section and the Pricing tab ever differ, the Pricing tab applies.

Use cases

• Preflight a supplier CSV against your ERP import specification.
• Normalize CRM migration exports and queue invalid emails for correction.
• Convert a chosen Excel worksheet into a canonical CSV.
• Read European and US amount columns into clean numbers without silent rounding.
• Reject statuses outside an approved vocabulary.
• Detect repeated contacts or transactions with compound keys.
• Keep a field-level history of automated changes for review.

Limits

At most 1,000 rows per run (maximumRows, default 1000), 100 columns, 100 keys per object and nesting depth 8. JSON input is limited to 5 MB and a decoded or downloaded document to 3 MB; each row to 64 KB. XLSX files are inspected before loading: at most 200 ZIP entries, 16 MB expanded, 10 worksheets, 100 columns and row numbers up to 1001; macros, encryption and external links are unsupported, and formula cells are rejected rather than evaluated. CSV must be UTF-8 with a header; delimiters are comma, semicolon, tab or pipe. Numbers are accepted with up to 15 significant digits, or as whole numbers up to 9,007,199,254,740,991. For CSV, sourceRow is the physical starting line; for XLSX the worksheet row; for JSON and dataset records the first record is row 2. Downloads use a 20-second timeout per attempt, up to three redirects (each checked) and two retries for network or 5xx errors. A 180-second watchdog ends the run.

FAQ

Does it work offline? Yes. Inline CSV, records, JSON and base64 documents make no network requests.

Are rejected rows lost? No. They are listed in OUTPUT.rejectedRows with their source row and reason codes, and are not in the dataset.

What happens if my file has too many rows? The run stops before delivering anything, reports LIMIT_REACHED with a ROW_LIMIT error in OUTPUT, and fails. Nothing is truncated silently.

How are 1,234 and 03/04/2026 handled? They are never guessed. Set numberFormat (US or EU), keep one day/month order in dateFormats, or include values in the same column that fit only one notation; otherwise the row is rejected with AMBIGUOUS_VALUE.

What does changedFields mean? The number of individual audit operations (for example trim and lowercase on one email count as two).

What happens to extra columns? drop removes them, reject reports them, preserve keeps them under snake_case names.

Are duplicates detected across runs? No, only within one run; the first valid occurrence wins.

Are values logged? Raw values are never logged. Full values live in your run's storage, so set retention accordingly. You need the right to process any personal data you upload.