# CSV & Excel Data Cleaner with Change Log (`patchwell/patchwell-clean-data`) Actor

Clean a CSV, Excel or JSON file, or any Apify dataset. Detects column types, standardizes dates, phones, names and money, merges duplicates, and logs each changed value. Save the recipe to clean next month's file the same way. $2 per 1,000 rows.

- **URL**: https://apify.com/patchwell/patchwell-clean-data.md
- **Developed by:** [Aidan McCollum](https://apify.com/patchwell) (community)
- **Security:** Limited permissions
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $2.00 / 1,000 input rows

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

Clean a messy **CSV, Excel or JSON file**, or the output of **any Apify Actor**. You get typed, deduplicated records and a **log of each value it changed**. The cleaner reads each column's values to work out what the column holds, fixes formats, and merges duplicate records. When it holds a row back, it tells you which row and why.

### What it fixes

| Column type | What happens | Example |
|---|---|---|
| Email | Trimmed, lowercased | `MARIA.KOWALSKI249@EXAMPLE.COM` → `maria.kowalski249@example.com` |
| Phone (any country) | Validated and formatted | `13743111047` → `(374) 311-1047` |
| Date | ISO `YYYY-MM-DD`, with day-first or month-first decided per column | `05/03/19` → `2019-05-03` |
| Money and numbers | Currency text to numbers | `1,419 USD` → `1419.00` |
| Names | Encoding repaired, `Last, First` reordered, case fixed | `O'Brien, Linda` → `Linda O'Brien` |
| US states | Two-letter codes | `Washington` → `WA` |
| Null markers | `n/a`, `none`, `-` and similar become empty | `n/a` → empty |

The cleaner repairs broken accents from wrong encodings (`Linda Martínez` → `Linda Martínez`) in every text column. It empties values that can't be valid, like the email `not-an-email` or the state `Narnia`, and lists each one in that record's `_flags`.

#### How it decides dates

`05/03/19` can mean May 3 or March 5. The cleaner counts the values in that column that parse only one way and picks the order they support. It saves the decision and the count in `columns`, for example: `"87 values only valid month-first vs 0 day-first"`.

### Duplicates merge into one record

Rows that describe the same record (same email by default, or the columns you choose) become one record, with gaps filled from each copy. When two copies disagree on a value, the cleaner keeps the first one and adds an entry to the review list with the rows and every value they held, so you can decide. Each clean record lists the original rows it came from in `_source_rows`.

### Every row is accounted for

The counts always add up: **input rows = blank rows + rows sent to review + clean records + merged duplicates**. If they don't, the run fails. Rows missing a required column go to a review list with the reason.

### Clean next month's file the same way

Each run saves a **recipe**: the column types, date order and phone settings it used. Paste it into **Recipe from an earlier run**, and the cleaner applies the same rules to the next file, even when that file's values are ambiguous on their own.

### How to use it

1. Give it a **file URL** (CSV, XLSX or JSON), or the **dataset ID** of another Actor's run to clean scraped data.
2. Optionally, choose the columns that identify a record, the required columns, and your phone region.
3. Click **Start**. Clean records appear in the Output tab. The key-value store holds `clean.csv`, the change log, the review list and the recipe.

### How much does data cleaning cost?

**$2.00 per 1,000 input rows** ($0.002 per row). Files it can't read cost nothing.

| Rows | Cost |
|---|---|
| 352 (the sample file) | $0.70 |
| 10,000 | $20.00 |
| 100,000 | $200.00 |

### Example

Input, using the sample prefilled in the form: 352 rows of customer records with mixed date formats, broken encodings, duplicate customers and currency strings.

```json
{ "url": "https://patchwell.io/samples/customers_messy.csv" }
```

The run took 2.2 seconds and returned **301 clean records**. It merged 45 duplicates, removed 6 blank rows and **fixed 1,435 values**, logging each one. A clean record:

```json
{
  "Customer Name": "Renée Lee",
  "E-mail": "rene.lee101@example.com",
  "Phone #": "(374) 311-1047",
  "State": "CO",
  "Signup Date": "2019-05-03",
  "Total Spend": 1418.84,
  "_source_rows": [2, 21],
  "_flags": []
}
```

Entries from the change log (`changes` in the key-value store):

```json
[
  { "row": 2, "column": "Phone #", "before": "13743111047", "after": "(374) 311-1047", "rule": "valid number, national format" },
  { "row": 2, "column": "Signup Date", "before": "05/03/19", "after": "2019-05-03", "rule": "ISO date YYYY-MM-DD (MDY)" },
  { "row": 4, "column": "Customer Name", "before": "O'Brien, Linda", "after": "Linda O'Brien", "rule": "trim, fix encoding, Last, First -> First Last, case" }
]
```

### What each run saves

| Where | Key | Contents |
|---|---|---|
| Dataset | | One clean record per row, with `_source_rows` and `_flags` |
| Key-value store | `clean.csv` | The clean records as a CSV file |
| | `changes` | Each changed value: row, column, before, after, rule |
| | `review` | Rows held back, with the reason |
| | `columns` | Detected type of each column and the evidence |
| | `clusters` | Values that look like variants of each other (likely typos) |
| | `schema` | A JSON Schema for the clean records |
| | `recipe` | Settings to repeat this exact clean |
| | `stats` | The row accounting |

### Use it from an AI agent

Add this Actor to Claude, Cursor or any MCP client through [Apify's MCP server](https://mcp.apify.com). Your agent can read the change log and check the fixes before it acts on the data.

### FAQ

#### What about scraped data with nested fields?

Nested objects become columns with dotted names (`address.city`, `address.zip`). Lists stay as JSON text, so you lose nothing.

#### Does it verify that emails or phone numbers exist?

No. It checks emails for format and validates phone numbers against their country's numbering rules. It doesn't contact anyone.

#### How large a file can it handle?

Files up to 100 MB, or 200,000 dataset items. For larger jobs, write to patchwell.support@gmail.com.

#### Is my data stored?

The Actor processes files in a temporary container and discards them. Patchwell keeps no copies. Results stay in your own Apify storage.

### Other Patchwell tools

- [AI Web Research with Cited Sources](https://apify.com/patchwell/patchwell-research): answers where each sentence cites its source page.
- [Audio & Video Transcriber](https://apify.com/patchwell/patchwell-transcribe): word-level timestamps, SRT and VTT for audio and video links.

Questions or a custom need: patchwell.support@gmail.com

# Actor input Schema

## `url` (type: `string`):

Public link to a CSV, XLSX or JSON file, up to 100 MB. Use this or a dataset ID.

## `datasetId` (type: `string`):

Pick the dataset of another run, such as a scraper's output, to clean it. Nested fields become columns like address.city.

## `dedupeOn` (type: `array`):

Rows that match on these columns become one record. Leave it empty to use the email column, or the whole row if there is none.

## `required` (type: `array`):

Rows missing any of these go to the review list instead of the clean output.

## `phoneRegion` (type: `string`):

Country for phone numbers written without a country code, as a two-letter code: US, GB, DE, IN...

## `phoneFormat` (type: `string`):

How cleaned phone numbers are written.

## `recipe` (type: `object`):

Each run saves a recipe in its key-value store. Paste it here to clean next month's file with the same column types and rules.

## Actor input object example

```json
{
  "url": "https://patchwell.io/samples/customers_messy.csv",
  "phoneRegion": "US",
  "phoneFormat": "national"
}
```

# Actor output Schema

## `records` (type: `string`):

No description

## `csv` (type: `string`):

No description

## `changes` (type: `string`):

No description

## `review` (type: `string`):

No description

## `recipe` (type: `string`):

No description

# 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 = {
    "url": "https://patchwell.io/samples/customers_messy.csv"
};

// Run the Actor and wait for it to finish
const run = await client.actor("patchwell/patchwell-clean-data").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 = { "url": "https://patchwell.io/samples/customers_messy.csv" }

# Run the Actor and wait for it to finish
run = client.actor("patchwell/patchwell-clean-data").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 '{
  "url": "https://patchwell.io/samples/customers_messy.csv"
}' |
apify call patchwell/patchwell-clean-data --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,patchwell/patchwell-clean-data"
        }
    }
}
```

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/pW6U2dZMegTy6ZbA7/builds/azjpjd7YG1ZiWoPUO/openapi.json