# CSV & Excel Data Cleaner | Dedupe & Fix (`makentest/csv-excel-data-cleaner-dedupe-fix`) Actor

Clean CSV, Excel & JSON in bulk: fix encoding, dates, numbers & currency, flag bad emails, remove duplicates (fuzzy too). Full change log. No AI.

- **URL**: https://apify.com/makentest/csv-excel-data-cleaner-dedupe-fix.md
- **Developed by:** [Harsh Garg](https://apify.com/makentest) (community)
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

$1.00 / 1,000 cleaned records

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

## CSV/Excel Data Cleaner – clean, dedupe & standardize data with a full audit trail

Upload or link a messy **CSV, Excel (.xlsx) or JSON** file and get back clean, consistent data **plus a record of every change**. Rule-based and deterministic: **no AI, no LLM calls, no web scraping** – the same input and settings always give the same result.

### Who is it for?

Ops and RevOps teams importing leads into a CRM, e-commerce teams merging supplier price lists, finance/analysts normalizing exports, and developers who need a reliable "clean this file" step in Make, Zapier, n8n or their own pipeline.

### What it does

- **Reads** CSV/TSV (auto-detects delimiter and encoding), Excel `.xlsx` (any sheet) and JSON / JSON Lines – from pasted text, a file URL, an Apify dataset or a key-value store record.
- **Cleans**: trim and collapse whitespace · repair mojibake (`Café` → `Café`), smart quotes, invisible characters, non-breaking spaces · normalize headers (snake_case, lower, upper, title) · normalize blanks/nulls · find/replace (literal or regex) · value casing · include/exclude columns.
- **Converts types**: currency & percent symbols stripped into true numbers (`$1,250.50` → `1250.5`, `(1.234,50)` → `-1234.5`) · dates to ISO / US / EU format.
- **Validates (flags only)**: email and phone *format* checks. Nothing is verified, contacted or sent.
- **Removes duplicates**: exact rows, key columns, or **fuzzy** (similar names, e.g. `Acme Corp.` ≈ `ACME Corp`) – you choose which duplicate wins (first, last, most complete).
- **Quarantines bad rows** instead of silently dropping them, with the reason and the original values.
- **Audit trail**: before/after counts, a row-level change log, a column profiling report (type, % null, unique count, top values) and `RUN_SUMMARY.json`.
- **Exports**: CSV, JSON and a multi-sheet Excel workbook (**Clean**, **Rejected**, **Summary** tabs) plus a dataset with an overview view.
- **Saved cleaning profiles**: reuse the same settings by name (`profileName`) or as pasted JSON.

### How to use it on Apify (inputs and outputs)

**There is one input form, with two kinds of input:** your **data** and your **settings**.

1. **Your data** (section 1 of the form) – pick a *Source type*:
   - **Pasted text** – paste your CSV *as it is* (no JSON conversion needed; JSON also works). Good for small data.
   - **File URL → Data file** – click *Upload* to upload a `.csv` / `.xlsx` / `.json` from your computer (it is stored in a key-value store of your account), or paste a public link.
   - **Apify dataset** – clean the output of another Actor (enter its dataset ID).
   - **Key-value store record** – a file you already stored (store ID + record key).
2. **Your settings** – either tick/fill the form fields (sections 2–6), **or** upload a **Settings file (JSON)** in section 7, for example:
   ```json
   {"headerCase": "snake_case", "numericColumns": ["Price"], "dateColumns": ["Date"], "dedupeMode": "key", "dedupeKeyColumns": ["Email"]}
   ```
   Fields in the settings file replace the same fields of the form. (It is the same file format the local `scripts/clean_local.py --settings` uses.) You can also save settings by name (`saveProfileAs`) and reuse them (`profileName`).
3. Press **Start**, then open the run:
   - **Output tab**: *Cleaned rows* (the dataset, overview table) and links to the **Clean files** (CSV / JSON / XLSX), **Rejected rows**, **Change log**, **Column profile** and **Run summary**.
   - **Storage → Key-value store** has all those files (`CLEAN.csv`, `CLEAN.xlsx`, `REJECTED.csv`, `CHANGE_LOG.csv`, `COLUMN_PROFILE.json`, `RUN_SUMMARY.json`). **Storage → Dataset** has one item per clean row.
   - Running locally with `apify run`/`python -m src`, the same things are in the `storage/` folder (`storage/datasets/default`, `storage/key_value_stores/default`) – nothing is printed to the terminal except status messages.

Tip: tick **Dry run** first to preview on a small sample (free, never charged), review `CHANGE_LOG.csv` and `REJECTED.csv`, then run the full file.

### Before / after example

**Input** (`pastedText`, settings: snake_case headers, numeric columns `Amount Paid` + `Discount`, date column `Signup Date`, email column `Email`, key-based duplicate removal on `Customer ID`):

```csv
Customer ID,Full Name ,Email,Phone,Signup Date,Amount Paid,Discount
1001,"  Ana   García ",ANA@EXAMPLE.COM,+34 612 345 678,03/15/2024,"$1,250.50",10%
1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
1003,Café Owner,cafe@example.com,555-0100,17 Mar 2024,"1,000",5 %
1004,Sam Lee,sam@example.com,,not a date,12abc,
```

**`CLEAN.csv`** – 3 rows delivered (and charged):

```csv
customer_id,full_name,email,phone,signup_date,amount_paid,discount,_flags
1001,Ana   García,ana@example.com,+34 612 345 678,2024-03-15,1250.5,10,
1002,Bob O'Neil,bob@example,(555) 010-9999,2024-03-16,99,,email_invalid:email
1003,Café Owner,cafe@example.com,555-0100,2024-03-17,1000,5,
```

**`REJECTED.csv`** – 2 rows quarantined with the reason (not charged):

```csv
_row,_reason,Customer ID,Full Name,Email,Phone,Signup Date,Amount Paid,Discount
4,duplicate_of_row:3,1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
6,date_invalid:signup_date; number_invalid:amount_paid,1004,Sam Lee,sam@example.com,,not a date,12abc,
```

**`RUN_SUMMARY.json`** (excerpt): 5 rows read → 3 clean, 2 rejected (1 duplicate), 12 cells changed in 3 rows – `numeric` ×5, `date` ×2, `repair_encoding` ×2, `trim`, `lowercase_email`, `blank_to_null` ×1 each – and 1 flag (`email_invalid`). Row numbers match Excel (header = row 1).

### Input

Every field has a tooltip in the Console form. Columns can be named by their original or normalized header (case-insensitive). Unknown field names are rejected with a "did you mean…" hint.

**1 · Data source**

| Field | Type | Default | What it does |
|---|---|---|---|
| `sourceType` | one of `text`/`url`/`dataset`/`keyValueStore` | `text` | Source type. Where the data comes from. Fill in the matching field below. |
| `pastedText` | string | – | Pasted text. CSV or JSON text (max 5 MB). Used when Source type is 'Pasted text'. |
| `fileUrl` | string | – | Data file (upload or URL). Upload your .csv, .xlsx or .json file here (Console: pick 'Upload' to store it in a key-value store), or paste a public http(s) link. The file is read once; only the location you give is contacted. |
| `datasetId` | string | – | Apify dataset ID. ID of the dataset to clean (e.g. output of another Actor). Nested values are converted to JSON text. |
| `kvStoreId` | string | – | Key-value store ID. Leave empty to use this run's default store. Used with 'Record key'. |
| `kvRecordKey` | string | – | Record key. Key of the CSV, XLSX or JSON record to clean. |
| `inputFormat` | one of `auto`/`csv`/`xlsx`/`json` | `auto` | File format. Auto-detect from the content, or force a format. |
| `sheetName` | string | – | Excel sheet name. Sheet to read. Empty = first sheet. |
| `hasHeader` | boolean | `true` | First row is a header. Untick if the file has no header row; columns are then named column_1, column_2, ... |
| `encoding` | one of `auto`/`utf-8`/`cp1252`/`latin-1`/`utf-16` | `auto` | Text encoding. Auto tries UTF-8 first, then Windows-1252. Pick one explicitly if accents look wrong. |
| `delimiter` | one of `auto`/`,`/`;`/`\t`/`\|` | `auto` | CSV delimiter. Field separator of the CSV input. |

**2 · Cleaning basics**

| Field | Type | Default | What it does |
|---|---|---|---|
| `trimWhitespace` | boolean | `true` | Trim whitespace. Remove leading/trailing spaces from every cell. |
| `collapseInternalSpaces` | boolean | `false` | Collapse repeated spaces. Turn runs of spaces/tabs inside a cell into one space ('Ana   García' → 'Ana García'). |
| `repairEncoding` | boolean | `true` | Repair encoding problems. Fix mojibake (e.g. 'Café' → 'Café'), smart quotes, invisible characters and non-breaking spaces. |
| `headerCase` | one of `none`/`snake_case`/`lower`/`upper`/`title` | `none` | Normalize headers. Headers are always trimmed, de-duplicated and never empty. Choose a casing style, or keep the originals. |
| `normalizeBlanks` | boolean | `true` | Normalize blanks / nulls. Treat empty cells and the null tokens below as one consistent empty value. |
| `nullTokens` | list | 10 items | Null tokens. Cell texts treated as empty (exact match after trimming). Note '-' and '0' are never treated as null unless you add them. |
| `blankFill` | string | – | Fill empty cells with. Text written into empty cells of CSV/XLSX output (JSON and dataset keep null). Empty = leave blank. |
| `caseRules` | list of objects | – | Value casing rules. List of {"column": "Name", "case": "title"} (case: lower, upper, title). Use "*" for all columns. |
| `findReplaceRules` | list of objects | – | Find / replace rules. List of {"column": "*", "find": "St.", "replace": "Street", "mode": "literal|regex", "caseSensitive": true, "wholeCell": false}. Applied in order. |

**3 · Numbers, dates & validation**

| Field | Type | Default | What it does |
|---|---|---|---|
| `numericColumns` | list | – | Numeric columns. Columns to convert into true numbers: strips currency symbols/codes, % and thousands separators; (1,234) becomes -1234. |
| `decimalSeparator` | one of `auto`/`.`/`,` | `auto` | Decimal separator. 'Auto' treats '1,234' as 1234 and '1,5' as 1.5. Pick one when your data is consistently European or US. |
| `percentMode` | one of `number`/`fraction` | `number` | Percent handling. '10%' becomes 10 (number) or 0.1 (fraction). |
| `dateColumns` | list | – | Date columns. Columns to convert into one consistent date format. |
| `dayFirst` | boolean | `false` | Day before month (31/12/2024). How to read ambiguous dates like 03/04/2024. Ambiguous values are flagged in the output. |
| `dateOutputFormat` | one of `iso`/`iso_date`/`us`/`eu` | `iso` | Date output format. ISO keeps a time part when the input had one; 'ISO date only' removes it. |
| `emailColumns` | list | – | Email columns. Columns checked for a valid email format (flags only; no emails are verified or sent). |
| `lowercaseEmails` | boolean | `true` | Lowercase emails. Lowercase values in the email columns. |
| `phoneColumns` | list | – | Phone columns. Columns checked for a plausible phone format (7–15 digits). |
| `phoneFormat` | one of `none`/`e164` | `none` | Phone output. 'E.164' only rewrites numbers that already start with + or 00; other numbers are left unchanged and flagged. |

**4 · Columns & row rules**

| Field | Type | Default | What it does |
|---|---|---|---|
| `includeColumns` | list | – | Keep only these columns. Empty = keep all columns. |
| `excludeColumns` | list | – | Drop these columns. Columns removed from the output. |
| `requiredColumns` | list | – | Required columns. Rows with an empty value here are handled by the error policy. |
| `errorPolicy` | one of `quarantine`/`keep`/`skip`/`fail` | `quarantine` | Error policy. What to do with a row that has an unparseable number/date, a missing required value or too many cells. |
| `preserveOriginals` | boolean | `false` | Preserve originals. Adds an '\_original' column with the original values of every cell that was changed in that row. |

**5 · Duplicates**

| Field | Type | Default | What it does |
|---|---|---|---|
| `dedupeMode` | one of `none`/`exact`/`key`/`fuzzy` | `none` | Duplicate detection. How duplicates are found. |
| `dedupeKeyColumns` | list | – | Key columns. Required for key and fuzzy modes. |
| `dedupeCaseInsensitive` | boolean | `true` | Ignore case in key. Key mode only: compare keys case-insensitively and trimmed. |
| `fuzzyThreshold` | number | `0.9` | Fuzzy similarity threshold. 0.5–1.0. Higher = stricter. Keys are lowercased, accent-stripped and punctuation-free before comparing. |
| `duplicatePrecedence` | one of `first`/`last`/`mostComplete` | `first` | Which duplicate wins. The row that stays in the clean output. |
| `quarantineDuplicates` | boolean | `true` | Send removed duplicates to Rejected export. Keeps an audit trail of every removed row. |

**6 · Output**

| Field | Type | Default | What it does |
|---|---|---|---|
| `outputFormats` | multi-select | csv | Export formats. Files saved to the key-value store (CLEAN.csv / CLEAN.json / CLEAN.xlsx). Excel has Clean, Rejected and Summary tabs. |
| `outputCsvDelimiter` | one of `,`/`;`/`\t`/`\|` | `,` | CSV output delimiter. Field separator used in CLEAN.csv and REJECTED.csv. |
| `csvUtf8Bom` | boolean | `false` | Add UTF-8 BOM to CSV. Helps older Excel versions open accents correctly. |
| `escapeFormulaCells` | boolean | `false` | Neutralize spreadsheet formulas in CSV. Prefix text cells starting with = + - @ with an apostrophe (numbers untouched). Changes data, so off by default. XLSX output never stores text as formulas. |
| `includeFlagsColumn` | boolean | `true` | Add '\_flags' column. Shows validation flags per row when email/phone checks or the 'keep' policy are used. |
| `changeLog` | boolean | `true` | Write row-level change log. CHANGE_LOG.csv: every changed cell with the rules that changed it. |
| `changeLogIncludeValues` | boolean | `true` | Include values in change log. Untick to log only row/column/rule (no before/after values) if the log is shared. |
| `maxChangeLogEntries` | integer | `10000` | Max change log entries. Counts stay exact; entries beyond the cap are not written. |
| `profileColumns` | one of `off`/`after`/`both` | `after` | Column profiling report. Type, % null, unique count and top values per column (COLUMN_PROFILE.json). |

**7 · Settings file & saved profiles**

| Field | Type | Default | What it does |
|---|---|---|---|
| `settingsFileUrl` | string | – | Settings file (JSON, optional). Upload or link a JSON file with settings, e.g. {"headerCase":"snake_case","numericColumns":\["Price"]}. Values in the file replace the same fields in this form. Source fields cannot be set in it. Same format as the local 'clean_local.py --settings' file. |
| `profileName` | string | – | Load saved profile. Name of a profile saved earlier. Its cleaning settings (sections 2–5) replace the ones in this form. |
| `profileJson` | object | – | Inline profile (JSON). Paste a profile document {"profileVersion":1,"settings":{...}} instead of loading by name. Do not set both. |
| `saveProfileAs` | string | – | Save these settings as profile. Letters, digits, . \_ - only (max 60). Saved after a successful run. |

**8 · Run & limits**

| Field | Type | Default | What it does |
|---|---|---|---|
| `dryRun` | boolean | `false` | Dry run (preview). Process only a small sample, never charge, and mark the result as a preview. |
| `dryRunSampleSize` | integer | `100` | Dry run sample size. Rows processed in dry run. |
| `chunkSize` | integer | `1000` | Chunk size. Rows processed per chunk. Keeps memory bounded. |
| `maxRows` | integer | `500000` | Max rows. Rows beyond this are not processed. |
| `maxFileSizeMb` | integer | `100` | Max input size (MB). Larger inputs fail before processing. |
| `maxOutputSizeMb` | integer | `200` | Max output size (MB, per file). Processing stops cleanly when exceeded. |
| `timeoutSeconds` | integer | `3000` | Timeout (seconds). Processing stops cleanly when exceeded. |
| `memoryLimitMb` | integer | `0` | Memory guard (MB). 0 = 85% of the run's memory. Processing stops cleanly if exceeded. |

**Order of cleaning steps** (per cell): repair encoding → trim → collapse spaces → blank/null tokens → find/replace → value casing → number/date conversion → email/phone checks. Column selection (include/exclude) happens last.

**Row numbers**: CSV/XLSX rows are numbered as in Excel (header = row 1, first data row = 2). JSON/JSONL: record 1 is the first object.

#### Sample input

```json
{
  "sourceType": "text",
  "pastedText": "id,amount,signup\n1,\"$1,000.50\",03/15/2024\n2,12abc,2024-03-16\n",
  "headerCase": "snake_case",
  "numericColumns": ["amount"],
  "dateColumns": ["signup"],
  "errorPolicy": "quarantine",
  "outputFormats": ["csv", "xlsx"]
}
```

A complete example is in `sample_input.json` (the same values the form is pre-filled with).

### Output

| Where | What |
|---|---|
| **Dataset** (`overview` view) | One item per delivered clean row: `rowNumber`, `status` (`clean` / `preview`), `data` (the cleaned row), `flags`, `changeCount` (and `originals` if enabled). |
| Key-value store `CLEAN.csv` / `.json` / `.xlsx` | The cleaned data in the formats you ticked. The `.xlsx` has **Clean**, **Rejected**, **Summary** (and **Profile**) tabs. |
| `REJECTED.csv` / `.json` | Quarantined rows with `_row`, `_reason` and the **original** values so you can fix and re-run. Removed duplicates are listed here too (`duplicate_of_row:N`). |
| `CHANGE_LOG.csv` | `row, scope, column, rules, before, after` – every changed cell, plus a line per quarantined/removed row. |
| `COLUMN_PROFILE.json` | Per column: type, null count/%, unique count, top values (before and/or after). |
| `RUN_SUMMARY.json` | Status, counts, before/after, changes by rule, flags by type, billing, effective settings, warnings, timing, and the **disclaimer**. |

Statuses: `SUCCEEDED`, `PARTIAL` (a limit was hit – see `stoppedReason`: `max_rows`, `timeout`, `memory`, `output_size`, `charge_limit`), `DRY_RUN`, `FAILED` (invalid input, or `errorPolicy: fail`).

### Pricing

**Pay per event**: you are charged for each **clean record delivered** to the dataset/exports (event `cleaned-record`). Rejected rows, removed duplicates, dry-run previews and skipped rows are **not** charged. Apify's standard Actor-start event may also apply.

> **Test price, not final:** the Actor ships with $0.001 per record (= **$1.00 per 1,000 records**) for evaluation; the intended test range is **$0.50–$2.00 per 1,000 records**. The final price is set in the Apify Console and shown on the Store page.

**Max charge limit:** if you set a maximum cost per run, the Actor stops cleanly when the next record would exceed it, delivers everything processed so far (dataset, exports and summary are consistent with what you were charged for), sets status `PARTIAL` with `stoppedReason: charge_limit` and a clear status message. Raise the limit and re-run to process the rest.

### Limits (all configurable in "Run & limits", with hard caps)

Max rows (default 500,000, cap 5,000,000) · max input size (default 100 MB, cap 500 MB) · pasted text 5 MB · max output per file (default 200 MB) · timeout (default 3,000 s) · memory guard (default 85 % of the run's memory) · 1,000 columns · 200 rules. Input over the size limit fails before processing. Row/time/memory/output/charge limits stop the run cleanly with a **partial result** and a message. Throughput is roughly 3,000–10,000 rows/s depending on options (two-pass duplicate precedence, Excel output and profiling are the slowest). Use 1,024 MB+ memory for large files.

### Limitations and behaviours to know

- **Ambiguous values are guessed by explicit rules, not intelligence.** `03/04/2024` follows `dayFirst`; ambiguous dates are flagged (`date_ambiguous`). `1,234` is read as 1234 in *Auto* decimal mode – set `decimalSeparator` if your data is European.
- **Dates**: English month names only; two-digit years and Excel serial numbers in CSV are not converted (they are quarantined/kept per the error policy). Time zone offsets are preserved, not converted.
- **Casing rules are blunt**: `title` makes `O'Neil` → `O'neil` and `McDonald` → `Mcdonald`. Use find/replace for exceptions.
- **Phone**: checks the format (7–15 digits); E.164 output only rewrites numbers that already start with `+` or `00` – no country guessing.
- **Fuzzy duplicates** compare normalized keys (lowercase, accent- and punctuation-free) using string similarity, with candidate blocking by the first/last three characters: a typo in both the start and end of a key can be missed. Limited to 100,000 distinct keys; use *Key columns* for larger files. Rows with an empty key are never duplicates.
- **Excel input**: reads cached cell values (formulas are not recalculated; never-calculated formulas are empty), the chosen sheet only, `.xlsx` only (not legacy `.xls`; macros are ignored). Text that looks like a formula is never stored as a formula in the `.xlsx` output.
- **JSON input**: arrays of objects or JSON Lines; nested values become JSON text; the column set is the union of all keys.
- **CSV formula injection**: CSV output is written as-is by default. Enable `escapeFormulaCells` if the file will be opened in a spreadsheet by people you do not trust the data source of (this prefixes text starting with `= + - @` with `'`).
- Numbers in `.json` output are JSON numbers (very long decimals can lose precision beyond ~15 digits); CSV keeps exact text.
- JSON exports (`CLEAN.json`, `REJECTED.json`) larger than 64 MB are stored with a generic content type (download and rename if needed); use CSV for very large outputs.
- Dataset records have a fixed envelope (`data` holds the row). Use the `CLEAN.*` files if you need a flat table.

### FAQ

**Is any AI used?** No. Plain rules and string/number/date parsing only. No model calls, no scraping or crawling, no network access except the single download of the file URL *you* provide.

**Can it damage my data?** Any automated transformation can. That is why every change is logged, originals can be preserved (`preserveOriginals`), bad rows are quarantined rather than deleted, dry run is free and the summary repeats the disclaimer. Always review before importing into a production system and keep your original file.

**What happens to my data?** It is processed in memory and temporary files inside the run and deleted when the run ends. Only the output files you receive remain in *your* storage. Cell values are never written to the run log; error messages name the row and column, not the value. Note: the dataset, key-value store and files are stored in your Apify account under your retention settings. Saved profiles contain settings only.

**A column is not found.** Names are matched against the original and the normalized header, case-insensitively. The error lists the available columns.

**Which duplicate is kept?** `first` (default), `last` or `mostComplete` (most non-empty cells; ties keep the first). Rejected rows never win. Removed duplicates are listed in `REJECTED` unless you untick the option.

**Why were rows rejected?** See `_reason` in `REJECTED.csv`: `number_invalid:<col>`, `date_invalid:<col>`, `required_missing:<col>`, `too_many_cells`, `duplicate_of_row:N`. Change `errorPolicy` to `keep` (keep + flag), `skip` or `fail`.

**Is the output always identical?** Yes for the same input and settings (no randomness, no time-dependence).

**Can I re-use settings?** Set `saveProfileAs` once (stored as a record in your named store `csv-excel-cleaner-profiles`), then use `profileName` – or paste a profile JSON into `profileJson`. A profile replaces the cleaning settings (sections 2–5); source, output and limits come from the run's own input.

### Integrations

**API call** (run synchronously and get the cleaned rows):

```bash
curl -X POST "https://api.apify.com/v2/acts/<YOUR_USERNAME>~csv-excel-data-cleaner/run-sync-get-dataset-items?token=<YOUR_API_TOKEN>" \
  -H "Content-Type: application/json" \
  -d '{"sourceType":"url","fileUrl":"https://example.com/leads.csv","headerCase":"snake_case","emailColumns":["email"],"dedupeMode":"key","dedupeKeyColumns":["email"]}'
```

For big files start the run asynchronously (`POST /v2/acts/<ACTOR>/runs`), wait for it to finish, then download a file from the run's key-value store:
`GET https://api.apify.com/v2/actor-runs/<RUN_ID>/key-value-store/records/CLEAN.csv?token=<YOUR_API_TOKEN>` (same for `REJECTED.csv`, `CHANGE_LOG.csv`, `RUN_SUMMARY.json`).

**Make** – *Apify* app → *Run an Actor* (this Actor, pass the fields as JSON) → *Get Dataset Items* (or *Download a record* from the key-value store for `CLEAN.csv`). Route on `RUN_SUMMARY.json` → `counts.rowsRejected` to alert someone when rows were quarantined.

**Zapier** – *Apify* app → *Run Actor* action, then *Fetch Dataset Items* or *Get Key-Value Store Record*; trigger on a new file in Google Drive/Dropbox/email attachment and pass its public URL as `fileUrl`.

**n8n** – *Apify* node → *Run an Actor* → *Get dataset items*, or an *HTTP Request* node with the `curl` call above.

**Google Sheets** – (1) Run the Actor and use Apify's Google Sheets integration/export on the dataset (each item has a nested `data` object – prefer the CSV route), or (2) publish `CLEAN.csv` through the API URL above and load it with `=IMPORTDATA("<signed or public URL>")`, or (3) Apps Script:

```javascript
function cleanedRows() {
  const url = 'https://api.apify.com/v2/acts/<YOUR_USERNAME>~csv-excel-data-cleaner/run-sync-get-dataset-items?token=<YOUR_API_TOKEN>';
  const res = UrlFetchApp.fetch(url, {method: 'post', contentType: 'application/json',
    payload: JSON.stringify({sourceType: 'url', fileUrl: '<PUBLIC_CSV_URL>', headerCase: 'snake_case'})});
  const items = JSON.parse(res.getContentText());
  const sheet = SpreadsheetApp.getActiveSheet();
  const headers = Object.keys(items[0].data);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(2, 1, items.length, headers.length).setValues(items.map(i => headers.map(h => i.data[h] ?? '')));
}
```

### Privacy

No data is kept beyond the run except the outputs you receive in your own Apify storage. No personal data is written to logs (logs contain counts, column names and error types only). The Actor makes no network requests other than downloading the file URL you provide (private/local addresses are refused) and, if you use them, reading your own Apify dataset or key-value store.

### Disclaimers

Automated cleaning can change or remove data in ways you did not expect. Always review the Rejected export, the change log and the summary, test with Dry run first, and keep a copy of your original file. Results are provided as is, without warranty; you remain responsible for checking the cleaned data before using it. Email and phone checks are format checks only – nothing is verified, contacted or sent. The same disclaimer is written into every `RUN_SUMMARY.json`.

### Development

```bash
pip install -r requirements.txt
pytest                                   # unit + golden-file tests
UPDATE_GOLDEN=1 pytest tests/test_golden.py   # only after an intentional behaviour change; review the diff
python scripts/generate_input_schema.py  # regenerate .actor/input_schema.json from src/spec.py
python scripts/build_readme.py           # regenerate the input table in README.md from README.template.md
apify run --input-file sample_input.json # local run (results in ./storage)
apify push                               # deploy
```

Versions: input schema `1.0.0` (see `CHANGELOG.md`; the version is also written to every summary). Requires Python 3.11+.

# Changelog

This Actor's version history is a separate document: https://apify.com/makentest/csv-excel-data-cleaner-dedupe-fix/changelog.md

# Actor input Schema

## `sourceType` (type: `string`):

Where the data comes from. Fill in the matching field below.

## `pastedText` (type: `string`):

CSV or JSON text (max 5 MB). Used when Source type is 'Pasted text'.

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

Upload your .csv, .xlsx or .json file here (Console: pick 'Upload' to store it in a key-value store), or paste a public http(s) link. The file is read once; only the location you give is contacted.

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

ID of the dataset to clean (e.g. output of another Actor). Nested values are converted to JSON text.

## `kvStoreId` (type: `string`):

Leave empty to use this run's default store. Used with 'Record key'.

## `kvRecordKey` (type: `string`):

Key of the CSV, XLSX or JSON record to clean.

## `inputFormat` (type: `string`):

Auto-detect from the content, or force a format.

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

Sheet to read. Empty = first sheet.

## `hasHeader` (type: `boolean`):

Untick if the file has no header row; columns are then named column_1, column_2, ...

## `encoding` (type: `string`):

Auto tries UTF-8 first, then Windows-1252. Pick one explicitly if accents look wrong.

## `delimiter` (type: `string`):

Field separator of the CSV input.

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

Remove leading/trailing spaces from every cell.

## `collapseInternalSpaces` (type: `boolean`):

Turn runs of spaces/tabs inside a cell into one space ('Ana   García' → 'Ana García').

## `repairEncoding` (type: `boolean`):

Fix mojibake (e.g. 'Café' → 'Café'), smart quotes, invisible characters and non-breaking spaces.

## `headerCase` (type: `string`):

Headers are always trimmed, de-duplicated and never empty. Choose a casing style, or keep the originals.

## `normalizeBlanks` (type: `boolean`):

Treat empty cells and the null tokens below as one consistent empty value.

## `nullTokens` (type: `array`):

Cell texts treated as empty (exact match after trimming). Note '-' and '0' are never treated as null unless you add them.

## `blankFill` (type: `string`):

Text written into empty cells of CSV/XLSX output (JSON and dataset keep null). Empty = leave blank.

## `caseRules` (type: `array`):

List of {"column": "Name", "case": "title"} (case: lower, upper, title). Use "\*" for all columns.

## `findReplaceRules` (type: `array`):

List of {"column": "\*", "find": "St.", "replace": "Street", "mode": "literal|regex", "caseSensitive": true, "wholeCell": false}. Applied in order.

## `numericColumns` (type: `array`):

Columns to convert into true numbers: strips currency symbols/codes, % and thousands separators; (1,234) becomes -1234.

## `decimalSeparator` (type: `string`):

'Auto' treats '1,234' as 1234 and '1,5' as 1.5. Pick one when your data is consistently European or US.

## `percentMode` (type: `string`):

'10%' becomes 10 (number) or 0.1 (fraction).

## `dateColumns` (type: `array`):

Columns to convert into one consistent date format.

## `dayFirst` (type: `boolean`):

How to read ambiguous dates like 03/04/2024. Ambiguous values are flagged in the output.

## `dateOutputFormat` (type: `string`):

ISO keeps a time part when the input had one; 'ISO date only' removes it.

## `emailColumns` (type: `array`):

Columns checked for a valid email format (flags only; no emails are verified or sent).

## `lowercaseEmails` (type: `boolean`):

Lowercase values in the email columns.

## `phoneColumns` (type: `array`):

Columns checked for a plausible phone format (7–15 digits).

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

'E.164' only rewrites numbers that already start with + or 00; other numbers are left unchanged and flagged.

## `includeColumns` (type: `array`):

Empty = keep all columns.

## `excludeColumns` (type: `array`):

Columns removed from the output.

## `requiredColumns` (type: `array`):

Rows with an empty value here are handled by the error policy.

## `errorPolicy` (type: `string`):

What to do with a row that has an unparseable number/date, a missing required value or too many cells.

## `preserveOriginals` (type: `boolean`):

Adds an '\_original' column with the original values of every cell that was changed in that row.

## `dedupeMode` (type: `string`):

How duplicates are found.

## `dedupeKeyColumns` (type: `array`):

Required for key and fuzzy modes.

## `dedupeCaseInsensitive` (type: `boolean`):

Key mode only: compare keys case-insensitively and trimmed.

## `fuzzyThreshold` (type: `number`):

0.5–1.0. Higher = stricter. Keys are lowercased, accent-stripped and punctuation-free before comparing.

## `duplicatePrecedence` (type: `string`):

The row that stays in the clean output.

## `quarantineDuplicates` (type: `boolean`):

Keeps an audit trail of every removed row.

## `outputFormats` (type: `array`):

Files saved to the key-value store (CLEAN.csv / CLEAN.json / CLEAN.xlsx). Excel has Clean, Rejected and Summary tabs.

## `outputCsvDelimiter` (type: `string`):

Field separator used in CLEAN.csv and REJECTED.csv.

## `csvUtf8Bom` (type: `boolean`):

Helps older Excel versions open accents correctly.

## `escapeFormulaCells` (type: `boolean`):

Prefix text cells starting with = + - @ with an apostrophe (numbers untouched). Changes data, so off by default. XLSX output never stores text as formulas.

## `includeFlagsColumn` (type: `boolean`):

Shows validation flags per row when email/phone checks or the 'keep' policy are used.

## `changeLog` (type: `boolean`):

CHANGE_LOG.csv: every changed cell with the rules that changed it.

## `changeLogIncludeValues` (type: `boolean`):

Untick to log only row/column/rule (no before/after values) if the log is shared.

## `maxChangeLogEntries` (type: `integer`):

Counts stay exact; entries beyond the cap are not written.

## `profileColumns` (type: `string`):

Type, % null, unique count and top values per column (COLUMN_PROFILE.json).

## `settingsFileUrl` (type: `string`):

Upload or link a JSON file with settings, e.g. {"headerCase":"snake_case","numericColumns":\["Price"]}. Values in the file replace the same fields in this form. Source fields cannot be set in it. Same format as the local 'clean_local.py --settings' file.

## `profileName` (type: `string`):

Name of a profile saved earlier. Its cleaning settings (sections 2–5) replace the ones in this form.

## `profileJson` (type: `object`):

Paste a profile document {"profileVersion":1,"settings":{...}} instead of loading by name. Do not set both.

## `saveProfileAs` (type: `string`):

Letters, digits, . \_ - only (max 60). Saved after a successful run.

## `dryRun` (type: `boolean`):

Process only a small sample, never charge, and mark the result as a preview.

## `dryRunSampleSize` (type: `integer`):

Rows processed in dry run.

## `chunkSize` (type: `integer`):

Rows processed per chunk. Keeps memory bounded.

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

Rows beyond this are not processed.

## `maxFileSizeMb` (type: `integer`):

Larger inputs fail before processing.

## `maxOutputSizeMb` (type: `integer`):

Processing stops cleanly when exceeded.

## `timeoutSeconds` (type: `integer`):

Processing stops cleanly when exceeded.

## `memoryLimitMb` (type: `integer`):

0 = 85% of the run's memory. Processing stops cleanly if exceeded.

## Actor input object example

```json
{
  "sourceType": "text",
  "pastedText": "Customer ID,Full Name ,Email,Phone,Signup Date,Amount Paid,Discount\n1001,\"  Ana   García \",ANA@EXAMPLE.COM,+34 612 345 678,03/15/2024,\"$1,250.50\",10%\n1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A\n1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A\n1003,Café Owner,cafe@example.com,555-0100,17 Mar 2024,\"1,000\",5 %\n1004,Sam Lee,sam@example.com,,not a date,12abc,\n",
  "fileUrl": "",
  "datasetId": "",
  "kvStoreId": "",
  "kvRecordKey": "",
  "inputFormat": "auto",
  "sheetName": "",
  "hasHeader": true,
  "encoding": "auto",
  "delimiter": "auto",
  "trimWhitespace": true,
  "collapseInternalSpaces": false,
  "repairEncoding": true,
  "headerCase": "snake_case",
  "normalizeBlanks": true,
  "nullTokens": [
    "null",
    "NULL",
    "N/A",
    "n/a",
    "NA",
    "#N/A",
    "nan",
    "NaN",
    "none",
    "None"
  ],
  "blankFill": "",
  "caseRules": [],
  "findReplaceRules": [],
  "numericColumns": [
    "Amount Paid",
    "Discount"
  ],
  "decimalSeparator": "auto",
  "percentMode": "number",
  "dateColumns": [
    "Signup Date"
  ],
  "dayFirst": false,
  "dateOutputFormat": "iso",
  "emailColumns": [
    "Email"
  ],
  "lowercaseEmails": true,
  "phoneColumns": [
    "Phone"
  ],
  "phoneFormat": "none",
  "includeColumns": [],
  "excludeColumns": [],
  "requiredColumns": [
    "Customer ID"
  ],
  "errorPolicy": "quarantine",
  "preserveOriginals": false,
  "dedupeMode": "key",
  "dedupeKeyColumns": [
    "Customer ID"
  ],
  "dedupeCaseInsensitive": true,
  "fuzzyThreshold": 0.9,
  "duplicatePrecedence": "first",
  "quarantineDuplicates": true,
  "outputFormats": [
    "csv"
  ],
  "outputCsvDelimiter": ",",
  "csvUtf8Bom": false,
  "escapeFormulaCells": false,
  "includeFlagsColumn": true,
  "changeLog": true,
  "changeLogIncludeValues": true,
  "maxChangeLogEntries": 10000,
  "profileColumns": "after",
  "settingsFileUrl": "",
  "profileName": "",
  "saveProfileAs": "",
  "dryRun": false,
  "dryRunSampleSize": 100,
  "chunkSize": 1000,
  "maxRows": 500000,
  "maxFileSizeMb": 100,
  "maxOutputSizeMb": 200,
  "timeoutSeconds": 3000,
  "memoryLimitMb": 0
}
```

# Actor output Schema

## `cleanedRows` (type: `string`):

One item per delivered clean row: rowNumber, status, data (the cleaned row), flags, changeCount.

## `cleanFiles` (type: `string`):

CLEAN.csv, CLEAN.json and CLEAN.xlsx (Clean, Rejected, Summary tabs), depending on the chosen export formats.

## `rejectedFiles` (type: `string`):

REJECTED.csv / REJECTED.json: rows that were quarantined or removed as duplicates, with the reason and original values.

## `changeLog` (type: `string`):

CHANGE_LOG.csv: every changed cell with the rules that changed it, before and after.

## `columnProfile` (type: `string`):

COLUMN_PROFILE.json: type, null %, unique count and top values per column.

## `runSummary` (type: `string`):

RUN_SUMMARY.json: status, before/after counts, changes by rule, flags, billing, warnings and disclaimer.

# 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 = {
    "pastedText": `Customer ID,Full Name ,Email,Phone,Signup Date,Amount Paid,Discount
1001,"  Ana   García ",ANA@EXAMPLE.COM,+34 612 345 678,03/15/2024,"$1,250.50",10%
1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
1003,Café Owner,cafe@example.com,555-0100,17 Mar 2024,"1,000",5 %
1004,Sam Lee,sam@example.com,,not a date,12abc,`,
    "headerCase": "snake_case",
    "caseRules": [],
    "findReplaceRules": [],
    "numericColumns": [
        "Amount Paid",
        "Discount"
    ],
    "dateColumns": [
        "Signup Date"
    ],
    "emailColumns": [
        "Email"
    ],
    "phoneColumns": [
        "Phone"
    ],
    "requiredColumns": [
        "Customer ID"
    ],
    "dedupeMode": "key",
    "dedupeKeyColumns": [
        "Customer ID"
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("makentest/csv-excel-data-cleaner-dedupe-fix").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 = {
    "pastedText": """Customer ID,Full Name ,Email,Phone,Signup Date,Amount Paid,Discount
1001,\"  Ana   García \",ANA@EXAMPLE.COM,+34 612 345 678,03/15/2024,\"$1,250.50\",10%
1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A
1003,Café Owner,cafe@example.com,555-0100,17 Mar 2024,\"1,000\",5 %
1004,Sam Lee,sam@example.com,,not a date,12abc,
""",
    "headerCase": "snake_case",
    "caseRules": [],
    "findReplaceRules": [],
    "numericColumns": [
        "Amount Paid",
        "Discount",
    ],
    "dateColumns": ["Signup Date"],
    "emailColumns": ["Email"],
    "phoneColumns": ["Phone"],
    "requiredColumns": ["Customer ID"],
    "dedupeMode": "key",
    "dedupeKeyColumns": ["Customer ID"],
}

# Run the Actor and wait for it to finish
run = client.actor("makentest/csv-excel-data-cleaner-dedupe-fix").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 '{
  "pastedText": "Customer ID,Full Name ,Email,Phone,Signup Date,Amount Paid,Discount\\n1001,\\"  Ana   García \\",ANA@EXAMPLE.COM,+34 612 345 678,03/15/2024,\\"$1,250.50\\",10%\\n1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A\\n1002,Bob O’Neil,bob@example,(555) 010-9999,2024-03-16,€99,N/A\\n1003,Café Owner,cafe@example.com,555-0100,17 Mar 2024,\\"1,000\\",5 %\\n1004,Sam Lee,sam@example.com,,not a date,12abc,\\n",
  "headerCase": "snake_case",
  "caseRules": [],
  "findReplaceRules": [],
  "numericColumns": [
    "Amount Paid",
    "Discount"
  ],
  "dateColumns": [
    "Signup Date"
  ],
  "emailColumns": [
    "Email"
  ],
  "phoneColumns": [
    "Phone"
  ],
  "requiredColumns": [
    "Customer ID"
  ],
  "dedupeMode": "key",
  "dedupeKeyColumns": [
    "Customer ID"
  ]
}' |
apify call makentest/csv-excel-data-cleaner-dedupe-fix --silent --output-dataset

```

## MCP server setup

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

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/uQ19YL1ykouE4YdYR/builds/UwIYQGqMb5duU6OXq/openapi.json