# CSV Cleaner: trim, fix case, drop empty rows, deduplicate (`examsens.ai/csv-cleaner`) Actor

Cleans CSV and JSON data: trims whitespace, changes case on the columns you pick, removes empty rows and duplicates by all or chosen columns, and reports how many rows each step removed.

- **URL**: https://apify.com/examsens.ai/csv-cleaner.md
- **Developed by:** [Ed Rayyis](https://apify.com/examsens.ai) (community)
- **Categories:** Developer tools, Automation
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $1.80 / 1,000 row outputs

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

# Actor input Schema

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

Links to CSV, TSV, JSON or JSON Lines files. A Google Sheets link works when the sheet is shared with anyone who has the link. Rows from every source are merged into one table before cleaning.

## `fileStore` (type: `string`):

To use files from your computer, upload them to a key-value store under Storage in Apify Console, then pick that store here. Every record whose name ends in .csv, .tsv, .txt, .json, .jsonl or .ndjson is read, unless you list record keys below.

## `fileStoreKeys` (type: `array`):

Read only these records from the picked key-value store.

## `sourceDataset` (type: `string`):

Clean the items of a dataset, such as the output of another actor run.

## `data` (type: `array`):

A JSON array of objects to clean, for small lists or calls from the API.

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

Auto reads a file as JSON when it starts with \[ or {, and as CSV otherwise.

## `csvDelimiter` (type: `string`):

Leave empty to detect comma, semicolon, tab or pipe. Type tab for tab separated files.

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

Remove spaces, tabs and line breaks at the start and end of every text value.

## `collapseSpaces` (type: `boolean`):

Replace runs of spaces, tabs and line breaks inside a value with one space.

## `emptyToNull` (type: `boolean`):

Output an empty text value as null in the dataset.

## `caseColumns` (type: `array`):

Column names whose text values get the case below, such as email.

## `caseAllColumns` (type: `boolean`):

Apply the case below to every text column.

## `caseMode` (type: `string`):

Lower: jane@example.com. Upper: NEW YORK. Title: New York. Sentence: New york.

## `removeEmptyRows` (type: `boolean`):

Drop rows where every column is empty after trimming.

## `dedupe` (type: `boolean`):

Keep one row for each set of matching rows.

## `dedupeColumns` (type: `array`):

Rows count as duplicates when these columns match, such as email. Leave empty to compare every column.

## `dedupeMatch` (type: `string`):

Exact: values must be identical after cleaning. Ignore case and spaces: Jane@Example.com and jane@example.com match.

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

Which row of a duplicate set stays.

## `keepRowsWithBlankKey` (type: `boolean`):

When matching on chosen columns, keep every row where all of them are empty, so rows with no email are not merged into one.

## `saveRemovedRows` (type: `boolean`):

Also save the removed duplicate rows as a second CSV file in the key-value store.

## `columnsToKeep` (type: `array`):

Output only these columns, in this order. Leave empty to keep all.

## `columnsToRemove` (type: `array`):

Leave these columns out of the output.

## `renameColumns` (type: `array`):

One per line as old:new, such as E-mail Address:email.

## `flattenJson` (type: `boolean`):

Turn nested objects into columns such as address.city. Lists become JSON text in one cell.

## `outputFileName` (type: `string`):

Name of the cleaned CSV record in the key-value store, without .csv.

## `outputDelimiter` (type: `string`):

Delimiter of the cleaned CSV file.

## `excelBom` (type: `boolean`):

Start the CSV file with a UTF-8 byte order mark.

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

Stop reading sources after this many rows in total. Each cleaned row output is one billable event.

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

Larger files are reported as an error and not read.

## Actor input object example

```json
{
  "fileUrls": [
    "https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv"
  ],
  "inputFormat": "auto",
  "trimWhitespace": true,
  "collapseSpaces": false,
  "emptyToNull": true,
  "caseAllColumns": false,
  "caseMode": "lower",
  "removeEmptyRows": true,
  "dedupe": true,
  "dedupeMatch": "exact",
  "keep": "first",
  "keepRowsWithBlankKey": true,
  "saveRemovedRows": false,
  "flattenJson": true,
  "outputFileName": "cleaned",
  "outputDelimiter": "comma",
  "excelBom": false,
  "maxRows": 1000000,
  "maxFileSizeMb": 100
}
```

# Actor output Schema

## `results` (type: `string`):

No description

## `report` (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 = {
    "fileUrls": [
        "https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv"
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("examsens.ai/csv-cleaner").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 = { "fileUrls": ["https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv"] }

# Run the Actor and wait for it to finish
run = client.actor("examsens.ai/csv-cleaner").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 '{
  "fileUrls": [
    "https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv"
  ]
}' |
apify call examsens.ai/csv-cleaner --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,examsens.ai/csv-cleaner"
        }
    }
}
```

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/bPlYzwl4u8chbivc3/builds/bYK4CaXHDNxhpUjAb/openapi.json