# CSV and Excel Merge, Dedupe and Clean Files (`daddyd-prints/csv-excel-merge-dedupe-clean`) Actor

Merge CSV, TSV, JSON, JSON Lines and Excel XLSX files into one clean table. Trims spaces, fixes Unicode, rewrites dates as ISO, drops empty rows and removes duplicates.

- **URL**: https://apify.com/daddyd-prints/csv-excel-merge-dedupe-clean.md
- **Developed by:** [Derek Derringer](https://apify.com/daddyd-prints) (community)
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.20 / 1,000 results

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 and Excel Merge, Dedupe and Clean Files

### What it does

This Actor merges pasted CSV text and CSV, TSV, JSON, JSON Lines and Excel XLSX files into one table. It trims and collapses spaces and normalises Unicode in every cell, rewrites the date columns you name as ISO dates, drops empty rows and removes duplicates. Each cleaned row goes to your run's dataset. REPORT counts what was dropped and removed, and SKIPPED lists every file or line it could not read, with the reason. It suits product lists, inventory sheets, order exports and reports.

### How to use it

1. Open the Actor in Apify Console.
2. The form opens with an invented sample in **Paste CSV text**. Replace it with your own text or clear it, and add file links or uploads in **Files to merge** if you have files.
3. Pick the options, and name your date columns in **Date columns to change to ISO format**.
4. Click **Start**.
5. When the run ends, open the **Output** tab and export the cleaned rows as CSV, JSON or Excel. REPORT and SKIPPED are in the run's key-value store.

### Input

| Field | What it does | Default | Limits |
| --- | --- | --- | --- |
| Paste CSV text (`csvText`) | A table pasted as text, with a header row. It is read before any file. | None. Prefilled with a 10-row invented product sample | Comma, semicolon, tab or pipe between values |
| Files to merge (`fileUrls`) | Files to upload, or the web addresses of files. They are read one at a time, in this order, after the pasted text. | None | CSV, TSV, JSON, JSON Lines and Excel XLSX. robots.txt rules apply. An address listed twice is read once |
| File format (`fileFormat`) | Forces one format for every file. Auto works out each file's format from its content, name and type. | Auto | Auto, CSV, TSV, JSON, JSON Lines, Excel XLSX. Files only, not pasted text |
| CSV delimiter (`delimiter`) | The character between values. Auto checks comma, semicolon, tab and pipe and picks the one that splits the first 40 rows most evenly. | Auto | Auto, Comma, Semicolon, Tab, Pipe |
| First row is a header (`hasHeaderRow`) | On: the first row that is not blank holds the column names. Off: columns are named column_1, column_2 and so on. | On | CSV, TSV, pasted text and XLSX. JSON uses its own keys |
| Excel sheet name (`sheetName`) | The XLSX sheet to read, matched ignoring case. | Empty, which reads the first sheet | A file without that sheet is skipped, and SKIPPED lists the sheets it has |
| Read every Excel sheet (`allSheets`) | Reads every sheet of each XLSX file and merges them. | Off | When on, Excel sheet name is not used |
| Trim spaces at the start and end of cells (`trimWhitespace`) | Removes spaces, tabs, line breaks, non-breaking and zero-width spaces at both ends of each value. | On | |
| Collapse repeated spaces (`collapseSpaces`) | Turns runs of spaces, tabs and non-breaking spaces inside a value into one space. | On | Line breaks inside a cell are kept |
| Normalise Unicode (NFC) (`unicodeNormalize`) | Writes accented letters the same way every time, so text that looks the same also matches. | On | |
| Drop empty rows (`dropEmptyRows`) | Leaves out rows where every value is empty after cleaning. | On | |
| Remove duplicate rows (`removeDuplicates`) | Keeps one row from each group of duplicates, across all sources. Off: merge and clean only. | On | |
| Columns that decide a duplicate (`dedupeColumns`) | Rows count as duplicates when these columns match. Names are matched ignoring case and extra spaces. | Empty, which compares every column | With duplicates on, a name that is not found stops the run before any row is written, and REPORT lists the columns in your data |
| Which duplicate to keep (`keepDuplicate`) | First seen keeps the earliest row. Last seen keeps the latest, useful when later files hold newer data. | First seen | First seen, Last seen |
| Ignore letter case when comparing (`ignoreCaseForDuplicates`) | On: Ceramic Mug and ceramic mug count as the same. The kept row keeps its own case. | On | Affects the comparison only |
| Date columns to change to ISO format (`dateColumns`) | Rewrites values as YYYY-MM-DD, with the time added when there is one. Values it cannot read are left as they were and counted in REPORT. | Empty. Prefilled with Last Restocked | A name that is not found is noted in REPORT warnings |
| Day and month order (`dateOrder`) | For number dates such as 03/04/2026. Auto decides per column from values that can only be read one way, such as 25/03/2026. | Auto (decide per column) | Auto, Day first, Month first. If Auto finds no clear answer, unclear values are left as they were |
| Maximum rows to read (`maxRows`) | Stops reading after this many rows across all sources. | 50,000 | 1 to 1,000,000 |
| Maximum file size (`maxFileSizeMb`) | Larger files are skipped and listed in SKIPPED. | 50 MB | 1 to 200 MB |
| Server wait timeout (`requestTimeoutSecs`) | How long to wait on a file's server at each step (connecting, sending, each wait for the next part) before the file is skipped as timed out. It is not a limit on the whole download. | 60 seconds | 5 to 600 seconds |

### Output

Each cleaned row is one dataset item, with your merged table's columns as its fields, so the fields change from run to run. Rows keep the order they were read in, and a row missing a column gets an empty value there.

Four of the ten rows in the prefilled sample, from a local test run:

```
SKU,Product Name,Category,Price,Stock,Last Restocked
A-100,  Ceramic Mug  ,Kitchen,12.50,40,2026-03-02
A-100,ceramic   mug,KITCHEN,12.50,40,03/02/2026
B-210,Linen Tea Towel,Kitchen,8.00,120,02/14/2026
B-210,Linen Tea Towel ,Kitchen,8.00,120,"Feb 14, 2026"
```

became two dataset items:

```json
{"SKU": "A-100", "Product Name": "Ceramic Mug", "Category": "Kitchen", "Price": "12.50", "Stock": "40", "Last Restocked": "2026-03-02"}
{"SKU": "B-210", "Product Name": "Linen Tea Towel", "Category": "Kitchen", "Price": "8.00", "Stock": "120", "Last Restocked": "2026-02-14"}
```

The full sample gave "6 rows written. 10 rows read, 1 empty row dropped, 3 duplicates removed, 0 sources or lines skipped."

### Pricing

This Actor is priced per event, with two charges:

- **Actor start:** Apify's automatic start event, at Apify's default price.
- **Output row:** one charge for each cleaned row written to the dataset.

**Price per output row: $0.20 per 1,000 output rows, which is $0.0002 each.**

Removed duplicates, dropped empty rows, skipped files and lines, rows past **Maximum rows to read**, REPORT and SKIPPED are never charged.

If you set a maximum cost for the run and it is reached before every row is written, the Actor writes no more rows, saves REPORT with the status `STOPPED_SPENDING_LIMIT` and the counts of rows written and not written, and ends the run without an error. Unwritten rows are not charged. A new run starts from the first row, so rows already written are charged again.

### Your data

The Actor reads only the text you paste, the files you list and, for file links, each site's robots.txt. It writes the cleaned rows to your run's dataset and REPORT and SKIPPED to its key-value store, and sends your data nowhere else. It adds no data and looks nothing up: every output value comes from your input, cleaned as you chose. Query strings in web addresses are shown as "(query hidden)" in the log, REPORT and SKIPPED, in case they hold a token. Your run stays in your own Apify account, and how long Apify keeps it depends on your plan.

### File links and robots.txt

Every file link gets a robots.txt check, always on. Before any other request to a site, the Actor reads its robots.txt and follows the group for `csv-excel-merge-dedupe-clean`, otherwise the `*` group, keeping it for the run and never over 24 hours. A redirect to a new site gets that site's check first. Files are read one at a time, so a site never gets two requests at once. Every skip below is listed in SKIPPED with the reason, is not retried and is not charged.

| Case | What the Actor does |
| --- | --- |
| robots.txt disallows the file, or a redirect leads into a disallowed path | Never requests it. Skips it |
| `Disallow: /` with no Allow rule | Skips every file on that site |
| robots.txt answers 401, 403, 429 or a server error, or cannot be reached | Skips every file on that site |
| robots.txt answers any other 4xx, such as 404 | Treats the site as having no rules and reads the file |
| The file answers 401, 403 or 429 | Skips it |
| The answer is a login page, a CAPTCHA page or another web page | Skips it, and never tries to get past a login or CAPTCHA |
| robots.txt sets a Crawl-delay | Waits that long between requests to the site, counted from the end of the last one |
| The Crawl-delay is over 60 seconds | Skips every file on that site |

The user agent starts with `csv-excel-merge-dedupe-clean/1.0`. There is no cookie, header, login, proxy or user agent input. On Apify, private and local network addresses are refused. Files you upload in Console are read from their Apify storage address and get the same robots.txt check as any other link.

### Your responsibility

You are responsible for having the right to use the files and data you give this Actor and, for file links, for following each website's terms of use. The Actor checks robots.txt for you, but it cannot read or judge a site's terms.

### Run records

Two records in the run's key-value store, never charged:

| Record | What it holds |
| --- | --- |
| REPORT | The status, one of `SUCCEEDED`, `SUCCEEDED_NO_ROWS`, `NO_INPUT`, `STOPPED_INPUT_ERROR`, `STOPPED_SPENDING_LIMIT`, `ABORTED` or `FAILED`, with a short message. Start and finish times and timings. Rows read, empty rows dropped, duplicates removed, rows out, rows written and rows not written because of a spending limit, and whether the row or spending limit was reached. The columns, and the columns used for the duplicate check. For each date column, the order used and how many values were converted, already ISO, left as they were or empty, with up to 5 examples of each kind left as they were. For each source, its format, encoding, delimiter, sheets, size and counts. The number skipped, warnings, the settings used, the user agent and the robots.txt result for each site. If you abort a run, REPORT is saved first with the status `ABORTED`, and rows already in the dataset are the only rows charged |
| SKIPPED | Every file or line not read: the source, a reason code such as `robots_txt_disallowed`, `robots_txt_disallows_all`, `login_page`, `captcha_page`, `access_denied`, `rate_limited`, `timeout`, `too_large`, `unsupported_format`, `sheet_not_found`, `bad_json_line`, `parse_error` or `malformed_url`, a detail, and the HTTP status where known. Up to 1,000 records, then one line with the count of the rest |

### Limits

| Limit | Value |
| --- | --- |
| Rows per run | Maximum rows to read, 1 to 1,000,000 across all sources, 50,000 by default. Rows past it are not read or charged, and file links after it are listed in SKIPPED without being downloaded |
| File size | Maximum file size, 1 to 200 MB per file, 50 MB by default. An XLSX workbook that unpacks to more than 20 times that size is skipped too |
| Supported files | CSV, TSV, JSON (a list of rows, or an object holding exactly one list of rows), JSON Lines and Excel XLSX. Text files are read as UTF-8 (with or without BOM) or UTF-16 with BOM, otherwise as Windows-1252, then Latin-1. REPORT shows which |
| Not supported | Old Excel XLS, OpenDocument ODS, Apple Numbers, ZIP files that are not XLSX, PDF, images, other binary files and web pages. Each is skipped with the reason |
| Damaged files | A file that breaks part way through, such as a cut-off XLSX sheet or JSON nested too deep, is skipped whole as `parse_error`. None of its rows are kept, so the output never holds part of a file. With Read every Excel sheet on, one damaged sheet skips the whole workbook |
| Merging | Columns are matched across files ignoring letter case and extra spaces, and the first spelling seen is kept. A repeated header such as Name becomes Name_2, blank rows above the header are skipped, and columns with no header and no values are dropped |
| Excel | Formula cells give the value saved in the file. Formulas are not recalculated. Date cells come out as ISO dates, with the time when there is one |
| JSON | Nested values become compact JSON text in one cell, and null becomes empty |
| Value types | CSV, TSV and pasted text give text. Numbers from Excel and JSON stay numbers |
| Duplicate matching | Values are compared as text, so 12.50 and 12.5 differ and 007 stays apart from 7 |
| Columns | Apify's CSV and Excel exports take up to 2,000 columns. JSON export keeps them all. REPORT warns when a table is wider |
| Links | http and https only, no user name or password in the address, at most 5 redirects. An address with an ASCII control character such as a tab or DEL, or with a hidden character or letters not allowed in the host name, is skipped as `malformed_url` before any request. A hidden character elsewhere in the address, such as a zero-width space pasted at the end, is sent as part of the address, and the site then usually answers 404 (`http_error`). robots.txt requests wait at most 30 seconds per step |
| Memory | 512 MB by default. A very large run can be given up to 4,096 MB in the run options. Above 1 GB, Apify charges the start event once more for each extra GB |

# Actor input Schema

## `csvText` (type: `string`):

Paste a table as CSV, TSV or semicolon separated text, with a header row. Leave it empty if you only use files. The sample shown here is invented product data.

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

Upload files or give their web addresses. Supported: CSV, TSV, JSON (a list of rows), JSON Lines and Excel XLSX. Files are read one at a time, in this order, after the pasted text. Before it reads a web address, the Actor reads that site's robots.txt and skips any file the site does not allow automated tools to read. Skipped files are listed in SKIPPED and are never charged.

## `fileFormat` (type: `string`):

Auto works out the format of each file from its content, name and type. Choose a format only if auto guesses wrong.

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

The character between values in CSV text. Auto checks comma, semicolon, tab and pipe and picks the one that splits the rows evenly.

## `hasHeaderRow` (type: `boolean`):

On: the first row that is not blank holds the column names. Off: columns are named column_1, column_2 and so on.

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

For XLSX files, the sheet to read. Leave it empty to read the first sheet.

## `allSheets` (type: `boolean`):

For XLSX files, read every sheet and merge them all into the table.

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

Removes spaces, tabs and non breaking spaces before and after each value.

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

Turns runs of spaces, tabs and non breaking spaces inside a value into one space. Line breaks inside a cell are kept.

## `unicodeNormalize` (type: `boolean`):

Writes letters with accents the same way every time, so text that looks the same also matches.

## `dropEmptyRows` (type: `boolean`):

Leaves out rows where every value is empty after cleaning.

## `removeDuplicates` (type: `boolean`):

Keeps one row from each group of duplicates. Turn it off to merge and clean only.

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

Rows count as duplicates when these columns match. Leave it empty to compare every column. Column names are matched ignoring case and extra spaces. If a name is not found, nothing is written and the REPORT lists the columns that exist.

## `keepDuplicate` (type: `string`):

First keeps the earliest row. Last keeps the latest row, which is useful when later files hold newer data.

## `ignoreCaseForDuplicates` (type: `boolean`):

On: Ceramic Mug and ceramic mug count as the same. The kept row keeps its own letter case.

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

Values in these columns are rewritten as YYYY-MM-DD (with the time added when there is one). Values that cannot be read as a date are left as they were and counted in the REPORT.

## `dateOrder` (type: `string`):

For dates written with numbers, such as 03/04/2026. Auto decides per column from values that can only be read one way, such as 25/03/2026. When a column gives no answer, unclear values are left as they were.

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

Stops reading after this many rows across all sources. Rows after the limit are not read and not charged. If a very large run runs out of memory, give it more memory in the run options.

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

Files larger than this are skipped and listed in SKIPPED.

## `requestTimeoutSecs` (type: `integer`):

How long to wait on the file's server at each step (connecting, sending the request, and each wait for the next part of the file) before the file is skipped as timed out. It is not a limit on the whole download, so a slow but steady file can take longer in total. The robots.txt check waits at most 30 seconds per step.

## Actor input object example

```json
{
  "csvText": "SKU,Product Name,Category,Price,Stock,Last Restocked\nA-100,  Ceramic Mug  ,Kitchen,12.50,40,2026-03-02\nA-100,ceramic   mug,KITCHEN,12.50,40,03/02/2026\nB-210,Linen Tea Towel,Kitchen,8.00,120,02/14/2026\nB-210,Linen Tea Towel ,Kitchen,8.00,120,\"Feb 14, 2026\"\nC-305,Café Press,Coffee,24.00,15,2026-01-20\nC-305,Café Press,Coffee,24.00,15,20 Jan 2026\n,,,,,\nD-410,Bamboo Cutting Board,Kitchen,18.75,32,2026-03-09\nE-520,Glass Storage Jar,Pantry,6.40,200,03/11/2026\nF-630,Cast Iron Skillet,Cookware,39.99,12,2026-02-27\n",
  "fileFormat": "auto",
  "delimiter": "auto",
  "hasHeaderRow": true,
  "allSheets": false,
  "trimWhitespace": true,
  "collapseSpaces": true,
  "unicodeNormalize": true,
  "dropEmptyRows": true,
  "removeDuplicates": true,
  "dedupeColumns": [],
  "keepDuplicate": "first",
  "ignoreCaseForDuplicates": true,
  "dateColumns": [
    "Last Restocked"
  ],
  "dateOrder": "auto",
  "maxRows": 50000,
  "maxFileSizeMb": 50,
  "requestTimeoutSecs": 60
}
```

# Actor output Schema

## `results` (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 = {
    "csvText": `SKU,Product Name,Category,Price,Stock,Last Restocked
A-100,  Ceramic Mug  ,Kitchen,12.50,40,2026-03-02
A-100,ceramic   mug,KITCHEN,12.50,40,03/02/2026
B-210,Linen Tea Towel,Kitchen,8.00,120,02/14/2026
B-210,Linen Tea Towel ,Kitchen,8.00,120,"Feb 14, 2026"
C-305,Café Press,Coffee,24.00,15,2026-01-20
C-305,Café Press,Coffee,24.00,15,20 Jan 2026
,,,,,
D-410,Bamboo Cutting Board,Kitchen,18.75,32,2026-03-09
E-520,Glass Storage Jar,Pantry,6.40,200,03/11/2026
F-630,Cast Iron Skillet,Cookware,39.99,12,2026-02-27`,
    "dedupeColumns": [],
    "dateColumns": [
        "Last Restocked"
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("daddyd-prints/csv-excel-merge-dedupe-clean").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 = {
    "csvText": """SKU,Product Name,Category,Price,Stock,Last Restocked
A-100,  Ceramic Mug  ,Kitchen,12.50,40,2026-03-02
A-100,ceramic   mug,KITCHEN,12.50,40,03/02/2026
B-210,Linen Tea Towel,Kitchen,8.00,120,02/14/2026
B-210,Linen Tea Towel ,Kitchen,8.00,120,\"Feb 14, 2026\"
C-305,Café Press,Coffee,24.00,15,2026-01-20
C-305,Café Press,Coffee,24.00,15,20 Jan 2026
,,,,,
D-410,Bamboo Cutting Board,Kitchen,18.75,32,2026-03-09
E-520,Glass Storage Jar,Pantry,6.40,200,03/11/2026
F-630,Cast Iron Skillet,Cookware,39.99,12,2026-02-27
""",
    "dedupeColumns": [],
    "dateColumns": ["Last Restocked"],
}

# Run the Actor and wait for it to finish
run = client.actor("daddyd-prints/csv-excel-merge-dedupe-clean").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 '{
  "csvText": "SKU,Product Name,Category,Price,Stock,Last Restocked\\nA-100,  Ceramic Mug  ,Kitchen,12.50,40,2026-03-02\\nA-100,ceramic   mug,KITCHEN,12.50,40,03/02/2026\\nB-210,Linen Tea Towel,Kitchen,8.00,120,02/14/2026\\nB-210,Linen Tea Towel ,Kitchen,8.00,120,\\"Feb 14, 2026\\"\\nC-305,Café Press,Coffee,24.00,15,2026-01-20\\nC-305,Café Press,Coffee,24.00,15,20 Jan 2026\\n,,,,,\\nD-410,Bamboo Cutting Board,Kitchen,18.75,32,2026-03-09\\nE-520,Glass Storage Jar,Pantry,6.40,200,03/11/2026\\nF-630,Cast Iron Skillet,Cookware,39.99,12,2026-02-27\\n",
  "dedupeColumns": [],
  "dateColumns": [
    "Last Restocked"
  ]
}' |
apify call daddyd-prints/csv-excel-merge-dedupe-clean --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,daddyd-prints/csv-excel-merge-dedupe-clean"
        }
    }
}
```

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/PH5nrhQHQue5XdjiI/builds/qZH9XHNAADMzxsOUo/openapi.json