# Csv Data Cleaning And Normalization (`first_watch/csv-data-cleaning-and-normalization`) Actor

Operations teams need to validate and normalize recurring CSV and Excel imports without manually correcting rows, silently discarding failures, or losing an audit trail of changes.

- **URL**: https://apify.com/first_watch/csv-data-cleaning-and-normalization.md
- **Developed by:** [Jordan Nabbe](https://apify.com/first_watch) (community)
- **Categories:** Automation, Business, Developer tools
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $2.00 / 1,000 result returneds

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## 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:

```json
{
  "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:

```json
{
  "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.

# Actor input Schema

## `source` (type: `object`):

Use inlineCsv with csv, inlineRecords with records, inlineJson with json, base64Document with format and base64, url with format and url, or apifyDataset with a public datasetId. CSV/XLSX files: at most 3 MB. Optional delimiter: comma, semicolon, tab or pipe; worksheet: name or index 1-10. Remote hosts: apify.com and api.apify.com only. See README for examples.

## `columns` (type: `array`):

1-100 objects with a safe lowercase snake_case target and type: string, email, phone, integer, number, boolean, date or datetime. Optional sources (up to 20), required, trim, case, emptyAsNull, default, allowedValues (up to 1000), dateFormats, outputDateFormat and numberFormat (auto, US or EU; number and integer columns only). Values such as 1,234 or 03/04/2026 are never guessed: numberFormat, a single day/month order in dateFormats, or unambiguous values in the same column must settle them. Runtime validation checks every nested option.

## `deduplicationKeys` (type: `array`):

Up to 20 canonical targets forming a compound key. The first valid occurrence is accepted; subsequent matches are rejected.

## `unknownColumns` (type: `string`):

Drop, reject, or preserve extra fields. Preserve converts extra field names to snake_case and nested values to stable JSON strings; name collisions reject that row.

## `maximumRows` (type: `integer`):

If the source has more rows than this, the run stops before any row is delivered, OUTPUT reports LIMIT_REACHED with a ROW_LIMIT error and the run fails. Hard maximum: 1000.

## `maskSensitiveInLogs` (type: `boolean`):

Compatibility option. Raw values are never logged, even when false. Full values are retained only in outputs.

## Actor input object example

```json
{
  "source": {
    "type": "inlineCsv",
    "delimiter": ",",
    "csv": "Customer Email,Full Name,Signup Date,Status\n ALICE@EXAMPLE.COM ,alice smith,31/01/2026, active \nbad-address,Bob Jones,2026-02-15,pending"
  },
  "columns": [
    {
      "target": "email",
      "type": "email",
      "sources": [
        "Customer Email",
        "Email"
      ],
      "required": true,
      "trim": true,
      "case": "lower"
    },
    {
      "target": "name",
      "type": "string",
      "sources": [
        "Full Name"
      ],
      "required": true,
      "trim": true,
      "case": "title"
    },
    {
      "target": "signup_date",
      "type": "date",
      "sources": [
        "Signup Date"
      ],
      "required": true,
      "dateFormats": [
        "DD/MM/YYYY",
        "YYYY-MM-DD"
      ],
      "outputDateFormat": "YYYY-MM-DD"
    },
    {
      "target": "status",
      "type": "string",
      "sources": [
        "Status"
      ],
      "required": true,
      "trim": true,
      "case": "lower",
      "allowedValues": [
        "active",
        "inactive"
      ]
    }
  ],
  "deduplicationKeys": [
    "email"
  ],
  "unknownColumns": "drop",
  "maximumRows": 1000,
  "maskSensitiveInLogs": true
}
```

# Actor output Schema

## `results` (type: `string`):

Every result row stored in the default dataset.

## `summary` (type: `string`):

Summary written to the OUTPUT record.

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "source": {
        "type": "inlineCsv",
        "delimiter": ",",
        "csv": "Customer Email,Full Name,Signup Date,Status\n ALICE@EXAMPLE.COM ,alice smith,31/01/2026, active \nbad-address,Bob Jones,2026-02-15,pending"
    },
    "columns": [
        {
            "target": "email",
            "type": "email",
            "sources": [
                "Customer Email",
                "Email"
            ],
            "required": true,
            "trim": true,
            "case": "lower"
        },
        {
            "target": "name",
            "type": "string",
            "sources": [
                "Full Name"
            ],
            "required": true,
            "trim": true,
            "case": "title"
        },
        {
            "target": "signup_date",
            "type": "date",
            "sources": [
                "Signup Date"
            ],
            "required": true,
            "dateFormats": [
                "DD/MM/YYYY",
                "YYYY-MM-DD"
            ],
            "outputDateFormat": "YYYY-MM-DD"
        },
        {
            "target": "status",
            "type": "string",
            "sources": [
                "Status"
            ],
            "required": true,
            "trim": true,
            "case": "lower",
            "allowedValues": [
                "active",
                "inactive"
            ]
        }
    ],
    "deduplicationKeys": [
        "email"
    ],
    "unknownColumns": "drop"
};

// Run the Actor and wait for it to finish
const run = await client.actor("first_watch/csv-data-cleaning-and-normalization").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "source": {
        "type": "inlineCsv",
        "delimiter": ",",
        "csv": """Customer Email,Full Name,Signup Date,Status
 ALICE@EXAMPLE.COM ,alice smith,31/01/2026, active 
bad-address,Bob Jones,2026-02-15,pending""",
    },
    "columns": [
        {
            "target": "email",
            "type": "email",
            "sources": [
                "Customer Email",
                "Email",
            ],
            "required": True,
            "trim": True,
            "case": "lower",
        },
        {
            "target": "name",
            "type": "string",
            "sources": ["Full Name"],
            "required": True,
            "trim": True,
            "case": "title",
        },
        {
            "target": "signup_date",
            "type": "date",
            "sources": ["Signup Date"],
            "required": True,
            "dateFormats": [
                "DD/MM/YYYY",
                "YYYY-MM-DD",
            ],
            "outputDateFormat": "YYYY-MM-DD",
        },
        {
            "target": "status",
            "type": "string",
            "sources": ["Status"],
            "required": True,
            "trim": True,
            "case": "lower",
            "allowedValues": [
                "active",
                "inactive",
            ],
        },
    ],
    "deduplicationKeys": ["email"],
    "unknownColumns": "drop",
}

