# Spreadsheet Doctor: Clean, Dedupe, Join & Reconcile CSV/Excel (`errandkit/spreadsheet-doctor`) Actor

Clean messy CSV/XLSX files (emails, E.164 phones, ISO dates, numbers, ZIPs), remove duplicates, join several files on a key, and reconcile two files cell by cell. Strict JSON schemas, fail-loud error codes, built for AI agents.

- **URL**: https://apify.com/errandkit/spreadsheet-doctor.md
- **Developed by:** [Killis Haraway](https://apify.com/errandkit) (community)
- **Categories:** Developer tools, Automation, AI
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $2.00 / 1,000 100 input rows processeds

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

## Spreadsheet Doctor: clean, dedupe, join and reconcile CSV and Excel files

Spreadsheet Doctor fixes the routine problems in CSV and XLSX exports. It standardizes emails, phone numbers, dates, numbers and ZIP codes, removes duplicate records, joins several files on a key, and compares two files cell by cell.

It was written to be called by AI agents as well as people. The input schema is strict, every run returns the same JSON envelope, and when something goes wrong the run **fails with a machine-readable error code** instead of returning an empty dataset.

### What it does

| Operation | Use it to | Main output |
|---|---|---|
| `profile` | See what's in a file before changing it: column types, empty cells, unique counts, format problems, likely key columns | One dataset item per column, plus a report |
| `clean` | Standardize formats. Headers become snake_case, `N/A`/`null`/`-` become empty, emails are lowercased, phones become E.164 (`+19015550142`), dates become ISO 8601 (`2024-03-04`), `$1,200.50` becomes `1200.5`, ZIPs get their leading zeros back | Cleaned table |
| `dedupe` | Remove duplicate records by key columns (or whole row). Matching ignores case, spaces and punctuation by default. Keep the first, last, or a merged record | Deduplicated table, plus duplicate groups in the report |
| `join` | Join 2 to 10 files left to right (`inner`, `left`, `outer`). Key names can differ per file | Joined table, plus matched and unmatched counts with example keys |
| `reconcile` | Compare two files (for example CRM vs billing) by key | `differences`, `only_in_left` and `only_in_right` tables |

### Input

Give files as uploads or public URLs (`fileUrls`), or inline as text (`inlineFiles`), which is usually easiest for agents and the API.

#### Example: dedupe a contact list by email

```json
{
  "operation": "dedupe",
  "inlineFiles": [{
    "name": "contacts.csv",
    "text": "Name,E-mail,Phone,Signup\n ALICE SMITH ,Alice@Example.com,(901) 555-0142,03/04/2024\nalice smith,alice@example.com ,901.555.0142,2024-03-04\nBob Jones,bob@x.com,,N/A\n"
  }],
  "keys": ["email"]
}
```

#### Example: join a CRM export with a billing workbook whose key column has another name

```json
{
  "operation": "join",
  "fileUrls": ["https://example.com/crm.csv", "https://example.com/billing.xlsx"],
  "keys": ["email"],
  "keysPerFile": {"billing.xlsx": ["Customer Email"]},
  "how": "left"
}
```

#### Example: reconcile two files and ignore cent-level rounding

```json
{
  "operation": "reconcile",
  "inlineFiles": [
    {"name": "crm.csv", "text": "id,email,amount\n1,a@x.com,10.00\n2,b@x.com,20.00\n"},
    {"name": "billing.csv", "text": "id,email,amount\n1,a@x.com,10.004\n3,c@x.com,5.00\n"}
  ],
  "keys": ["id"],
  "numericTolerance": 0.01
}
```

#### Input fields

| Field | Type | Notes |
|---|---|---|
| `operation` | string, **required** | `profile`, `clean`, `dedupe`, `join` or `reconcile` |
| `fileUrls` | array | Uploads or public `http(s)` URLs. Order matters for `join` and `reconcile` |
| `inlineFiles` | array | `{"name": "a.csv", "text": "..."}` for CSV/TSV text, or `{"name": "b.xlsx", "content_base64": "..."}` for Excel |
| `keys` | array of strings | Key columns. Original header names and snake_case names both work |
| `keysPerFile` | object | Only when key names differ between files: `{"billing.xlsx": ["Customer Email"]}` |
| `how` | string | Join type: `inner`, `left` (default) or `outer` |
| `match` | string | `normalized` (default; ignores case, spaces and punctuation) or `exact` |
| `keep` | string | Dedupe: `merge` (default; fills blanks from duplicates), `first` or `last` |
| `compareColumns` | array | Reconcile: columns to compare. Default is every shared non-key column |
| `numericTolerance` | number | Reconcile: numbers within this difference count as equal |
| `cleanOptions` | object | For example `{"date_order": "DMY", "default_phone_region": "GB", "column_types": {"Mobile": "phone"}}` |
| `outputFormat` | string | Files saved to the key-value store: `csv` (default), `xlsx` or `both` |
| `maxDatasetRows` | integer | Rows also pushed to the dataset (default 10,000). Full tables are always saved as files |

### Output

Each run writes three things:

1. **Dataset:** one item per output row. The `_table` field says which table the row belongs to (for example `joined`, `differences`, `only_in_left`). For `profile`, one item per column.
2. **Key-value store, `OUTPUT`:** the full JSON result with the report, warnings and any error.
3. **Key-value store, files:** each output table as CSV and/or XLSX (for example `contacts.csv`, `joined.xlsx`).

Dataset items from the dedupe example above:

```json
[
  {"_table": "contacts.csv", "name": "Alice Smith", "email": "alice@example.com", "phone": "+19015550142", "signup": "2024-03-04"},
  {"_table": "contacts.csv", "name": "Bob Jones", "email": "bob@x.com", "phone": null, "signup": null}
]
```

Shape of `OUTPUT` (shortened):

```json
{
  "schema_version": "1.0",
  "ok": true,
  "operation": "dedupe",
  "tables": [{"name": "contacts.csv", "columns": ["name", "email", "phone", "signup"], "row_count": 2}],
  "report": {"dedupe": [{"rows_in": 3, "rows_out": 2, "rows_removed": 1, "duplicate_groups": 1,
                         "examples": [{"key": ["alice@example.com"], "row_numbers": [1, 2]}]}]},
  "warnings": [],
  "error": null,
  "stats": {"input_rows": 3, "output_rows": 2, "elapsed_ms": 2}
}
```

#### When something goes wrong

The run status is **Failed**, the status message starts with the error code, and `OUTPUT.error` has the details. For example, a join where no keys match:

```json
{"ok": false, "error": {"code": "NO_MATCHES", "message": "The join produced zero rows.",
  "details": {"steps": [{"left_rows": 3, "right_rows": 1, "left_rows_matched": 0,
    "unmatched_left_key_examples": [["alice@example.com"], ["bob@x.com"]],
    "unmatched_right_key_examples": [["x@y.com"]]}]}}}
```

| Code | Meaning | What to do |
|---|---|---|
| `INPUT_INVALID` | Missing or malformed field | Read `message`; fix the input |
| `KEY_NOT_FOUND` | A key column isn't in a file | `details` lists the available columns |
| `NO_MATCHES` | A join or reconcile matched nothing | Compare the example keys from both sides; try `keysPerFile` or `match: normalized` |
| `EMPTY_TABLE` | A file has no data rows | Check the file and the `sheet` name |
| `UNSUPPORTED_FORMAT`, `PARSE_ERROR` | Not a readable CSV/TSV/XLSX | Re-save as `.csv` or `.xlsx` |
| `FILE_TOO_LARGE`, `TOTAL_TOO_LARGE`, `TOO_MANY_FILES`, `TOO_MANY_ROWS`, `TOO_MANY_COLUMNS`, `OUTPUT_TOO_LARGE` | Over a limit (below) | Split the file or reduce the join |
| `BUDGET_TOO_LOW` | Your maximum cost per run can't cover the rows | Raise the run's max cost |

You are **not charged** the per-row or report fees for failed runs.

### Pricing (pay per event)

| Event | Price |
|---|---|
| 100 input rows processed (primary event) | $0.002 |
| Data-quality or reconciliation report (`profile` and `reconcile` only) | $0.02 |
| Actor start (set by Apify) | $0.00005 |

Examples: clean 1,000 rows = **$0.02**. Dedupe 50,000 rows = **$1.00**. Profile 2,000 rows = **$0.06**. Reconcile two 10,000-row files = **$0.42**.

Rows are counted once per run across all input files. Output rows pushed to the dataset are not charged separately. Charges happen only after your results are saved.

### Limits

| Limit | Value |
|---|---|
| Files per run | 10 |
| Size per file / total | 10 MB / 25 MB |
| Rows per file | 200,000 |
| Columns per file | 300 |
| Characters per cell | 32,767 (Excel's own limit) |
| Output rows (for example after a join) | 500,000 |
| Formats | `.csv`, `.tsv`, `.txt` (delimited), `.xlsx`. Not `.xls`, `.xlsm` or `.ods` |

Other things to know:

- Only one sheet is read per XLSX file (the first, unless you set `sheet` inside an inline file).
- European number formats such as `1.234,50` are flagged in profiles but not converted.
- Matching is exact after normalization. There is no fuzzy matching yet.
- Excel formulas are never evaluated; the cached value is used. CSV output escapes cells that start with `=`, `+`, `-` or `@` so they can't run as formulas when opened in Excel.

### Use it from an AI agent (MCP)

Apify exposes Store Actors to agents through its MCP server. Add this Actor to your client:

```bash
claude mcp add --transport http apify "https://mcp.apify.com/?actors=errandkit/spreadsheet-doctor" \
  --header "Authorization: Bearer <APIFY_TOKEN>"
```

Tips for agents:

- Start with `profile` to learn column names and types, then `clean`, `dedupe`, `join` or `reconcile`.
- Send small files inline with `inlineFiles`. Use `fileUrls` for anything over a few MB.
- Read the dataset for rows and `OUTPUT` for the report. If the run failed, read `OUTPUT.error.code`.

### Use it from the API

```bash
curl -X POST "https://api.apify.com/v2/acts/errandkit~spreadsheet-doctor/run-sync-get-dataset-items?token=<APIFY_TOKEN>" \
  -H "Content-Type: application/json" \
  -d '{"operation":"clean","inlineFiles":[{"name":"a.csv","text":"Email,Phone\nA@X.COM,(901) 555-0142\n"}]}'
```

### Privacy

Files are processed in memory during the run. Results are stored only in your own Apify run storage, under your account's data retention settings. The Actor does not send your data anywhere else. URL inputs must be public `http(s)` addresses; private and internal network addresses are refused.

### Support

Open an issue on the Actor's **Issues** tab with the run ID. We usually reply within 1–2 business days.

# Actor input Schema

## `operation` (type: `string`):

profile = inspect only; clean = fix formats; dedupe = remove duplicates; join = merge 2-10 files on a key; reconcile = compare exactly 2 files cell by cell.

## `fileUrls` (type: `array`):

Upload CSV/TSV/XLSX files or paste public http(s) URLs. Order matters for join (left to right) and reconcile (left, right). Max 10 files, 10 MB each.

## `inlineFiles` (type: `array`):

Alternative to uploads: \[{"name": "a.csv", "text": "email,name\nx@y.com,X"}] or {"name": "b.xlsx", "content_base64": "..."}. Inline files come after uploaded files.

## `keys` (type: `array`):

Columns that identify a record. dedupe: duplicate keys (empty = whole row). join: 'on' columns present in every file. reconcile: key columns of the left file. Original or snake_case names both work.

## `keysPerFile` (type: `object`):

Only when key names differ between files, e.g. {"billing.xlsx": \["Customer Email"]}. For reconcile, the entry for the second file is used as its keys.

## `how` (type: `string`):

inner = only matches; left = all rows of the first file; outer = all rows of all files.

## `match` (type: `string`):

normalized ignores case, spaces and punctuation in keys; exact compares as-is.

## `keep` (type: `string`):

merge = keep the first row and fill its empty cells from later duplicates.

## `compareColumns` (type: `array`):

Default: every non-key column present in both files.

## `numericTolerance` (type: `number`):

Treat numbers within this absolute difference as equal.

## `cleanOptions` (type: `object`):

Optional overrides, e.g. {"date_order": "DMY", "default_phone_region": "GB", "column_types": {"Mobile": "phone"}}. See README for all fields.

## `outputFormat` (type: `string`):

Files saved to the key-value store for each output table.

## `maxDatasetRows` (type: `integer`):

Output rows are also pushed to the dataset (handy for agents). Full tables are always in the key-value store.

## Actor input object example

```json
{
  "operation": "clean",
  "inlineFiles": [
    {
      "name": "contacts.csv",
      "text": "Name,E-mail,Phone\n ALICE SMITH ,Alice@Example.com,(901) 555-0142\nalice smith,alice@example.com ,901.555.0142\n"
    }
  ],
  "how": "left",
  "match": "normalized",
  "keep": "merge",
  "numericTolerance": 0,
  "outputFormat": "csv",
  "maxDatasetRows": 10000
}
```

# Actor output Schema

## `rows` (type: `string`):

No description

## `result` (type: `string`):

No description

## `files` (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 = {
    "operation": "clean",
    "inlineFiles": [
        {
            "name": "contacts.csv",
            "text": "Name,E-mail,Phone\n ALICE SMITH ,Alice@Example.com,(901) 555-0142\nalice smith,alice@example.com ,901.555.0142\n"
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("errandkit/spreadsheet-doctor").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 = {
    "operation": "clean",
    "inlineFiles": [{
            "name": "contacts.csv",
            "text": """Name,E-mail,Phone
 ALICE SMITH ,Alice@Example.com,(901) 555-0142
alice smith,alice@example.com ,901.555.0142
""",
        }],
}

# Run the Actor and wait for it to finish
run = client.actor("errandkit/spreadsheet-doctor").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 '{
  "operation": "clean",
  "inlineFiles": [
    {
      "name": "contacts.csv",
      "text": "Name,E-mail,Phone\\n ALICE SMITH ,Alice@Example.com,(901) 555-0142\\nalice smith,alice@example.com ,901.555.0142\\n"
    }
  ]
}' |
apify call errandkit/spreadsheet-doctor --silent --output-dataset

```

## MCP server setup

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

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/lhF0xQa7s9Cv7juTH/builds/om6lO3V9k3rDH0svP/openapi.json