Merge, Dedupe & Transform Datasets: CSV, JSON, Excel

Combine, clean and reshape your own data: Apify datasets and CSV, JSON, JSONL or Excel files go in; merged, deduplicated, filtered rows come out in the dataset and, if you want, as a CSV, JSON, JSONL or XLSX file with a download link. Keyless, no browser, no proxy.

Price: $0.40 per 1,000 output rows ($0.0004 per row). You pay for rows saved. Rows that are read and then dropped (duplicates, filtered out, unmatched in a join) are free, and so is reading. No extra fee for file export. Apify's default start event applies as for every Actor.

• Sources: one or more dataset IDs, CSV/JSON/JSONL/XLSX file URLs (up to 200 MB each) and uploaded files, mixed freely.
• Operations, in a fixed order: merge (concatenate, or inner/left join on a key), dedupe (first or last wins, optional case and whitespace normalising), filter, flatten, drop/select/rename fields, sort, limit.
• Streaming: rows are read in pages. Dedupe keeps only a small hash per distinct key (about 100 bytes), so 4 million distinct keys fit in 1 GB (measured, see Limits). Up to 5 million input rows per run.
• Output: rows to the run's dataset, and/or a file in the key-value store (CSV, JSON, JSONL or Excel). Every row is charged once, wherever it is saved.
• Quick start: press Start. The prefilled input downloads a small public CSV (World Bank GDP by country and year, 13,979 rows), keeps the first row per country code and returns 20 rows for $0.008.

Reading your own datasets works with the Actor's default Limited permissions. Pick the datasets in the input form's dataset picker: choosing a dataset gives that run read access to it (the input field is declared as a dataset resource with READ permission). The Actor never asks for, receives or stores an API token. File URLs and uploads need no dataset access at all.

What it does

