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:

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

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):

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):

_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

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

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:

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

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+.