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

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

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

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

[
  {"_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):

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

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

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

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.