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:

{
  "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

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

{
  "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

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

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

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:

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:

{
  "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 — convert between JSON and CSV before or after cleaning.
• XML to CSV & Excel Converter — turn XML into tabular files that can enter this workflow.
• 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.