# CSV & Excel Data Quality Cleaner (`automation-lab/csv-excel-data-quality-cleaner`) Actor

Clean CSV and Excel files under explicit rules, normalize columns and values, flag or remove duplicates, and export cleaned rows plus a structured validation report.

- **URL**: https://apify.com/automation-lab/csv-excel-data-quality-cleaner.md
- **Developed by:** [Stas Persiianenko](https://apify.com/automation-lab) (community)
- **Categories:** Developer tools
- **Stats:** 3 total users, 2 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

Pay per event

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.
Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have.

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

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
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.
Actors are written with capital "A".

## 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.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

## CSV & Excel Data Quality Cleaner

Turn messy spreadsheet exports into consistent, typed rows before they reach dashboards, warehouses, or client reports. This **CSV data cleaner** accepts inline CSV or a public CSV/XLSX file URL, normalizes headers and values, applies explicit validation rules, finds duplicates, and returns both cleaned files and an auditable quality report.

The Actor does not guess business rules or send data to an AI model. You decide which columns are required, typed, range-limited, pattern-matched, or deduplicated.

### What can this CSV data cleaner do?

- Read inline CSV or download one public HTTP(S) CSV/XLSX file.
- Select a named Excel worksheet or use the first worksheet.
- Normalize headers to unique lowercase `snake_case` names.
- Trim text and represent empty cells consistently as `null`.
- Convert configured columns to strings, numbers, integers, booleans, or ISO dates.
- Validate required values, numeric minimums/maximums, regular expressions, and allowed values.
- Detect duplicates from one column or a compound key.
- Flag, keep, or remove later duplicate rows.
- Push cleaned typed rows to the default Apify dataset.
- Export `CLEANED.csv`, `CLEANED.xlsx`, and a JSON `REPORT` from the run storage.

### Who is it for?

**Analytics engineers** can standardize recurring exports before loading them into a warehouse.

**Operations teams** can detect repeated customer, invoice, order, or inventory rows before reporting.

**Data analysts** can convert dates and numeric text once instead of repairing the same spreadsheet in every workbook.

**Automation builders** can schedule the Actor and connect the default dataset or cleaned file to Make, Zapier, n8n, webhooks, or the Apify API.

### Why use explicit data quality rules?

A generic cleaner may silently change values. This Actor keeps the contract visible in its input:

1. Choose the source.
2. Name the columns to convert or validate.
3. Choose the duplicate key and action.
4. Inspect row-level findings and run totals.

Rows retain their source columns. Five metadata fields explain quality status without hiding the original value:

| Field | Type | Meaning |
| --- | --- | --- |
| `_sourceRow` | integer | Original spreadsheet row number, including the header row |
| `_isValid` | boolean | `true` when every configured rule passed |
| `_isDuplicate` | boolean | `true` when the duplicate key appeared earlier |
| `_issueCount` | integer | Number of validation findings |
| `_issues` | array | Column, code, message, and offending value for each finding |

### Getting started

1. Open the Actor input.
2. Paste CSV into **Inline CSV**, or enter a public URL in **CSV or XLSX file URL**.
3. Keep format detection on `auto`, unless the URL has an ambiguous extension or content type.
4. Add `columnRules` for columns that need conversion or validation.
5. Add `deduplicateBy` columns and select `flag`, `remove`, or `keep`.
6. Set `maxRows` to a safe bound for the job.
7. Run the Actor.
8. Open **Cleaned rows** for dataset output, or download the cleaned CSV/XLSX and report from run storage.

Provide exactly one of `csvText` and `fileUrl`.

### Input parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `csvText` | string | — | Inline CSV including the header row |
| `fileUrl` | string | — | Public HTTP(S) URL to one CSV or XLSX file, up to 25 MB |
| `format` | string | `auto` | `auto`, `csv`, or `xlsx` |
| `sheetName` | string | first sheet | Worksheet to read from an XLSX workbook |
| `normalizeColumnNames` | boolean | `true` | Convert headers to unique lowercase snake\_case names |
| `trimWhitespace` | boolean | `true` | Trim leading and trailing text whitespace |
| `emptyAsNull` | boolean | `true` | Convert empty cells to `null` |
| `columnRules` | array | `[]` | Conversion and validation rules |
| `deduplicateBy` | string\[] | `[]` | Columns forming the duplicate key |
| `duplicateAction` | string | `flag` | `flag`, `remove`, or `keep` |
| `caseInsensitiveDuplicates` | boolean | `true` | Ignore text case in duplicate keys |
| `maxRows` | integer | `10000` | Read between 1 and 100,000 data rows |

A column rule supports:

```json
{
  "column": "amount",
  "outputName": "net_amount",
  "type": "number",
  "required": true,
  "min": 0,
  "max": 1000000,
  "pattern": "optional-regex-for-text",
  "allowedValues": ["optional", "exact", "values"]
}
```

Rules match normalized headers when `normalizeColumnNames` is enabled. For example, `Order Date` becomes `order_date`.

### Example: clean and validate customer CSV data

```json
{
  "csvText": "Customer ID,Full Name,Email,Amount,Order Date\nC-001,Alice Smith,alice@example.com,1250.50,2026-08-01\nC-001,Alice Smith,alice@example.com,1250.50,2026-08-01\nC-002,Bob Jones,not-an-email,-5,invalid-date",
  "columnRules": [
    {
      "column": "email",
      "type": "string",
      "required": true,
      "pattern": "^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$"
    },
    {
      "column": "amount",
      "type": "number",
      "required": true,
      "min": 0
    },
    {
      "column": "order_date",
      "type": "date",
      "required": true
    }
  ],
  "deduplicateBy": ["customer_id", "order_date"],
  "duplicateAction": "flag",
  "maxRows": 100
}
```

### Output example

The default dataset contains the cleaned source columns plus quality metadata:

```json
{
  "customer_id": "C-001",
  "full_name": "Alice Smith",
  "email": "alice@example.com",
  "amount": 1250.5,
  "order_date": "2026-08-01T00:00:00.000Z",
  "_sourceRow": 2,
  "_isValid": true,
  "_isDuplicate": false,
  "_issueCount": 0,
  "_issues": []
}
```

A failed rule preserves the source value and adds a finding instead of silently discarding the row. With `duplicateAction: "remove"`, only later copies of a duplicate key are omitted.

### Validation report and downloadable files

Every successful run writes three records to the default key-value store:

- `REPORT` — source, format, sheet, row totals, duplicate totals, issue counts, output columns, and generation time.
- `CLEANED.csv` — clean source columns without the `_` quality metadata fields.
- `CLEANED.xlsx` — the same clean source columns in a worksheet named `Cleaned Data`.

The dataset is best when downstream automation needs row-level findings. The files are convenient for spreadsheet users and file-based pipeline stages.

### How much does it cost to clean CSV and Excel rows?

This Actor uses pay-per-event pricing: one start event per run and one `item` event for each cleaned row written to the default dataset. Validation findings, the JSON report, and the two downloadable files have no separate charge event.

The exact tier active for your account appears in the Apify Console before you start a run. Cost scales with rows actually delivered. For example, at a BRONZE item price of `$0.0008` and a `$0.001` start event:

- 10 cleaned rows cost about **$0.009**.
- 100 cleaned rows cost about **$0.081**.
- 1,000 cleaned rows cost about **$0.801**.

These examples use the current BRONZE source configuration and will be updated if live pricing changes. Removed duplicate rows are not emitted and do not incur the per-item event.

### Scheduling a recurring spreadsheet quality check

Use an Apify schedule when a report is published to a stable HTTPS URL:

1. Set `fileUrl` to the current export.
2. Store the conversion, validation, and duplicate rules in an Actor task.
3. Schedule the task after the source export is produced.
4. Send a webhook when the run succeeds or fails.
5. Read `REPORT.invalidRows`, `REPORT.duplicateRows`, and `REPORT.issueCounts` in the next workflow step.

The Actor processes each run independently. It does not compare today’s file with an earlier run or send alerts by itself.

### API: run with cURL

```bash
curl -X POST \
  "https://api.apify.com/v2/acts/automation-lab~csv-excel-data-quality-cleaner/runs?token=$APIFY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "fileUrl": "https://datahub.io/core/country-list/_r/-/data.csv",
    "format": "csv",
    "columnRules": [
      {"column": "name", "type": "string", "required": true},
      {"column": "code", "type": "string", "required": true, "pattern": "^[A-Z]{2}$"}
    ],
    "deduplicateBy": ["code"],
    "duplicateAction": "remove",
    "maxRows": 250
  }'
```

Keep API tokens in secrets or environment variables, not source code.

### API: JavaScript client

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

const client = new ApifyClient({ token: process.env.APIFY_TOKEN });
const run = await client.actor('automation-lab/csv-excel-data-quality-cleaner').call({
  csvText: 'invoice_id,total,date\nINV-1,99.50,2026-08-01',
  columnRules: [
    { column: 'total', type: 'number', min: 0 },
    { column: 'date', type: 'date', required: true }
  ],
  deduplicateBy: ['invoice_id'],
  duplicateAction: 'remove'
});

const { items } = await client.dataset(run.defaultDatasetId).listItems();
console.log(items);
```

### API: Python client

```python
import os
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor("automation-lab/csv-excel-data-quality-cleaner").call(run_input={
    "fileUrl": "https://datahub.io/core/country-list/_r/-/data.csv",
    "format": "csv",
    "deduplicateBy": ["code"],
    "duplicateAction": "remove",
    "maxRows": 250,
})

for row in client.dataset(run["defaultDatasetId"]).iterate_items():
    print(row)
```

### Use with Apify MCP

Add the Actor to Claude Code:

```bash
claude mcp add --transport http apify \
  "https://mcp.apify.com?tools=automation-lab/csv-excel-data-quality-cleaner"
```

#### Claude Desktop, Cursor, and VS Code setup

Claude Desktop, Cursor, and VS Code can each use this MCP configuration in their MCP settings:

```json
{
  "mcpServers": {
    "apify": {
      "url": "https://mcp.apify.com?tools=automation-lab/csv-excel-data-quality-cleaner"
    }
  }
}
```

Example prompts:

- “Run the CSV cleaner on this public file, convert `amount` to a number, require `invoice_id`, and remove duplicates by invoice ID.”
- “Clean this inline CSV, validate two-letter country codes, and summarize invalid and duplicate rows from the report.”
- “Schedule my saved spreadsheet-cleaning task every weekday after the source export.”

### Integrations

**Apify datasets:** query cleaned rows through the dataset API or export JSON, CSV, Excel, XML, and other supported formats.

**Make, Zapier, and n8n:** run a saved task, wait for completion, then route invalid rows or the cleaned file.

**Webhooks:** trigger a pipeline when a run succeeds, fails, or times out.

**Cloud storage:** use the API links for `CLEANED.csv` or `CLEANED.xlsx` as the source for a controlled storage copy step.

### Limits and failure behavior

- One file or inline CSV source is processed per run.
- Downloads are limited to 25 MB and 30 seconds.
- Up to 100,000 data rows can be read.
- Only `.xlsx` Excel workbooks are supported; legacy `.xls`, macros, formulas requiring recalculation, and password-protected files are not.
- Formula cells use the cached result stored in the workbook when available.
- CSV delimiter detection follows the parser’s CSV defaults; explicitly convert unusual fixed-width or non-CSV files first.
- Dates use JavaScript date parsing and are emitted in ISO 8601 UTC form. Ambiguous locale-specific dates should be normalized upstream.
- The Actor fails with a non-zero status for two sources, no source, inaccessible URLs, oversized files, missing worksheets, invalid regular expressions, or missing duplicate-key columns.
- An input with a header but no data rows succeeds with an empty dataset and zero-row report.

### Legality and responsible data use

Only process files you are authorized to access and transform. A public URL does not automatically grant rights to redistribute its contents. Avoid putting credentials or sensitive personal data in public URLs, Actor input examples, logs, or public datasets. Use Apify access controls and retention settings appropriate to your data classification.

The Actor performs deterministic transformation; it does not verify the truth, ownership, legality, or business meaning of source values.

### Troubleshooting and FAQ

#### Why did my column rule not run?

Rules match the post-normalization header. With normalization enabled, `Order Date` is `order_date`. Inspect `REPORT.columns` or a dataset row to confirm output names.

#### Why is a date still a string?

The Actor preserves an unparseable source value and adds a `type` finding. Supply an unambiguous ISO-style date such as `2026-08-01`, or normalize locale-specific dates before this step.

#### Why was the first duplicate kept?

Duplicate removal keeps the first occurrence and removes only later rows with the same configured key. Sort the source before cleaning if another record should win.

#### Can it merge multiple files or compare runs?

No. Each run processes exactly one source. Orchestrate multiple tasks and a downstream merge/comparison when that workflow is required.

#### Does it modify the original file?

No. It reads the supplied content and writes new run-scoped outputs.

#### Are validation findings charged separately?

No. The start and cleaned-row events are the only declared events. Reports and downloadable files have no separate event charge.

### Related automation-lab Actors

- [JSON CSV Converter](https://apify.com/automation-lab/json-csv-converter) — convert between JSON and CSV before or after cleaning.
- [XML to CSV & Excel Converter](https://apify.com/automation-lab/xml-to-csv-excel-converter) — turn XML into tabular files that can enter this workflow.
- [CSV Diff Tool](https://apify.com/automation-lab/csv-diff-tool) — compare two CSV versions after standardizing their columns and types.

Use this Actor for deterministic quality rules; use the related Actors when the primary job is format conversion or version comparison.

# Actor input Schema

## `csvText` (type: `string`):

CSV text including a header row. Provide this or File URL, but not both.

## `fileUrl` (type: `string`):

Public HTTP(S) URL of one CSV or Excel .xlsx file (maximum 25 MB). Provide this or Inline CSV, but not both.

## `format` (type: `string`):

Detect the format automatically or force CSV/XLSX parsing.

## `sheetName` (type: `string`):

Optional worksheet name. The first sheet is used when omitted.

## `normalizeColumnNames` (type: `boolean`):

Convert headers to lowercase snake\_case and make duplicate headers unique.

## `trimWhitespace` (type: `boolean`):

Remove leading and trailing whitespace from text values.

## `emptyAsNull` (type: `boolean`):

Represent empty cells consistently as null.

## `columnRules` (type: `array`):

Rules are matched after header normalization. Configure output names, types, required values, numeric ranges, patterns, or allowed values.

## `deduplicateBy` (type: `array`):

Columns whose combined values identify duplicate rows. Leave empty to disable duplicate detection.

## `duplicateAction` (type: `string`):

Flag duplicates in output, remove later duplicates, or keep them without removal (duplicate metadata is still included).

## `caseInsensitiveDuplicates` (type: `boolean`):

Treat text values that differ only by letter case as the same duplicate key.

## `maxRows` (type: `integer`):

Maximum number of data rows to read after the header.

## Actor input object example

```json
{
  "csvText": "Customer ID, Full Name ,Email,Amount,Order Date\nC-001, Alice Smith ,ALICE@ACME.COM,1250.50,2026-08-01\nC-001,Alice Smith,alice@acme.com,1250.50,2026-08-01",
  "format": "auto",
  "normalizeColumnNames": true,
  "trimWhitespace": true,
  "emptyAsNull": true,
  "columnRules": [
    {
      "column": "amount",
      "type": "number",
      "required": true,
      "min": 0
    },
    {
      "column": "order_date",
      "type": "date",
      "required": true
    },
    {
      "column": "email",
      "type": "string",
      "pattern": "^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$"
    }
  ],
  "deduplicateBy": [
    "customer_id",
    "order_date"
  ],
  "duplicateAction": "flag",
  "caseInsensitiveDuplicates": true,
  "maxRows": 20
}
```

# Actor output Schema

## `dataset` (type: `string`):

Default dataset containing cleaned source columns and row-level quality metadata.

## `report` (type: `string`):

Structured summary of processed rows, duplicates, and validation findings.

## `cleanedCsv` (type: `string`):

Download the cleaned rows as CSV.

## `cleanedExcel` (type: `string`):

Download the cleaned rows as XLSX.

# 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 = {
    "csvText": `Customer ID, Full Name ,Email,Amount,Order Date
C-001, Alice Smith ,ALICE@ACME.COM,1250.50,2026-08-01
C-001,Alice Smith,alice@acme.com,1250.50,2026-08-01`,
    "format": "auto",
    "normalizeColumnNames": true,
    "trimWhitespace": true,
    "emptyAsNull": true,
    "columnRules": [
        {
            "column": "amount",
            "type": "number",
            "required": true,
            "min": 0
        },
        {
            "column": "order_date",
            "type": "date",
            "required": true
        },
        {
            "column": "email",
            "type": "string",
            "pattern": "^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$"
        }
    ],
    "deduplicateBy": [
        "customer_id",
        "order_date"
    ],
    "duplicateAction": "flag",
    "caseInsensitiveDuplicates": true,
    "maxRows": 20
};

// Run the Actor and wait for it to finish
const run = await client.actor("automation-lab/csv-excel-data-quality-cleaner").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 = {
    "csvText": """Customer ID, Full Name ,Email,Amount,Order Date
C-001, Alice Smith ,ALICE@ACME.COM,1250.50,2026-08-01
C-001,Alice Smith,alice@acme.com,1250.50,2026-08-01""",
    "format": "auto",
    "normalizeColumnNames": True,
    "trimWhitespace": True,
    "emptyAsNull": True,
    "columnRules": [
        {
            "column": "amount",
            "type": "number",
            "required": True,
            "min": 0,
        },
        {
            "column": "order_date",
            "type": "date",
            "required": True,
        },
        {
            "column": "email",
            "type": "string",
            "pattern": "^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$",
        },
    ],
    "deduplicateBy": [
        "customer_id",
        "order_date",
    ],
    "duplicateAction": "flag",
    "caseInsensitiveDuplicates": True,
    "maxRows": 20,
}

# Run the Actor and wait for it to finish
run = client.actor("automation-lab/csv-excel-data-quality-cleaner").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 '{
  "csvText": "Customer ID, Full Name ,Email,Amount,Order Date\\nC-001, Alice Smith ,ALICE@ACME.COM,1250.50,2026-08-01\\nC-001,Alice Smith,alice@acme.com,1250.50,2026-08-01",
  "format": "auto",
  "normalizeColumnNames": true,
  "trimWhitespace": true,
  "emptyAsNull": true,
  "columnRules": [
    {
      "column": "amount",
      "type": "number",
      "required": true,
      "min": 0
    },
    {
      "column": "order_date",
      "type": "date",
      "required": true
    },
    {
      "column": "email",
      "type": "string",
      "pattern": "^[^@\\\\s]+@[^@\\\\s]+\\\\.[^@\\\\s]+$"
    }
  ],
  "deduplicateBy": [
    "customer_id",
    "order_date"
  ],
  "duplicateAction": "flag",
  "caseInsensitiveDuplicates": true,
  "maxRows": 20
}' |
apify call automation-lab/csv-excel-data-quality-cleaner --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,automation-lab/csv-excel-data-quality-cleaner"
        }
    }
}

```

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/eyMmLLttffd19Mc9H/builds/fQ01DnRvsFdTAOcnd/openapi.json