# Dataset Transformer: Filter, Dedupe, JSON to CSV (`humble-echidna/dataset-transform`) Actor Clean, deduplicate and transform data: an Apify dataset, pasted JSON or a CSV, JSON, JSONL or Excel XLSX file URL. Filter rows, remove duplicates by any field, sort, rename fields, flatten JSON. Get a dataset and an optional CSV or JSONL file: JSON to CSV, CSV to JSON, Excel to CSV or Excel to JSON. - **URL**: https://apify.com/humble-echidna/dataset-transform.md - **Developed by:** [Michael Costa](https://apify.com/humble-echidna) (community) - **Categories:** Developer tools, Automation, Integrations - **Stats:** 31 total users, 24 monthly users, 96.8% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $0.70 / 1,000 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 ### What does Dataset Transformer do? **Dataset Transformer** cleans up **Apify datasets and CSV, JSON or Excel files**: **filter** rows, **remove duplicates**, **sort**, **keep and rename fields**, and **flatten nested JSON**. Give it one of your datasets, a file link, or the rows themselves as pasted JSON; you get a new dataset, and optionally a CSV or JSONL file (**JSON to CSV**, **CSV to JSON** or **Excel to JSON** in one step). It's the clean-up step between a scraper and your spreadsheet, CRM or database: "only the jobs in Berlin, one row per URL, newest first, with these five columns". [Input fields](https://apify.com/humble-echidna/dataset-transform/input-schema) · [API](https://apify.com/humble-echidna/dataset-transform/api) · [Use it from Claude, ChatGPT or Cursor (MCP)](#can-i-use-dataset-transformer-from-an-ai-agent-mcp) **Jump to:** [Fields](#what-data-does-dataset-transformer-return) · [Price](#how-much-does-it-cost-to-filter-and-dedupe-a-dataset) · [How to use](#how-to-filter-dedupe-and-convert-a-dataset) · [Input](#input) · [Output](#output) · [AI agents (MCP)](#can-i-use-dataset-transformer-from-an-ai-agent-mcp) · [Limits](#limits) · [FAQ](#faq) **Try it in one click:** the input comes pre-filled with the US Treasury's official exchange rates (a public CSV of 174 currencies). The example keeps the currencies worth more than one US dollar, removes a repeated one, sorts by rate and keeps four renamed columns: 9 rows, about $0.009 (9 × $0.001, plus $0.00005 for the run start). **Then pick your own dataset or file.** Cleaning a scraper's results? See [scraper results to a clean CSV](#example-scraper-results-to-a-clean-csv). ### What data does Dataset Transformer return? Your own rows, with the fields you chose. From the example: | Field | Example | Notes | |---|---|---| | `country` | `Kuwait` | Kept as is. | | `currency` | `Dinar` | Kept as is. | | `unitsPerUsd` | `0.308` | Renamed from `exchange_rate` (`exchange_rate -> unitsPerUsd`). | | `date` | `2025-12-31` | Renamed from `record_date`. | Fields you asked for that a row doesn't have are `null`, so every row has the same columns. Nested fields can be flattened into plain columns (`address.city`). The run's `RUN_STATS` record counts the rows read, filtered out, duplicates, skipped and written. More under [Output](#output). ### How much does it cost to filter and dedupe a dataset? You pay per row written to the output: **$1.00 per 1,000 rows**, plus $0.00005 each time a run starts. **It's cheaper on paid Apify plans:** $0.90 per 1,000 rows on Starter, $0.80 on Scale and $0.70 on Business. The prices on this page are the Free-plan price, so on a paid plan you pay less than the examples show. - **The example below:** 9 rows × $0.001 = $0.009, plus the start fee; the 164 rows filtered out and the 1 duplicate were free. - **A month, for example:** a daily scraper's output cleaned down to 200 rows a day: 6,000 rows × $0.001 = **$6.00**, plus 30 starts ($0.0015). - **Caps:** **Max rows per run** in the input (with sorting, these are the top rows), and **Maximum cost per run** in the run options. The run stops cleanly at whichever comes first. **Never charged:** rows that are filtered out, duplicates, empty after your field selection, or too large. The optional CSV or JSONL file is free. ### How to filter, dedupe and convert a dataset 1. Open Dataset Transformer and click **Try for free** (or **Start** if you're signed in). 2. Pick one of your datasets in **Apify dataset**, upload a file or paste its link in **Or: file URL or upload**, or paste the rows themselves in **Or: paste rows (JSON array)**. 3. Add **Filters** (one per line, e.g. `price > 10`), **Remove duplicates by**, **Sort by** and **Fields to keep (and rename)** as you need. 4. Optional: **Also save as a file** (CSV or JSON Lines). 5. Click **Start**, then open the **Output** tab and export as JSON, CSV or Excel. ### Example: the US Treasury's exchange rates The pre-filled input (a public CSV, filtered, deduplicated, sorted and trimmed to four renamed columns): ```json {"fileUrl": "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?format=csv&filter=record_date:eq:2025-12-31&page[size]=300", "filters": ["exchange_rate < 1"], "dedupeBy": ["currency", "exchange_rate"], "sortBy": ["exchange_rate"], "fields": ["country", "currency", "exchange_rate -> unitsPerUsd", "record_date -> date"]} ``` It read 174 rows and wrote 9. The first three (real output from a local run on 2026-09-25): ```json [ {"country": "Kuwait", "currency": "Dinar", "unitsPerUsd": "0.308", "date": "2025-12-31"}, {"country": "Bahrain", "currency": "Dinar", "unitsPerUsd": "0.377", "date": "2025-12-31"}, {"country": "Bulgaria", "currency": "Lev New", "unitsPerUsd": "0.86", "date": "2025-12-31"} ] ``` ### Example: scraper results to a clean CSV A typical job for Dataset Transformer: a Google Maps-style scraper ran two searches over the same area ("coffee shop in Austin, TX" and "cafe in Austin, TX"), so places repeat, and every row has 24 fields, most of them nested or not needed in a spreadsheet (opening hours, image lists, search rank, scrape time). The goal is one CSV with one row per open, well-rated place that has a phone number, busiest first, with plain column names. The places, phone numbers (555-01xx) and websites (`.example`) are made up for this example. **Before:** the scraper's 15 rows (6 of the 24 fields shown; the rest include `placeId`, `url`, `location.lat`, `location.lng`, `openingHours`, `imageUrls`, `additionalInfo`, `rank` and `scrapedAt`): | # | `title` | `totalScore` | `reviewsCount` | `phone` | `permanentlyClosed` | `searchString` | What happens | |---|---|---|---|---|---|---|---| | 1 | Juniper & Oak Coffee | 4.7 | 1284 | (512) 555-0101 | false | coffee shop in Austin, TX | kept | | 2 | Lantern Street Roasters | 4.6 | 902 | (512) 555-0102 | false | coffee shop in Austin, TX | kept | | 3 | Copper Kettle Café | 4.4 | 377 | (512) 555-0103 | false | coffee shop in Austin, TX | kept (no website: empty cell) | | 4 | Night Owl Espresso Bar | 4.1 | 215 | (512) 555-0104 | false | coffee shop in Austin, TX | filtered out: rating below 4.3 | | 5 | Riverbend Bakery & Coffee | 4.8 | 2140 | (512) 555-0105 | false | coffee shop in Austin, TX | kept | | 6 | The Grind House | 4.5 | 640 | (512) 555-0106 | **true** | coffee shop in Austin, TX | filtered out: permanently closed | | 7 | Bluebonnet Brew Co. | 4.5 | 88 | `null` | false | coffee shop in Austin, TX | filtered out: no phone | | 8 | Sixth & Vine Coffee | 4.5 | 312 | (512) 555-0108 | false | coffee shop in Austin, TX | kept | | 9 | Arbor Lane Café | 4.6 | 59 | (512) 555-0109 | false | coffee shop in Austin, TX | kept | | 10 | Riverbend Bakery & Coffee | 4.8 | 2140 | (512) 555-0105 | false | cafe in Austin, TX | duplicate of 5 (same `placeId`) | | 11 | Mesquite Moon Café | 4.3 | 141 | (512) 555-0110 | false | cafe in Austin, TX | kept | | 12 | Juniper & Oak Coffee | 4.7 | 1284 | (512) 555-0101 | false | cafe in Austin, TX | duplicate of 1 | | 13 | Pecan Tree Tea & Coffee | 4.2 | 403 | (512) 555-0111 | false | cafe in Austin, TX | filtered out: rating below 4.3 | | 14 | Lantern Street Roasters | 4.6 | 902 | (512) 555-0102 | false | cafe in Austin, TX | duplicate of 2 | | 15 | Copper Kettle Café | 4.4 | 377 | (512) 555-0103 | false | cafe in Austin, TX | duplicate of 3 | **The input** (replace `YOUR-SCRAPER-DATASET-ID` with your scraper run's dataset, or pick it in **Apify dataset**): ```json { "datasetId": "YOUR-SCRAPER-DATASET-ID", "filters": ["permanentlyClosed != true", "totalScore >= 4.3", "phone is not empty"], "dedupeBy": ["placeId"], "sortBy": ["reviewsCount desc"], "fields": ["title -> name", "categoryName -> category", "totalScore -> rating", "reviewsCount -> reviews", "phone", "website", "street", "city", "postalCode -> zip", "location.lat -> lat", "location.lng -> lng", "url -> googleMapsUrl"], "exportFormat": "csv" } ``` **After:** 7 rows with 12 columns, in the dataset and in `OUTPUT.csv` (6 of the 12 columns shown; the others are `category`, `street`, `city`, `lat`, `lng` and `googleMapsUrl`): | `name` | `rating` | `reviews` | `phone` | `website` | `zip` | |---|---|---|---|---|---| | Riverbend Bakery & Coffee | 4.8 | 2140 | (512) 555-0105 | https://riverbendbakery.example | 78746 | | Juniper & Oak Coffee | 4.7 | 1284 | (512) 555-0101 | https://juniperoak.example | 78702 | | Lantern Street Roasters | 4.6 | 902 | (512) 555-0102 | https://lanternstreet.example/menu?utm_source=gmb | 78704 | | Copper Kettle Café | 4.4 | 377 | (512) 555-0103 | | 78741 | | Sixth & Vine Coffee | 4.5 | 312 | (512) 555-0108 | https://sixthandvine.example | 78701 | | Mesquite Moon Café | 4.3 | 141 | (512) 555-0110 | https://mesquitemoon.example | 78751 | | Arbor Lane Café | 4.6 | 59 | (512) 555-0109 | https://arborlane.example | 78703 | The first lines of `OUTPUT.csv` (real output from a local run on 2026-10-04): ```csv name,category,rating,reviews,phone,website,street,city,zip,lat,lng,googleMapsUrl Riverbend Bakery & Coffee,Bakery,4.8,2140,(512) 555-0105,https://riverbendbakery.example,3300 Riverbend Dr,Austin,78746,30.2851,-97.8012,https://www.google.com/maps/place/?q=place_id:ChIJexample000005 Juniper & Oak Coffee,Coffee shop,4.7,1284,(512) 555-0101,https://juniperoak.example,418 Juniper St,Austin,78702,30.2641,-97.7289,https://www.google.com/maps/place/?q=place_id:ChIJexample000001 ``` The run's status: "7 rows written from 15 rows read; 4 filtered out; 4 duplicates removed; file saved as OUTPUT.csv". You pay for the 7 rows written (7 × $0.001 = $0.007 on the Free plan, plus the run start); the 4 filtered rows and the 4 duplicates are free. Filters, duplicates and sorting use the scraper's own field names (`totalScore`, `placeId`); the new names (`rating`, `zip`) only appear in the output. ### Ready-to-run examples Each example opens with the input already filled in. Run it as it is, or change the input first. - **[Filter, dedupe and sort a CSV file from a URL](https://apify.com/humble-echidna/dataset-transform/examples/filter-dedupe-csv-file)**: Clean a CSV, JSON or JSONL file in one step: keep only the rows that match your filters, remove duplicates, sort, and keep or rename the columns you need. - **[Convert a JSON API response to a CSV file](https://apify.com/humble-echidna/dataset-transform/examples/json-api-to-csv)**: Turn nested JSON from an API into flat rows: pick the list inside the response, flatten nested fields, rename the columns you need and get a CSV file. - **[Convert a CSV file to JSON or JSON Lines](https://apify.com/humble-echidna/dataset-transform/examples/csv-to-json)**: Load a CSV from a URL and get it as a dataset you can download as JSON, Excel or XML, plus a JSON Lines file, with the columns renamed the way you want. - **[Sort a CSV file and keep the top 10 rows](https://apify.com/humble-echidna/dataset-transform/examples/csv-top-rows-sorted)**: Sort any CSV by one or more columns, numbers as numbers, and keep only the top rows and the columns you need. - **[Convert an Excel file to JSON or CSV](https://apify.com/humble-echidna/dataset-transform/examples/excel-to-json)**: Read an Excel workbook (.xlsx) from a URL into rows, with numbers kept as numbers and dates as ISO text, then filter, rename and save a CSV. - **[Read an Excel table that starts below a title](https://apify.com/humble-echidna/dataset-transform/examples/excel-table-below-title)**: When a title and notes sit above the table, as in many official spreadsheets, set the header row and get clean rows from the table only. - **[Filter Google Maps leads by rating and reviews](https://apify.com/humble-echidna/dataset-transform/examples/filter-google-maps-leads)**: Keep only places with a website and phone, rated 4.5 or more with 50+ reviews and still open, one row per place, as a CSV. - **[Convert XLSX to CSV: an Excel spreadsheet to a CSV file](https://apify.com/humble-echidna/dataset-transform/examples/xlsx-to-csv)**: Turn an Excel workbook at a web address into a CSV file and a dataset, numbers kept as numbers. ### Input | Field | What it does | |---|---| | **Apify dataset** | One of your datasets, picked from the list (or its id or name). Read-only. | | **Or: paste rows (JSON array)** | The rows themselves, e.g. `[{"name": "Ada"}, {"name": "Bo"}]`. Used when no dataset is picked; the file URL is then ignored. | | **Or: file URL or upload** | A file uploaded from your computer, or a direct link to a CSV/TSV, JSON, JSONL or Excel (.xlsx) file (up to 200 MB). A Google Sheets link works when the sheet is shared as "Anyone with the link". Used when no dataset is picked and no rows are pasted. | | Filters | One condition per line: `field operator value` (see below). | | A row must match | All filters (AND) or any filter (OR). | | Remove duplicates by | Fields that identify a row, e.g. `url`, one per line or comma-separated (`name, city`). The first row of each is kept. `*` compares whole rows. | | Sort by | Fields, each optionally followed by `desc`, e.g. `price desc`. | | Fields to keep (and rename) | `field` or `field -> new name`, one per line, in output order. Empty keeps every field. | | Flatten nested objects | `{"address": {"city": "Paris"}}` becomes `"address.city": "Paris"`. | | Also save as a file | No file, CSV (`OUTPUT.csv`) or JSON Lines (`OUTPUT.jsonl`) in the run's key-value store. | | Max rows per run | Cap the number of output rows. | | Excel: sheet name, header row | The worksheet to read, by its tab name (empty: the first sheet), and the row with the column names when a title sits above the table (empty: the first non-empty row). | | File format, JSON path, CSV delimiter | Only needed when detection gets a file wrong (see the FAQ). | **Filters.** `==` `!=` `>` `>=` `<` `<=` compare numbers as numbers (also numbers stored as text, as in CSV files) and anything else as text, so ISO dates like `2026-01-31` compare correctly. `contains`, `not contains`, `starts with` and `ends with` ignore upper and lower case. `is empty` and `is not empty` take no value. Examples: ``` price > 10 country == US title contains remote postedAt >= 2026-09-01 email is not empty address.city == "New York" ``` Filters, duplicates and sorting use your data's original field names; renaming happens last. A full example, for a dataset of job postings: ```json { "datasetId": "YOUR-DATASET-ID", "filters": ["location contains Berlin", "salary is not empty"], "dedupeBy": ["url"], "sortBy": ["postedAt desc"], "fields": ["title", "company.name -> company", "salary", "url", "postedAt"], "exportFormat": "csv", "maxResults": 500 } ``` ### Output One dataset row per output row, with the fields you chose (or all of the source's fields). Fields you asked for that a row doesn't have are `null`, so every row has the same columns. With **Also save as a file**, the same rows are saved as `OUTPUT.csv` or `OUTPUT.jsonl` in the run's key-value store (nested values become JSON text in CSV cells). The `RUN_STATS` record has the counts: rows read, filtered out, duplicates, skipped, written. To download your latest file from code: `GET https://api.apify.com/v2/acts/humble-echidna~dataset-transform/runs/last/key-value-store/records/OUTPUT.csv?status=SUCCEEDED` with your API token (for a saved task, `actor-tasks/` in place of `acts/humble-echidna~dataset-transform`). The dataset itself can also be downloaded from Apify as CSV, JSON, Excel, XML or HTML at any time. ### Run it on a schedule, or from your own code 1. Save your input as a [task](https://docs.apify.com/platform/actors/running/tasks) (**Save as a new task**, top right of the actor page) and add it to a [schedule](https://docs.apify.com/platform/schedules) (Console → Schedules → **Create new**): for example daily, a few minutes after the scraper it cleans up. 2. Collect results: download the dataset as JSON, CSV or Excel; fetch the latest run's results from the [API](https://apify.com/humble-echidna/dataset-transform/api) (`GET https://api.apify.com/v2/actor-tasks//runs/last/dataset/items?status=SUCCEEDED&format=csv`, with your API token); let a [webhook](https://docs.apify.com/platform/integrations/webhooks) tell your system when a run succeeds; or connect it to Make, Zapier or n8n through [Apify's integrations](https://docs.apify.com/platform/integrations). To run it right after another actor finishes instead, chain it with an integration (any actor, ours or not): 1. Open the other actor or its saved task, go to the **Integrations** tab and add **Apify actor** → **Dataset Transformer**, triggered when a run **succeeds**. 2. Set `"datasetId": "{{resource.defaultDatasetId}}"` in the input (Apify fills in the finished run's dataset) and add your filters, dedupe keys and fields as usual. Every successful scrape then produces a cleaned copy, with no code. From the API it's the same input: pass the finished run's `defaultDatasetId` as `datasetId`. #### Run it automatically after your scraper finishes The worked example above, as an [Actor-to-Actor integration](https://docs.apify.com/platform/integrations/actors) on the scraper (or its saved task): 1. On the scraper's **Integrations** tab, add **Dataset Transformer** and set **Start when** to run succeeded. 2. In **Actor input**, check that **Apify dataset** says `{{resource.defaultDatasetId}}`; Apify replaces it with the dataset of the run that just finished. If the field is empty, the run still reads the finished run's dataset (from the integration's details). 3. Fill in the rest as in the example: the filters, `placeId` under **Remove duplicates by**, `reviewsCount desc` under **Sort by**, the fields, and **Also save as a file: CSV**. In JSON, the whole input is the example's input with `"datasetId": "{{resource.defaultDatasetId}}"`. 4. Save, then test it on one of the scraper's past runs (the integration's test menu). Each successful scrape then leaves a deduplicated `OUTPUT.csv` in the Dataset Transformer run's key-value store. The integration also sends a `payload` field with the finished run's details. A dataset named in **Apify dataset** wins; when that field is empty, Dataset Transformer reads `payload.resource.defaultDatasetId` instead. **Without the Integrations form**, an integration is a webhook that starts a run. Create it with the [Create webhook API](https://docs.apify.com/api/v2/webhooks-post) on the `ACTOR.RUN.SUCCEEDED` event, with the input as the payload template ([variables](https://docs.apify.com/platform/integrations/webhooks/actions#payload-template)): ```json { "eventTypes": ["ACTOR.RUN.SUCCEEDED"], "condition": {"actorId": ""}, "requestUrl": "https://api.apify.com/v2/acts/humble-echidna~dataset-transform/runs", "shouldInterpolateStrings": true, "payloadTemplate": "{\"datasetId\": \"{{resource.defaultDatasetId}}\", \"filters\": [\"totalScore >= 4.3\"], \"dedupeBy\": [\"placeId\"], \"exportFormat\": \"csv\"}" } ``` Use `"actorTaskId"` in `condition` for a saved task. Apify adds your token to requests to its own API, and the Console lists this one as an HTTP webhook rather than an integration ([details](https://docs.apify.com/platform/integrations/actors/integrating-actors-via-api)). #### Use it from n8n With n8n's Apify node ([Apify's n8n guide](https://docs.apify.com/platform/integrations/n8n)): 1. Add an **Apify** node with the operation **Run an Actor**, pick `humble-echidna/dataset-transform`, paste your input JSON (for example the one above, with your scraper's dataset id), and turn on **Wait for finish**. 2. Add a second **Apify** node with the operation **Get dataset items**, and set **Dataset ID** to the `defaultDatasetId` from the first node's output. 3. Send the rows on: Google Sheets, a CRM, a database. For the CSV file instead, use **Get key-value store record** with the first node's `defaultKeyValueStoreId` and the key `OUTPUT.csv`. **Run an Actor and get dataset** does steps 1 and 2 in one node. Don't replace step 1 with a fixed Wait node, and don't read the actor's "last run" instead of the run's own `defaultDatasetId`: a fixed wait breaks when a run takes longer than usual, and a "last run" read returns another run's rows when two runs overlap (a schedule and a manual run, or two workflows). #### Can I use Dataset Transformer from an AI agent (MCP)? Yes, through [Apify's MCP server](https://docs.apify.com/platform/integrations/mcp), from Claude, ChatGPT, Cursor or any other MCP client. Add this to your client's MCP configuration (or let the agent find it with the server's actor search); your client signs you in to Apify: ```json { "mcpServers": { "apify": { "url": "https://mcp.apify.com?tools=humble-echidna/dataset-transform" } } } ``` To use an [Apify API token](https://console.apify.com/settings/integrations) instead of signing in, add `"headers": {"Authorization": "Bearer "}` next to `url`. An agent that has just run a scraper can pass its dataset, e.g. `{"datasetId": "", "filters": ["price > 10"], "fields": ["title", "price", "url"]}`, to get a smaller, cleaner result back. An agent holding JSON can pass the rows themselves to get a CSV: `{"items": [{"name": "Ada", "city": "London"}, {"name": "Bo", "city": "Paris"}], "exportFormat": "csv"}`. ### Who it's for Anyone who already runs scrapers on Apify (ours or anyone's) and hands the results to a spreadsheet, CRM or database: it's the scheduled clean-up step in between, so the export has only the rows and columns you want, with no duplicates, every time. ### Why this one? - **One flat price, no usage bill.** You pay per result at the price above and nothing else: no compute, proxy or storage charges on top, apart from the $0.00005 run-start fee, so you know what a run costs before it starts. Results you don't get aren't charged. - **Datasets and files alike.** Your own Apify datasets, or CSV/TSV, JSON, JSON Lines and Excel (.xlsx) files from any public web address, gzip-compressed or not. Format, delimiter and text encoding (UTF-8, UTF-16, Windows-1252) are detected for you, and a broken character (half an emoji, from text cut mid-character) never fails the run. - **Pay only for the rows you keep**: $1.00 per 1,000 output rows, and the CSV/JSONL file is included at no extra charge. Rows you filter out or that are duplicates cost nothing. - **Built for big inputs.** Files stream to disk and are read one row at a time, so a 200 MB file doesn't need 200 MB of memory. Up to 1,000,000 rows per run. - **Nested data made simple.** `address.city` or `offers.0.price` work in every option, with or without flattening. - **Tells you what happened.** The run's status says how many rows were read, filtered out, duplicates and written, and warns about field names that no row has (the most common mistake is a typo in a field name). - **Polite and safe with files.** It identifies itself honestly (User-Agent `HumbleEchidnaApify`), follows each site's robots.txt, and only requests public web addresses on the standard ports (80 and 443). ### Limits - One source per run: one dataset, one file, or one list of pasted rows. Pasted rows travel inside the run's input, so keep them to small data; for big data use a dataset or a file link. - Files: up to 200 MB (after decompression), public web addresses on ports 80 and 443, CSV/TSV, JSON, JSON Lines, Excel (.xlsx, .xlsm). - Excel: one sheet per run. Old `.xls` and password-protected workbooks aren't read (save them as .xlsx or CSV first). A workbook may unzip to at most 1 GB, with at most 150 MB of text. - Up to 1,000,000 rows read per run; the status says so if a source has more. - Sorting without a row limit holds at most 100 MB of matching rows; beyond that the run stops before writing anything and says so. Set **Max rows per run** (it then keeps only the top rows) or filter first. - A single output row can be up to 5 MB; larger ones are skipped and counted. - The CSV/JSONL file is saved up to 100 MB; beyond that it's skipped (the dataset still has every row). ### FAQ #### Which duplicate is kept? The first one in the source's order, before sorting. Rows where every duplicate field is empty are always kept (so rows without an email aren't collapsed into one). Text that looks the same counts as the same: "é" saved as one character or as "e" plus an accent is one value. A field whose name has a comma goes in double quotes: `"Revenue, USD"`. #### Why does my JSON file fail with "set itemsPath"? The file is one object with the rows inside it, like `{"data": {"items": [...]}}`. Put the path to the list, here `data.items`, in **JSON: path to the list of rows**. The error lists the paths it found. #### Why are CSV values all text? On purpose: leading zeros (zip codes, ids) survive. Filters and sorting still treat numbers as numbers. Excel cells keep their type (see the next question). #### Can it convert Excel to JSON or CSV? Yes. Upload an `.xlsx` file or paste its link in **Or: file URL or upload**; it's detected from the file itself, so a download link without `.xlsx` in it works too. You get one row per spreadsheet row, keyed by the sheet's first non-empty row (the header). When a title or notes sit above the table, put the number of the row with the column names in **Excel: header row**, as Excel numbers it, and the rows above are skipped. Numbers and TRUE/FALSE stay numbers and booleans, dates become `2025-12-31` (or `2025-12-31T14:30:00` with a time), and formulas give the value Excel last saved. Empty rows are skipped. Pick a sheet other than the first in **Excel: sheet name**, and choose **Also save as a file: CSV** for Excel to CSV. Filters, duplicates, sorting and renaming work as for any other source. #### Can I use a file on my computer or a Google Sheet? Yes. Click the upload button in **Or: file URL or upload** and pick the file; a path like `C:\Users\...` typed into the field can't be reached by the run. For a Google Sheet, paste its link as it is (the `/edit` link is fine) after setting **Share > General access** to **Anyone with the link**: the tab in the link is read, or the one you name in **Excel: sheet name**. Google Drive and Dropbox file links can't be read (their robots.txt disallows it), so upload those files instead. #### Can I paste JSON instead of giving a link? Yes. Put the rows in **Or: paste rows (JSON array)** (`items` in the API), as a list of objects: `[{"name": "Ada", "city": "London"}, {"name": "Bo", "city": "Paris"}]`. Filters, duplicates, sorting, renaming, flattening and the CSV or JSONL file work as for any other source, so this is JSON to CSV with no file to host. An element that isn't an object becomes `{"value": ...}`. An empty list counts as nothing pasted, so the file link is used. #### Why does the status say a field wasn't found in any row? Field names are case-sensitive and must match your data exactly; use dots for nested fields. #### Can it read someone else's dataset? Only datasets your own Apify account can access. The run reads the dataset you pick with read-only access. #### How do I turn nested scraper JSON into flat CSV columns? Turn on **Flatten nested objects** (`"flatten": true`): `{"address": {"city": "Paris"}}` becomes an `address.city` column, and a list becomes numbered columns (`categories.0`, `categories.1`). To keep only the nested values you need, name them in **Fields to keep (and rename)**, flattened or not: `location.lat -> lat`, `offers.0.price -> price`. Without flattening, a nested value is written to the CSV file as JSON text in one cell. #### How do I keep only Google Maps leads with a website, a phone and enough reviews? Pick the Maps scraper's dataset in **Apify dataset** and filter on the scraper's own field names, for example: ``` website is not empty phone is not empty totalScore >= 4.5 reviewsCount >= 50 permanentlyClosed != true ``` Add `placeId` under **Remove duplicates by** (two searches over the same area return the same place twice) and `reviewsCount desc` under **Sort by**. Field names differ between scrapers, so check one row of your dataset first; the status warns about a field that no row has. The [worked example](#example-scraper-results-to-a-clean-csv) shows the whole input and its CSV. #### Why not just export the dataset as CSV from Apify? Apify's export (and its dataset API) gives you every row: it can pick, omit or flatten fields and reverse the order, but it can't keep only the rows that match a condition, drop duplicates by a key, or sort by a field. Dataset Transformer does those in one step and writes a new dataset (plus an optional CSV file) that you export the usual way or pass to the next integration. You pay only for the rows it keeps. #### Can it remove duplicates across several runs of my scraper? Each run reads one source, so duplicates are removed within that dataset or file. To get only the rows that are new or changed since your scraper's previous run, use [Dataset Diff](https://apify.com/humble-echidna/dataset-diff) first, then clean its output here. #### Can it change values, split a column or combine two datasets? No. Dataset Transformer keeps or drops rows and fields, sorts, renames and flattens, but doesn't rewrite values (no lowercasing, splitting or formulas). To add fields from another dataset or file matched by a key, use [Join Datasets](https://apify.com/humble-echidna/dataset-join); to have an LLM classify or summarize each row, use [AI for Every Row](https://apify.com/humble-echidna/ai-rows). #### Is it legal to transform datasets and files with it? It only processes data you choose: your own Apify datasets, and files at addresses you supply, fetched politely (robots.txt honoured, honest User-Agent). Make sure you're allowed to use the files you point it at. The example data is the US Treasury's Fiscal Data API, which is "offered free, without restriction" for commercial and non-commercial use. #### Something that used to work now fails. Why? The run log and the status say what went wrong. Please open an issue with the input you used. ### Related actors It works on any dataset; some of ours whose output it's handy for: | Actor | Use it when | |---|---| | [Company Career Page Jobs Scraper](https://apify.com/humble-echidna/ats-jobs), [Remote Jobs Scraper](https://apify.com/humble-echidna/remote-jobs) | You want only jobs in a city or with a salary, one row per URL, as a CSV for a job board. | | [SEO Audit Crawler](https://apify.com/humble-echidna/seo-audit) | You want only pages with errors, with the columns a client report needs. | | [Company Register Search](https://apify.com/humble-echidna/company-registers) | You want nested register records flattened into plain columns for a CRM import. | | [Dataset Diff](https://apify.com/humble-echidna/dataset-diff) | You run a scraper on a schedule and want only the rows that are **new, changed or removed** since its last run (or between two files). | | [Join Datasets](https://apify.com/humble-echidna/dataset-join) | You need fields from **another dataset or file** added to each row, matched by a key or website domain (VLOOKUP for datasets). | | [AI Data Extractor](https://apify.com/humble-echidna/ai-extract) | Your dataset holds page URLs and you need **fields from each page** (services, contacts, prices), not just the rows reshaped. Chain it after the scraper, then flatten its `data` into columns here. | ### Feedback and support Found a bug, or need an operation that isn't here? Open an issue on the **Issues** tab with the input you used. ### Versions Current version: **1.2**. See the Changelog tab for what changed in each version. # Changelog This Actor's version history is a separate document: https://apify.com/humble-echidna/dataset-transform/changelog.md # Actor input Schema ## `datasetId` (type: `string`): The id or name of one of your own Apify datasets, e.g. the defaultDatasetId of another actor's run (or pick one in the form). The run reads it with your own Apify account's access, read-only. When it's set, items and fileUrl are ignored. ## `fileUrl` (type: `string`): Upload a CSV/TSV, JSON (an array of objects), JSON Lines or Excel (.xlsx) file from your computer, or give a direct public http(s) link to one, up to 200 MB, gzip-compressed or not. A Google Sheets link works too when the sheet is shared as "Anyone with the link" (the tab in the link is read, or the one named in sheetName). Used only when datasetId and items are empty. The default is the US Treasury's official exchange rates for 2025-12-31 (a public CSV of 174 currencies). ## `items` (type: `array`): Rows given directly in the input, as a JSON array of objects, e.g. \[{"name": "Ada", "city": "London"}, {"name": "Bo", "city": "Paris"}]. For data you already have, such as JSON an AI agent wants as CSV; for large data use datasetId or fileUrl. Used when datasetId is empty; fileUrl is then ignored. An element that isn't an object becomes {"value": ...}. Default: none. ## `filters` (type: `array`): Keep only rows that match, one condition per line: `field operator value`. Operators: == != > >= < <= (numbers compare as numbers, anything else as text), contains, not contains, starts with, ends with (these ignore upper/lower case), is empty, is not empty. Examples: `price > 10`, `country == US`, `email is not empty`. Dots for nested fields: `address.city == Paris`. Combined per filterMode. Default: none. ## `filterMode` (type: `string`): How several filters combine. `all` (the default): a row must match every filter (AND). `any`: a row must match at least one (OR). ## `dedupeBy` (type: `array`): Fields that together identify a row, one per line or comma-separated (e.g. `url`, or `name, city`; put a field name that has a comma in double quotes: `"Revenue, USD"`). Only the first row with each combination is kept; text that differs only in how an accent is encoded counts as the same. `*` alone compares whole rows. Rows where all these fields are empty are always kept. Default: no deduplication. ## `sortBy` (type: `array`): Fields to sort by, one per line, each optionally followed by `desc` (e.g. `price desc`). Numbers sort as numbers, text ignoring upper/lower case; empty values always go last. Leave empty to keep the source order. ## `fields` (type: `array`): The output fields, in this order, one per line: `field`, or `field -> new name` to rename it. A field a row doesn't have is null. Leave empty to keep every field. Filters, duplicates and sorting use the original names. ## `flatten` (type: `boolean`): Default false. When true, turn nested objects and arrays into dotted fields ({"address": {"city": "Paris"}} becomes "address.city": "Paris"), which is what spreadsheets and CSV need. Dotted field names in the options above work either way. ## `exportFormat` (type: `string`): Also save the output rows as one file in the run's key-value store (up to 100 MB), at no extra cost. `none` (the default): no file. `csv`: OUTPUT.csv. `jsonl`: OUTPUT.jsonl (JSON Lines). The dataset itself can always be downloaded as CSV, JSON, Excel and more from Apify. ## `maxResults` (type: `integer`): Stop after this many output rows in total, e.g. 20. Minimum 1; leave empty (the default) for no limit. With sorting, these are the top rows. The run also stops cleanly at the maximum cost per run you set in the run options, whichever comes first. Set it to keep a run small and quick. ## `fileFormat` (type: `string`): The format of fileUrl. `auto` (the default): read from the file itself; set another only if detection gets it wrong. `csv`: CSV or TSV. `json`: JSON (an array, or see itemsPath). `jsonl`: JSON Lines. `xlsx`: an Excel workbook (.xlsx or .xlsm; for an old .xls, save it as .xlsx first). ## `itemsPath` (type: `string`): JSON files only: when the file is one object with the rows inside it (like {"data": {"items": \[...]}}), the dotted path to that list, e.g. `data.items`. Leave empty (the default) for a file that is a list. ## `csvDelimiter` (type: `string`): CSV files only: the character between columns: `,` `;` `|` or `tab`. Leave empty (the default) to detect it. ## `sheetName` (type: `string`): Excel files only: the worksheet to read, by its tab name (e.g. `Sales 2025`; upper/lower case doesn't matter). Leave empty (the default) for the first sheet. Its first non-empty row is the header row; formulas give the value Excel last saved. ## `headerRow` (type: `integer`): Excel files only: the number of the row that has the column names, as Excel numbers rows (e.g. `5` when a title and notes fill rows 1-4). Rows above it are skipped. Leave empty (the default) to use the first non-empty row. ## Actor input object example ```json { "fileUrl": "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?format=csv&filter=record_date:eq:2025-12-31&page[size]=300", "filters": [ "exchange_rate < 1" ], "filterMode": "all", "dedupeBy": [ "currency", "exchange_rate" ], "sortBy": [ "exchange_rate" ], "fields": [ "country", "currency", "exchange_rate -> unitsPerUsd", "record_date -> date" ], "flatten": false, "exportFormat": "none", "fileFormat": "auto" } ``` # Actor output Schema ## `results` (type: `string`): No description ## `runStats` (type: `string`): No description ## `csvFile` (type: `string`): No description ## `jsonlFile` (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 '' with your token const client = new ApifyClient({ token: '', }); // Prepare Actor input const input = { "fileUrl": "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?format=csv&filter=record_date:eq:2025-12-31&page[size]=300", "filters": [ "exchange_rate < 1" ], "dedupeBy": [ "currency", "exchange_rate" ], "sortBy": [ "exchange_rate" ], "fields": [ "country", "currency", "exchange_rate -> unitsPerUsd", "record_date -> date" ] }; // Run the Actor and wait for it to finish const run = await client.actor("humble-echidna/dataset-transform").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 '' with your token. client = ApifyClient("") # Prepare the Actor input run_input = { "fileUrl": "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?format=csv&filter=record_date:eq:2025-12-31&page[size]=300", "filters": ["exchange_rate < 1"], "dedupeBy": [ "currency", "exchange_rate", ], "sortBy": ["exchange_rate"], "fields": [ "country", "currency", "exchange_rate -> unitsPerUsd", "record_date -> date", ], } # Run the Actor and wait for it to finish run = client.actor("humble-echidna/dataset-transform").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 '{ "fileUrl": "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?format=csv&filter=record_date:eq:2025-12-31&page[size]=300", "filters": [ "exchange_rate < 1" ], "dedupeBy": [ "currency", "exchange_rate" ], "sortBy": [ "exchange_rate" ], "fields": [ "country", "currency", "exchange_rate -> unitsPerUsd", "record_date -> date" ] }' | apify call humble-echidna/dataset-transform --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "type": "http", "url": "https://mcp.apify.com/?tools=fetch-actor-details,humble-echidna/dataset-transform" } } } ``` 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 ` 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/hPoDPuM4tQIRqkAj0/builds/aPkFsfIcXLYrYxQxY/openapi.json