# Dataset & CSV Cleaner → Excel — Filter, Dedupe, Japanese CSV (`masahishop/dataset-cleaner`) Actor

Clean any Apify dataset, CSV, JSON or Excel file: flatten nested fields, filter, dedupe, sort, rename, normalize full-width text. Get an Excel file and a CSV that opens in Excel without garbled Japanese. $0.05 per 1,000 rows.

- **URL**: https://apify.com/masahishop/dataset-cleaner.md
- **Developed by:** [Masahi Shop](https://apify.com/masahishop) (community)
- **Categories:** Developer tools, Automation
- **Stats:** 1 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.04 / 1,000 output rows

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.
Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have.

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

## Dataset & CSV Cleaner → Excel — Filter, Dedupe, Japanese CSV

Turn messy data into a **clean Excel file** in one run. Works on **any Apify dataset** (e.g. a scraper's results), **CSV / TSV / JSON / JSON Lines / Excel files**, or JSON you paste in. **$0.05 per 1,000 output rows.**

- **Flatten nested JSON** into tidy columns (`address.city`), lists into one cell or one column per item
- **Filter** with 15 operators — numbers like `1,200` and dates like `2026/10/07` or `2026年10月7日` are compared correctly
- **Remove duplicates** by one or more fields (or the whole row)
- **Sort** naturally: `2 < 10 < 1,000`, `item2 < item10`, Japanese text in Japanese order, empty values last
- **Choose, order and rename columns** (Japanese column names are fine)
- **Clean text**: trim spaces (incl. full-width `　`), collapse spaces, **normalize full-width / half-width** characters (`ＡＢＣ１２３` → `ABC123`, `ｶﾀｶﾅ` → `カタカナ`) so that Japanese data matches and dedupes correctly
- **Excel output** (.xlsx) with a frozen header row, auto filter and fitted column widths; `"1,200"` becomes a real number, while zip codes and phone numbers with leading zeros stay text
- **CSV that opens in Excel without garbled Japanese** (UTF-8 with BOM), and **Shift_JIS CSV** for older Japanese systems
- Reads **Shift_JIS / EUC-JP / GBK / Big5 / EUC-KR** CSV files (auto-detected)

### Why?

Exported CSV files often look broken when opened in Excel: Japanese, Chinese and Korean text turns into unreadable characters, nested fields appear as `[object Object]`, and numbers like `1,200` are text. This Actor fixes all of that and adds the usual clean-up steps — filtering, deduplication, sorting and column selection — so the result can be shared directly.

### Typical uses

- Make a **scraper's dataset Excel-ready**: flatten, keep 5 columns, rename them, dedupe, sort
- **Merge** a dataset with CSV/Excel files and remove duplicates
- Convert a **Shift_JIS CSV** from a Japanese system to UTF-8 / Excel, or the other way round
- **Filter** a large export to the rows you need (e.g. `price >= 1000` and `prefecture in 東京都, 大阪府`)
- Clean data **before loading it into an AI / RAG pipeline** (JSON Lines output)

### Input

| Field | Description |
|---|---|
| `datasetId` | An Apify dataset (read-only access to that dataset only) |
| `fileUrls` | CSV, TSV, JSON, JSON Lines or .xlsx file URLs |
| `data` | Rows as a JSON array |
| `flattenNested`, `arrayMode` | Nested fields → columns |
| `trimSpaces`, `normalizeWidth`, `collapseSpaces`, `emptyToNull` | Text clean-up |
| `filters`, `filterMode`, `caseSensitive` | Keep matching rows |
| `dedupeBy`, `sortBy`, `offset`, `limit` | Dedupe, sort, slice |
| `fields`, `renameFields` | Columns to keep (in order) and new names |
| `outputFormats` | `xlsx`, `csv-excel`, `csv-sjis`, `csv`, `tsv`, `json`, `jsonl` |

Filter example (rows from Tokyo or Osaka with a price of at least 1,000):

```json
{
    "filters": [
        { "field": "address.pref", "operator": "in", "value": "東京都, 大阪府" },
        { "field": "price", "operator": "gte", "value": "1000" }
    ],
    "filterMode": "all"
}
```

Operators: `equals`, `notEquals`, `contains`, `notContains`, `startsWith`, `endsWith`, `in`, `regex`, `gt`, `gte`, `lt`, `lte`, `isEmpty`, `notEmpty`.

Processing order: load → flatten → clean text → filter → dedupe → sort → offset/limit → select & rename columns.

### Output

- **Files** in the key-value store (Output tab → "Output files"): `data.xlsx`, `data-excel.csv`, … with download links
- **Cleaned rows** in the dataset (optional), so Apify's own exports and integrations work too
- **SUMMARY**: rows in, rows after filters, duplicates removed, rows out, columns, files

Example: the prefilled rows with `normalizeWidth` on, dedupe by `name`, a date filter (`updated` ≥ 2026/10/01), sorting by `-price` and two renamed columns. `ＡＢＣ商店` and `ABC商店` are recognised as the same shop and kept once (CSV for Excel, first lines):

```
name,価格,tags,都道府県,address.city,updated
テスト(株) 😀,15000,,北海道,札幌市,2026-10-01
ABC商店,"1,200",食品; セール,東京都,千代田区,2026年10月7日
```

### Pricing

| Event | Price |
|---|---|
| Output row | $0.00005 ($0.05 per 1,000 rows) |
| Run start | $0.002 |

You pay for the rows in the result, not for rows that were filtered out or removed as duplicates. All output formats are included.

### Notes

- Only the dataset and files you provide are read.
- Characters that do not exist in Shift_JIS (such as emoji) are written as `?` in the Shift_JIS CSV; the run log tells you how many. The Excel file and the UTF-8 CSV keep everything.
- Large inputs are held in memory: about 1 GB per ~200,000 typical rows. Increase the memory for bigger datasets.

# Actor input Schema

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

A dataset from one of your runs (e.g. a scraper's results). The Actor gets read-only access to this dataset only.

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

CSV, TSV, JSON, JSON Lines or Excel (.xlsx) files. Japanese Shift_JIS CSV files are detected automatically.

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

Rows as a JSON array of objects.

## `fileEncoding` (type: `string`):

Auto: UTF-8 (with/without BOM), UTF-16 with BOM, otherwise Shift_JIS.

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

Auto-detects comma, tab or semicolon from the header line.

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

For .xlsx input. Empty = first sheet. The first row is used as the header.

## `flattenNested` (type: `boolean`):

{"address":{"city":"Tokyo"}} becomes a column "address.city" — needed for tidy Excel/CSV output.

## `arrayMode` (type: `string`):

How lists are written into cells.

## `trimSpaces` (type: `boolean`):

Removes leading/trailing spaces, including full-width spaces (　).

## `normalizeWidth` (type: `boolean`):

Unicode NFKC: ＡＢＣ１２３ → ABC123, ｶﾀｶﾅ → カタカナ, ① → 1. Makes Japanese data match and dedupe correctly.

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

Several spaces/tabs inside a value become one space.

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

Text values that are empty after cleaning are written as empty cells (null).

## `filters` (type: `array`):

Keep only rows that match. Each rule: {"field", "operator", "value"}. Operators: equals, notEquals, contains, notContains, startsWith, endsWith, in (comma-separated list), regex, gt, gte, lt, lte (numbers like "1,200" and dates like 2026/10/07 or 2026年10月7日 are compared correctly), isEmpty, notEmpty. Use the field names after flattening (e.g. "address.pref").

## `filterMode` (type: `string`):

How several filter rules are combined.

## `caseSensitive` (type: `boolean`):

By default, text filters ignore upper/lower case.

## `dedupeBy` (type: `array`):

Field names; rows with the same values in these fields are kept once (first one wins). "\*" = whole row.

## `sortBy` (type: `array`):

Field names; prefix with "-" for descending (e.g. "-price"). Numbers, dates and Japanese text are sorted naturally. Empty values go last.

## `offset` (type: `integer`):

Applied after filtering, deduplication and sorting.

## `limit` (type: `integer`):

0 = no limit.

## `fields` (type: `array`):

Empty = all columns.

## `renameFields` (type: `object`):

{"old name": "new name"}, applied last. Japanese column names are fine, e.g. {"price": "価格"}.

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

xlsx = Excel workbook (header row frozen, auto filter, column widths). csv-excel = CSV that opens in Excel without garbled Japanese (UTF-8 with BOM). csv-sjis = Shift_JIS CSV for older Japanese systems. csv / tsv / json / jsonl = plain UTF-8.

## `fileName` (type: `string`):

Base name of the output files (letters a–z, digits, - and \_). Also used as the Excel sheet name, which may be Japanese.

## `excelNumbers` (type: `boolean`):

Text like "1,200" becomes the number 1200 in the .xlsx file. Values with leading zeros (zip codes, phone numbers) stay text.

## `pushToDataset` (type: `boolean`):

Lets you use Apify's own exports and integrations on the cleaned rows.

## `maxInputRows` (type: `integer`):

Rows read from all inputs together; the rest is ignored.

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

Larger input files are rejected.

## Actor input object example

```json
{
  "data": [
    {
      "name": "ＡＢＣ商店",
      "price": "1,200",
      "tags": [
        "食品",
        "セール"
      ],
      "address": {
        "pref": "東京都",
        "city": "千代田区"
      },
      "updated": "2026年10月7日"
    },
    {
      "name": "XYZ Store",
      "price": "980",
      "tags": [
        "雑貨"
      ],
      "address": {
        "pref": "大阪府",
        "city": "大阪市"
      },
      "updated": "2026/09/30"
    },
    {
      "name": "ABC商店",
      "price": "1,200",
      "tags": [
        "食品",
        "セール"
      ],
      "address": {
        "pref": "東京都",
        "city": "千代田区"
      },
      "updated": "2026-10-07"
    },
    {
      "name": "ﾃｽﾄ㈱ 😀",
      "price": "15000",
      "tags": [],
      "address": {
        "pref": "北海道",
        "city": "札幌市"
      },
      "updated": "2026-10-01"
    }
  ],
  "fileEncoding": "auto",
  "csvDelimiter": "auto",
  "flattenNested": true,
  "arrayMode": "join",
  "trimSpaces": true,
  "normalizeWidth": false,
  "collapseSpaces": false,
  "emptyToNull": false,
  "filters": [],
  "filterMode": "all",
  "caseSensitive": false,
  "dedupeBy": [
    "name"
  ],
  "offset": 0,
  "limit": 0,
  "renameFields": {
    "address.pref": "都道府県"
  },
  "outputFormats": [
    "xlsx",
    "csv-excel"
  ],
  "fileName": "data",
  "excelNumbers": true,
  "pushToDataset": true,
  "maxInputRows": 200000,
  "maxFileSizeMb": 100
}
```

# Actor output Schema

## `files` (type: `string`):

Links to the Excel / CSV / JSON files.

## `rows` (type: `string`):

The cleaned rows (when "Also write rows to the run's dataset" is on).

## `summary` (type: `string`):

Row counts before/after filtering and deduplication, columns and files.

# 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 = {
    "data": [
        {
            "name": "ＡＢＣ商店",
            "price": "1,200",
            "tags": [
                "食品",
                "セール"
            ],
            "address": {
                "pref": "東京都",
                "city": "千代田区"
            },
            "updated": "2026年10月7日"
        },
        {
            "name": "XYZ Store",
            "price": "980",
            "tags": [
                "雑貨"
            ],
            "address": {
                "pref": "大阪府",
                "city": "大阪市"
            },
            "updated": "2026/09/30"
        },
        {
            "name": "ABC商店",
            "price": "1,200",
            "tags": [
                "食品",
                "セール"
            ],
            "address": {
                "pref": "東京都",
                "city": "千代田区"
            },
            "updated": "2026-10-07"
        },
        {
            "name": "ﾃｽﾄ㈱ 😀",
            "price": "15000",
            "tags": [],
            "address": {
                "pref": "北海道",
                "city": "札幌市"
            },
            "updated": "2026-10-01"
        }
    ],
    "filters": [],
    "dedupeBy": [
        "name"
    ],
    "renameFields": {
        "address.pref": "都道府県"
    },
    "outputFormats": [
        "xlsx",
        "csv-excel"
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("masahishop/dataset-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 = {
    "data": [
        {
            "name": "ＡＢＣ商店",
            "price": "1,200",
            "tags": [
                "食品",
                "セール",
            ],
            "address": {
                "pref": "東京都",
                "city": "千代田区",
            },
            "updated": "2026年10月7日",
        },
        {
            "name": "XYZ Store",
            "price": "980",
            "tags": ["雑貨"],
            "address": {
                "pref": "大阪府",
                "city": "大阪市",
            },
            "updated": "2026/09/30",
        },
        {
            "name": "ABC商店",
            "price": "1,200",
            "tags": [
                "食品",
                "セール",
            ],
            "address": {
                "pref": "東京都",
                "city": "千代田区",
            },
            "updated": "2026-10-07",
        },
        {
            "name": "ﾃｽﾄ㈱ 😀",
            "price": "15000",
            "tags": [],
            "address": {
                "pref": "北海道",
                "city": "札幌市",
            },
            "updated": "2026-10-01",
        },
    ],
    "filters": [],
    "dedupeBy": ["name"],
    "renameFields": { "address.pref": "都道府県" },
    "outputFormats": [
        "xlsx",
        "csv-excel",
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("masahishop/dataset-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 '{
  "data": [
    {
      "name": "ＡＢＣ商店",
      "price": "1,200",
      "tags": [
        "食品",
        "セール"
      ],
      "address": {
        "pref": "東京都",
        "city": "千代田区"
      },
      "updated": "2026年10月7日"
    },
    {
      "name": "XYZ Store",
      "price": "980",
      "tags": [
        "雑貨"
      ],
      "address": {
        "pref": "大阪府",
        "city": "大阪市"
      },
      "updated": "2026/09/30"
    },
    {
      "name": "ABC商店",
      "price": "1,200",
      "tags": [
        "食品",
        "セール"
      ],
      "address": {
        "pref": "東京都",
        "city": "千代田区"
      },
      "updated": "2026-10-07"
    },
    {
      "name": "ﾃｽﾄ㈱ 😀",
      "price": "15000",
      "tags": [],
      "address": {
        "pref": "北海道",
        "city": "札幌市"
      },
      "updated": "2026-10-01"
    }
  ],
  "filters": [],
  "dedupeBy": [
    "name"
  ],
  "renameFields": {
    "address.pref": "都道府県"
  },
  "outputFormats": [
    "xlsx",
    "csv-excel"
  ]
}' |
apify call masahishop/dataset-cleaner --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,masahishop/dataset-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/P2rz2Y16dAdYzeb1n/builds/HNE3WHreiikDannK2/openapi.json