# Run the Actor and wait for it to finish
run = client.actor("first_watch/csv-data-cleaning-and-normalization").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "source": {
    "type": "inlineCsv",
    "delimiter": ",",
    "csv": "Customer Email,Full Name,Signup Date,Status\\n ALICE@EXAMPLE.COM ,alice smith,31/01/2026, active \\nbad-address,Bob Jones,2026-02-15,pending"
  },
  "columns": [
    {
      "target": "email",
      "type": "email",
      "sources": [
        "Customer Email",
        "Email"
      ],
      "required": true,
      "trim": true,
      "case": "lower"
    },
    {
      "target": "name",
      "type": "string",
      "sources": [
        "Full Name"
      ],
      "required": true,
      "trim": true,
      "case": "title"
    },
    {
      "target": "signup_date",
      "type": "date",
      "sources": [
        "Signup Date"
      ],
      "required": true,
      "dateFormats": [
        "DD/MM/YYYY",
        "YYYY-MM-DD"
      ],
      "outputDateFormat": "YYYY-MM-DD"
    },
    {
      "target": "status",
      "type": "string",
      "sources": [
        "Status"
      ],
      "required": true,
      "trim": true,
      "case": "lower",
      "allowedValues": [
        "active",
        "inactive"
      ]
    }
  ],
  "deduplicationKeys": [
    "email"
  ],
  "unknownColumns": "drop"
}' |
apify call first_watch/csv-data-cleaning-and-normalization --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,first_watch/csv-data-cleaning-and-normalization"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/gvxLlAzn9PAnXddZc/builds/zm4fwSG79XP4lWpu3/openapi.json