1. Reads each source in order: datasets first (in the order you list them), then file URLs, then uploaded files. Datasets are read through the Apify API in pages of 1,000 rows. Files are downloaded to the run's temporary disk, then parsed as a stream.
2. Merges. Concatenate appends all sources (fields that differ simply become different fields). A join takes exactly two sources: the first is the left side and streams through; the second is the right side and is held in memory (at most 1,000,000 rows and about 40% of the run's memory). Matching right rows are added to each left row; a right field with the same name as a left field gets the suffix _right; several matches give several rows.
3. Dedupes on the fields you choose (dot paths such as owner.id work, and * compares whole rows). Keep the first or the last occurrence. Normalise keys compares after trimming, collapsing inner spaces and lower-casing; your output values are never changed.
4. Filters with a list of simple conditions that must all hold.
5. Flattens nested objects to dot keys ({"a": {"b": 1}} becomes {"a.b": 1}), if you turn it on.
6. Drops, selects and renames fields.
7. Sorts by one field and limits the number of rows.

The order matters: dedupe happens before filtering (a row that is later filtered out can still be the "first" of its group), flatten comes before the field operations (so you can select owner.name), and the limit applies after sorting.

Input

Field	What it does
Apify dataset IDs or names (datasetIds)	Datasets in your account, one per line: the ID or username/dataset-name.
File URLs (fileUrls)	Direct http(s) links to CSV, JSON, JSONL or XLSX files, one per line. Format is detected from the content. Default: a public sample CSV.
Uploaded files (files)	Upload from your computer, or paste a key-value store record URL.
Excel sheet (xlsxSheet)	Sheet number (1 = first) or exact name. The first row is the header.
If a source cannot be read (onSourceError)	fail (default): stop before any output, nothing charged. skip: continue without it and list it in RUN_SUMMARY.
Merge mode (mergeMode), Join key (joinKey), Join key on the right side (joinRightKey)	concat, join-inner or join-left. A join needs exactly 2 sources.
Add source field (sourceField)	Adds a field holding where each row came from.
Dedupe on fields (dedupeFields), Keep (dedupeKeep), Normalise keys (normalizeKeys)	See above. Empty = no dedupe.
Filters (filters)	JSON list: [{"field": "price", "op": "gt", "value": 10}]. Ops: equals, notEquals, contains, notContains, regex, exists, notExists, gt, gte, lt, lte. Optional "ignoreCase": true.
Flatten (flatten), Drop fields (dropFields), Keep only these fields (selectFields), Rename fields (renameFields)	selectFields uses the original names and sets the output order; renameFields is {"old": "new"} and is applied last.
Sort by field (sortField), Sort order (sortOrder)	Uses the output field name (after renaming). Numbers sort numerically; empty values go last.
Max output rows (maxOutputRows)	0 = no limit. The prefilled 20 is only for the quick start; set 0 for everything.
Max input rows (maxInputRows)	Stop reading after this many rows in total. Hard maximum 5,000,000.
Save rows to the dataset (outputDataset), Also save a file (outputFile)	none, csv, json, jsonl, xlsx. The file is output.<ext> in the run's key-value store; its URL is in RUN_SUMMARY under file.url.
Neutralise spreadsheet formulas (neutralizeFormulas)	Prefixes cells starting with = + - @ with an apostrophe in CSV/XLSX files. Off by default because it changes values.
Text encoding (encoding)	utf-8 (default), windows-1252 (Latin-1, Excel's Western European CSV export) or utf-16le, for CSV, JSON and JSONL files. A UTF-8 file with invalid bytes is read anyway (bad bytes become U+FFFD), and a warning in the log and in RUN_SUMMARY.warnings suggests windows-1252.
On field name clash (onKeyCollision)	If flattening or renaming gives two fields of a row the same name: skip (default) leaves that row out and counts it in RUN_SUMMARY.keyCollisions (not charged); overwrite keeps the row, the later field wins.
Max size per file (maxFileMb), Download timeout (downloadTimeoutSecs)	Default 200 MB (hard cap; 50 MB for .xlsx) and 300 s.

How filters compare: equals and notEquals compare numbers numerically when both sides are numbers (also in CSV text) and otherwise as exact text (case-sensitive unless ignoreCase). gt, gte, lt, lte compare numerically when both sides are numbers, otherwise as text, which orders ISO dates (2024-06-01) correctly. contains is a substring test. regex runs on V8's linear-time regular expression engine, so no pattern can take exponential time (^(a|a)*$ finishes at once). It is case-sensitive (ignoreCase is refused with regex; write [Aa]), at most 200 characters, tests at most the first 20,000 characters of a value, and patterns that cannot run in linear time (backreferences such as (a)\1, lookahead such as (?=a)) are refused with a message. Nested fields: "field": "owner.name".

CSV details: the delimiter (, ; tab or |) is detected from the header line, a UTF-8 BOM is removed, duplicate or empty header names become name_2 and column_3, and every value is text (a filter such as gt still compares numbers numerically). Dates in Excel cells come out as ISO text. Formulas come out as their last calculated value.

Example inputs

1. Merge two scrapes, keep the newest row per URL, export CSV:

{
  "datasetIds": ["WkzbQMuFYuamGv3YF", "Xp3kLmN8vQrT2aBcD"],
  "dedupeFields": ["url"],
  "dedupeKeep": "last",
  "normalizeKeys": true,
  "outputFile": "csv"
}

2. Join a dataset with a price list from a CSV file, only matching rows:

{
  "datasetIds": ["WkzbQMuFYuamGv3YF"],
  "fileUrls": ["https://example.com/price-list.csv"],
  "mergeMode": "join-inner",
  "joinKey": "sku",
  "selectFields": ["sku", "title", "price"]
}

3. Filter, flatten and export Excel only (no dataset rows):

{
  "datasetIds": ["WkzbQMuFYuamGv3YF"],
  "filters": [
    { "field": "rating", "op": "gte", "value": 4 },
    { "field": "seller.country", "op": "equals", "value": "DE" }
  ],
  "flatten": true,
  "renameFields": { "seller.name": "seller" },
  "sortField": "rating",
  "sortOrder": "desc",
  "outputDataset": false,
  "outputFile": "xlsx"
}

Output

Dataset items are your rows, as they come out of the pipeline. With the default input, one item looks like:

{ "Country Name": "Afghanistan", "Country Code": "AFG", "Year": "2000", "Value": "3521418059.923445" }

RUN_SUMMARY (key-value store) records what happened:

{
  "sourcesRequested": 1, "sourcesUsed": 1, "sourceFailures": [],
  "inputRows": 989, "duplicates": 967, "filteredOut": 0, "unmatchedLeft": 0, "unmatchedRight": 0,
  "outputRows": 20, "charged": { "output-row": 20 },
  "file": { "key": "output.csv", "url": "https://api.apify.com/v2/key-value-stores/.../records/output.csv", "bytes": 1234, "rows": 20, "format": "csv" },
  "bytesDownloaded": 562767, "stoppedBy": "limit", "durationSecs": 0.7, "peakRssMb": 119
}

stoppedBy is finished, limit (Max output rows), max-charge (the run's maximum cost), input-row-cap, unpaid-work-cap (too many failed sources, 100 rows that were too large, refused by the dataset or unreadable, two refused groups in a row, or a join that produced more than 10 million row combinations) or error. When a run stops early at the limit, the counts cover the rows read up to that point.

If a source fails in fail mode, the dataset gets one error row per failed source (status: "error", source, errorCode, message), the run is marked failed, and nothing is charged.

Error codes

Code	Meaning
invalid-url, blocked	Not an http(s) URL, or a private, local or internal address (SSRF guard, re-checked on every redirect).
platform	A video, social or Google link. This Actor reads direct links to data files only.
dns, network, timeout, redirects	Host did not resolve or connect, stopped sending for 60 s, took longer than the timeout, or redirected more than 5 times.
http	The server answered something other than 200 (for example 404, 403, 429). Blocks and rate limits are reported, never worked around.
not-data	The link returned a web page, not a data file.
too-large	Over the size limit (200 MB, or 50 MB for .xlsx).
parse, unsupported-format	The file is malformed (the message names the line where it can), or is an old .xls (save as .xlsx or CSV).
item-rejected, parse (error rows in the output dataset)	A single row the dataset refused (item-rejected) or a dataset item nested too deep to read (parse). Free; the run goes on.
dataset-not-found, dataset-access, dataset-error	No such dataset in your account; the run may not read it (pick it with the dataset picker so the run gets read access, and check it is your own); other API error.
join-sources, join-too-large, sort-too-large	Run-level errors: a join needs exactly 2 sources; the right side or the sort buffer does not fit in memory. These stop the run before anything is charged.
too-many-columns	More than 5,000 different fields for a CSV/Excel file. The run stops with an error and no file is made. With a file-only output (outputDataset: false) nothing is charged. With dataset output, rows already saved in the dataset before the 5,001st field appeared stay saved and are charged (never more than were saved); RUN_SUMMARY.incomplete is true. Use Keep only these fields or a JSON output.

Pricing

Event	Price	Charged
output-row	$0.0004	Per row saved (dataset or file; a row in both counts once), after it is saved.

Output rows	Cost
20 (default run)	$0.008
1,000	$0.40
10,000	$4
100,000	$40
1,000,000	$400

Reading, parsing, duplicates, filtered rows and unmatched join rows cost nothing. Failed sources cost nothing. Set Apify's Maximum cost per run to cap spending: the Actor stops cleanly at the cap (stoppedBy: max-charge) and never saves rows it cannot charge.

Limits

• 5,000,000 input rows per run (lower with maxInputRows). Later rows are not read and stoppedBy is input-row-cap.
• Files: 200 MB each (50 MB for .xlsx, which is unzipped while reading), up to 50 sources per run.
• Memory: 1,024 MB by default (512 to 8,192). Dedupe uses about 100 bytes per distinct key (measured: 4 million distinct keys, 430 MB peak with keep-first and 470 MB with keep-last). Keep last reads the sources twice. Join: the right side is held in memory, at most 1,000,000 rows and about 40% of the run memory. Sort: all rows are held in memory, at most 1,000,000 rows and about 40% of the run memory; the run stops with an error before saving anything if it does not fit. Use more memory or filter/dedupe first.
• CSV and Excel files hold scalars: objects and arrays are written as JSON text. Excel files start a new sheet after 1,000,000 rows and cut cells at 32,767 characters. CSV and Excel files are limited to 5,000 different fields.
• Dedupe key hashing uses a 64-bit hash; the chance of a false duplicate in a 5-million-key run is about 1 in 1.5 million.
• Failed sources: the run stops after 5 failed sources or 500 MB downloaded for failed files.
• Files are checked completely before any output. Every file is downloaded and parsed once (count only) while sources are prepared; a damaged file (a bad JSON line, a record over 8 MB, a corrupt workbook) therefore fails the run or, with onSourceError: skip, is skipped, before any row is saved or charged. The error names the file and, where possible, the line. The only read error that can still happen after output has started is a failing Apify API call while reading a dataset; rows saved by then stay saved and charged and RUN_SUMMARY.incomplete is true.
• Excel files are opened safely: the zip directory is read first, a workbook that would unzip to more than 500 MB, has more than 5,000 parts, a shared-strings table over 100 MB, a cell over 100,000 characters, a row that expands to more than 9 million characters (cells pointing at the same long shared text count every time), a sheet whose text expands to more than about 4 times the unzipped size, or a password is refused (too-large), and only the chosen sheet is streamed. The sheet is chosen by its 1-based tab position or exact name, as listed in the workbook (not by file name). Blank header cells become column_N. Cell dates are written as ISO text.
• Big records: one CSV record or JSONL line may be at most 8 MB. A row over Apify's 9 MB dataset item limit is left out of the dataset (not charged, counted in RUN_SUMMARY.rowsTooLarge); send such rows to a file output instead.
• Nesting: JSON and JSONL files nested deeper than 1,000 levels are refused as a parse error while the file is checked, before anything is saved or charged. A dataset item nested deeper than 1,000 levels is checked as it is read (datasets are read during the run, not beforehand): it is left out and replaced by one free error row (status: "error", errorCode: "parse"), and the run goes on. It counts toward the 100-row limit below.
• Rows that cannot be saved: the size check is exact for rows over about 1.4 MB. A row over the 9 MB item limit is left out (counted in RUN_SUMMARY.rowsTooLarge). A row the dataset refuses anyway is retried alone and, if it is refused again, replaced by one free error row (status: "error", errorCode: "item-rejected", also counted in rowsTooLarge). These error rows carry a runId field (the id of the run that wrote them), which is how a restarted run tells them from rows in your data that look the same. Rows are sent in groups that fit one upload chunk (about 9 MB), so a refused group never repeats rows that were already saved. The run stops (stoppedBy: unpaid-work-cap) after 100 rows that were too large, were refused by the dataset, or could not be read (nested too deep), counted together, or after two groups in a row that the dataset refused entirely: reading them is work nobody pays for.
• Join: at most 10 million left-by-right row combinations per join (counted before filters); beyond that the run stops with stoppedBy: unpaid-work-cap. A right-side field that would take a name already used (for example x becomes x_right but the left row already has x_right) is a name clash: the row is left out and counted in keyCollisions, or overwritten with On field name clash = overwrite.
• Restarts (migrations): rows are saved in batches of up to 500 (or every 30 s, or when a batch nears one upload chunk). The order is: save the batch, charge it, then write the PROGRESS record (rowsPushed, errorRows, rowsCharged) to the key-value store; while a refused batch is retried row by row, PROGRESS is written after every error row. If Apify moves the run to another server, the restarted run skips as many output rows as the larger of the dataset's item count and PROGRESS.rowsPushed (if the count is behind PROGRESS it waits up to 30 s for it to catch up), counts the error rows among them (from PROGRESS and by reading the dataset after the point PROGRESS describes), and charges only real rows that were saved but not yet charged, i.e. skipped rows minus error rows minus rows already charged. "Already charged" is the larger of PROGRESS.rowsCharged and the platform's own count of charged events passed to the container at start (APIFY_CHARGED_ACTOR_EVENT_COUNTS); the Actor assumes that count is current when the container starts and cannot check it. Output files are rebuilt from the first row. What this does not promise: if a restart happens after rows were saved but before PROGRESS was written and the dataset's item count is also behind, up to that many rows can be saved again and charged again; nothing else in the Actor repairs a platform count that is stale. A PROGRESS record from a different input together with rows already in the dataset makes the run fail (nothing is read or charged) instead of starting again on top of them; with an empty dataset it is ignored. If saving a batch fails for a reason that is not the row's fault (for example the dataset API is unavailable after retries), including at a migration, the run fails with a RUN_SUMMARY instead of carrying on without those rows. A file-only restart whose budget is already used up still rebuilds the file, at no extra charge, and RUN_SUMMARY.charged keeps the earlier figure.
• Hidden fields: dataset fields whose names start with # (debug data) are dropped when datasets are read.
• Delimiter detection reads the header line and ignores separators inside quotes.

Privacy and terms

• Sample data: the default CSV is the datasets/gdp table (World Bank, World Development Indicators, with OECD national accounts data), licensed CC BY 4.0, pinned to one version in the datasets repository on GitHub. Attribution: World Bank - World Development Indicators.
• You are responsible for having the right to process the datasets and files you give it, and for what you do with the output. The Actor does not crawl websites; it reads only the sources you list, with the user agent dataset-tools-actor/0.1 (https://apify.com/ataism/dataset-tools), and it respects the limits described above.
• Your data is processed in the run. Downloaded files live in the run's temporary disk and are deleted when the run ends. Logs contain counts and source URLs without query strings, never row contents. The Actor collects nothing beyond what your own input contains.
• Output files are stored in the run's key-value store under your account and follow your Apify data retention. The file link contains the store ID: share it only with people who should have the data.
• If your rows contain personal data, you remain responsible for handling it lawfully; dropping fields with Drop fields or Keep only these fields is a simple way to leave them out.

Use it via API, n8n or Make

cURL (returns the rows):

curl -X POST "https://api.apify.com/v2/acts/ataism~dataset-tools/run-sync-get-dataset-items?token=$APIFY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"datasetIds": ["WkzbQMuFYuamGv3YF"], "dedupeFields": ["url"]}'

JavaScript (apify-client):

import { ApifyClient } from 'apify-client';
const client = new ApifyClient({ token: process.env.APIFY_TOKEN });
const run = await client.actor('ataism/dataset-tools').call({
    datasetIds: ['WkzbQMuFYuamGv3YF'],
    dedupeFields: ['url'],
    outputFile: 'csv',
});
const { items } = await client.dataset(run.defaultDatasetId).listItems();
const summary = await client.keyValueStore(run.defaultKeyValueStoreId).getRecord('RUN_SUMMARY');

Python (apify-client):

from apify_client import ApifyClient
client = ApifyClient("<APIFY_TOKEN>")
run = client.actor("ataism/dataset-tools").call(run_input={
    "datasetIds": ["WkzbQMuFYuamGv3YF"],
    "dedupeFields": ["url"],
})
items = client.dataset(run["defaultDatasetId"]).list_items().items

n8n: add the Apify node, operation Run an Actor and get dataset, Actor ataism/dataset-tools, and paste the input JSON.

Make: add the Apify module Run an Actor (Actor ataism/dataset-tools, input JSON), then Get Dataset Items with the run's default dataset ID.