CSV Cleaner: trim, fix case, drop empty rows, deduplicate

Give it CSV or JSON data and it returns the cleaned rows, a cleaned CSV file, and a report of how many rows each step removed.

Sources can be mixed in one run, and their rows are merged into one table before cleaning:

• CSV, TSV, JSON and JSON Lines files by link. The CSV delimiter (comma, semicolon, tab or pipe) is detected. A JSON object holding one list of records, such as {"count": 3, "items": [...]}, is read as that list.
• A Google Sheets link, when the sheet is shared with anyone who has the link. The tab in the link (gid) is the one read.
• Files in an Apify key-value store you pick. Upload your own files to a key-value store under Storage in Apify Console first.
• An Apify dataset you pick, such as the output of another actor run.
• A JSON array pasted into the input.

Columns from all sources are combined in the order they first appear. Nested JSON objects become columns such as address.geo.lat, and lists become JSON text in one cell.

Cleaning steps

The steps run in this order:

1. Keep, remove and rename columns. Rename with old:new, such as E-mail Address:email. Later settings use the new names.
2. Trim spaces, tabs and line breaks at both ends of every text value (on by default). Optionally collapse runs of spaces inside a value to one space, so Bob Ray becomes Bob Ray.
3. Change case on the columns you name, or on every column: lower, UPPER, Title or Sentence case.
4. Remove rows where every column is empty (on by default).
5. Remove duplicates (on by default), matching on every column or only the columns you name. Matching is exact, or ignores case and spaces so Ann@Example.com and ann@example.com match. Keep the first or the last row of each duplicate set. When you match on chosen columns, rows where all of them are blank are kept, so two rows without an email are not merged.

Case changes run before duplicate removal, so lowering an email column also makes those emails match.

Input

• File URLs, Files in a key-value store with optional Record keys, Apify dataset, Inline data: at least one source
• File format: Auto (default), CSV or TSV, JSON or JSON Lines
• CSV delimiter (optional, detected when empty)
• Trim whitespace (default on), Collapse inner spaces (default off), Empty text to empty cell (default on)
• Columns to change case, Change case in every column, Case (default lower)
• Remove empty rows (default on)
• Remove duplicates (default on), Match on columns, Matching (Exact or Ignore case and spaces), Row to keep (First or Last), Keep rows with blank match columns (default on), Save removed duplicates (default off)
• Columns to keep, Columns to remove, Rename columns, Flatten nested JSON (default on)
• CSV file name (default cleaned), CSV output delimiter (comma, semicolon or tab), Add byte order mark for Excel (default off)
• Maximum rows read (default 1,000,000), Maximum file size in MB (default 100)

Output

• Dataset: the cleaned rows, each with _row, its position in the cleaned output. Export as JSON, CSV or Excel, or read it over the API. The CSV file in the key-value store has no _row column.
• Key value store, cleaned.csv: the cleaned rows as a UTF-8 CSV file, with the name you choose.
• Key value store, cleaned-duplicates.csv: the removed duplicate rows, when Save removed duplicates is on.
• Key value store, REPORT: rows read from each source, rows read in total, empty rows removed, duplicates removed, rows output, cells trimmed, cells whose case changed, the columns matched for duplicates, and the output columns.

A source that cannot be read adds one dataset item with source and error, and the run continues with the other sources. Errors include a link that answers HTTP 404, a link that returns a web page, an Excel workbook (use a spreadsheet converter first), and a file that is not text. A column named under Match on columns that is not in the data stops the run with a message listing the columns found.

Pricing

Pay per event: one event per cleaned row output. Removed rows and error items are not charged. The run stops when it reaches the maximum charge you set.

Scope and conduct

Only the sources you give are read. Nothing is written back to them. The actor does not check or enrich values against any outside service.

Support

Questions and problems: the Issues tab on this page. Replies are written by an AI assistant on behalf of the developer and reviewed by the developer.