# Smart Dataset Merger, Cleaner & Pattern Splitter (CSV + XLSX) (`thecfmarketing/smart-dataset-merger-cleaner`) Actor Merge mixed CSV & Excel files by pattern, standardize columns, clean leads, remove duplicates, extract domains, and split outputs by row limits. - **URL**: https://apify.com/thecfmarketing/smart-dataset-merger-cleaner.md - **Developed by:** [sadique shaikh](https://apify.com/thecfmarketing) (community) - **Categories:** Lead generation, Automation, Developer tools - **Stats:** 3 total users, 2 monthly users, 100.0% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $0.10 / actor start 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/platform/actors/running/actors-in-store#pay-per-event ## What's an Apify Actor? Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows. 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. Actors are written with capital "A". ## 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. The best way to integrate Actors is as follows. - **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use). - **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`. - **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`. - **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`. - **Any other language** — the [REST API](https://docs.apify.com/api/v2.md). For usage examples, see the [API](#api) section below. For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt). # README ## Smart Multi-Format Dataset Merger, Cleaner & Pattern Splitter 🚀 A high-performance Apify Actor to merge mixed **CSV** and **Excel (`.xlsx`)** files, auto-group by column patterns, clean leads, remove duplicate records, split large outputs by row limits, and ingest directly from **Apify Datasets**, **ZIP archives**, or **Public URLs**. *** ### 🌟 Key Features & USPs - 🧠 **Pattern-Based Auto Grouping**: Prevents column misalignment and data corruption by automatically grouping files matching exact column signatures. - ⚡ **Mixed File Ingestion**: Process `.csv` and `.xlsx` files simultaneously in a single run. - 🎯 **Custom Row-Limit Splitting**: Split output files into custom row counts (e.g. `50,000` for CRMs, `100,000` for Google Sheets, `500,000` for balanced handling). - 🧹 **Lead Cleaning & Deduplication**: - Remove duplicates across merged datasets. - Lowercase all email addresses (`USER@GMAIL.COM` -> `user@gmail.com`). - Trim extra whitespace from all text columns. - Split `Full Name` into `first_name` and `last_name`. - Extract root domain (`company.com`) from email or website columns. - ☁️ **Cloud Native Integration**: Ingest direct **Apify Dataset IDs**, ZIP download links, or public CSV/Excel file URLs. - 📦 **Multi-Format Export**: Export output files as `CSV`, `Excel (.xlsx)`, or `JSON`. *** ### 📋 Input Parameters | Parameter | Type | Default | Description | | :--- | :--- | :--- | :--- | | `zipFileUrl` | String | `null` | Direct URL to a `.zip` file containing CSV or Excel files. | | `fileUrls` | Array | `[]` | List of public URLs to direct CSV/XLSX files. | | `apifyDatasetIds` | Array | `[]` | List of Apify Dataset IDs to merge directly cloud-to-cloud. | | `rowsPerFile` | Integer | `500000` | Max rows allowed per split output file. | | `autoStandardizeHeaders` | Boolean | `true` | Map alias headers (`Email Address`, `e-mail` -> `email`). | | `removeDuplicates` | Boolean | `true` | Remove duplicate records across datasets. | | `dedupColumn` | String | `null` | Column to deduplicate on (e.g. `email`). | | `trimWhitespace` | Boolean | `true` | Trim spaces from text cells. | | `lowercaseEmails` | Boolean | `true` | Lowercase email addresses. | | `splitFullNames` | Boolean | `false` | Split `Full Name` into `first_name` & `last_name`. | | `extractDomains` | Boolean | `false` | Extract root domains into a `domain` column. | | `outputFormat` | String | `"csv"` | Output file format (`csv`, `xlsx`, `json`). | *** ### 📤 Output Artifacts - **Key-Value Store**: Output split files (`format_1_part_1.csv`, `format_1_part_2.csv`, etc.) ready for download. - **Dataset Summary**: A JSON report logging total files processed, pattern groups, row counts, and column schemas. *** ### 🛠️ How to Deploy to Apify 1. Install Apify CLI: ```bash npm install -g apify-cli ``` 2. Log in to your Apify account: ```bash apify login ``` 3. Push to your Apify console: ```bash apify push ``` *** ### 👨‍💻 Support & Issues For feature requests or issues, reach out on the Apify Store actor discussion page! # Actor input Schema ## `uploadedFileUrl` (type: `string`): Upload or paste your CSV, Excel (.xlsx), or ZIP file from your computer. ## `zipFileUrl` (type: `string`): Direct download link to a .zip file (e.g. Google Drive, Dropbox, or public URL). ## `fileUrls` (type: `array`): List of direct public URLs to CSV or Excel files. ## `apifyDatasetIds` (type: `array`): List of Apify Dataset IDs to download and merge directly from your scrapers. ## `rowsPerFile` (type: `integer`): Maximum number of rows allowed per split file in the output (e.g. 50,000 for CRMs, 100,000 for Google Sheets, 500,000 for balanced processing). ## `autoStandardizeHeaders` (type: `boolean`): Automatically map alias column names (e.g. 'Email Address', 'e-mail', 'Contact Email' -> 'email') so files with slight variations merge into one format. ## `removeDuplicates` (type: `boolean`): Enable to remove duplicate records across merged datasets. ## `dedupColumn` (type: `string`): Column name to base deduplication on (e.g. 'email' or 'phone'). Leave blank to deduplicate on exact full row match. ## `trimWhitespace` (type: `boolean`): Trim leading and trailing spaces from all text fields. ## `lowercaseEmails` (type: `boolean`): Convert all email addresses to lowercase (e.g., USER@GMAIL.COM -> user@gmail.com). ## `splitFullNames` (type: `boolean`): Split 'Full Name' or 'name' columns into 'first\_name' and 'last\_name' columns. ## `extractDomains` (type: `boolean`): Extract domain names from email addresses or website URLs into a 'domain' column. ## `outputFormat` (type: `string`): Format for output files (CSV, XLSX, or JSON). ## Actor input object example ```json { "rowsPerFile": 500000, "autoStandardizeHeaders": true, "removeDuplicates": true, "trimWhitespace": true, "lowercaseEmails": true, "splitFullNames": false, "extractDomains": false, "outputFormat": "csv" } ``` # 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 = {}; // Run the Actor and wait for it to finish const run = await client.actor("thecfmarketing/smart-dataset-merger-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 '' with your token. client = ApifyClient("") # Prepare the Actor input run_input = {} # Run the Actor and wait for it to finish run = client.actor("thecfmarketing/smart-dataset-merger-cleaner").call(run_input=run_input) # Fetch and print Actor results from the run's dataset (if there are any) print("💾 Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"]) for item in client.dataset(run["defaultDatasetId"]).iterate_items(): print(item) # 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start ``` ## CLI example ```bash echo '{}' | apify call thecfmarketing/smart-dataset-merger-cleaner --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "command": "npx", "args": [ "mcp-remote", "https://mcp.apify.com/?tools=thecfmarketing/smart-dataset-merger-cleaner", "--header", "Authorization: Bearer " ] } } } ``` ## OpenAPI specification ```json { "openapi": "3.0.1", "info": { "title": "Smart Dataset Merger, Cleaner & Pattern Splitter (CSV + XLSX)", "description": "Merge mixed CSV & Excel files by pattern, standardize columns, clean leads, remove duplicates, extract domains, and split outputs by row limits.", "version": "1.0", "x-build-id": "JDoRvnE1Gryzzb2PX" }, "servers": [ { "url": "https://api.apify.com/v2" } ], "paths": { "/acts/thecfmarketing~smart-dataset-merger-cleaner/run-sync-get-dataset-items": { "post": { "operationId": "run-sync-get-dataset-items-thecfmarketing-smart-dataset-merger-cleaner", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for its completion, and returns Actor's dataset items in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } }, "/acts/thecfmarketing~smart-dataset-merger-cleaner/runs": { "post": { "operationId": "runs-sync-thecfmarketing-smart-dataset-merger-cleaner", "x-openai-isConsequential": false, "summary": "Executes an Actor and returns information about the initiated run in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK", "content": { "application/json": { "schema": { "$ref": "#/components/schemas/runsResponseSchema" } } } } } } }, "/acts/thecfmarketing~smart-dataset-merger-cleaner/run-sync": { "post": { "operationId": "run-sync-thecfmarketing-smart-dataset-merger-cleaner", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for completion, and returns the OUTPUT from Key-value store in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } } }, "components": { "schemas": { "inputSchema": { "type": "object", "properties": { "uploadedFileUrl": { "title": "📂 Select File from Computer / Direct File URL", "type": "string", "description": "Upload or paste your CSV, Excel (.xlsx), or ZIP file from your computer." }, "zipFileUrl": { "title": "ZIP File Direct Link (URL)", "type": "string", "description": "Direct download link to a .zip file (e.g. Google Drive, Dropbox, or public URL)." }, "fileUrls": { "title": "Direct File URLs", "type": "array", "description": "List of direct public URLs to CSV or Excel files.", "items": { "type": "string" } }, "apifyDatasetIds": { "title": "Apify Dataset IDs", "type": "array", "description": "List of Apify Dataset IDs to download and merge directly from your scrapers.", "items": { "type": "string" } }, "rowsPerFile": { "title": "Rows Per Split File", "minimum": 1000, "maximum": 2000000, "type": "integer", "description": "Maximum number of rows allowed per split file in the output (e.g. 50,000 for CRMs, 100,000 for Google Sheets, 500,000 for balanced processing).", "default": 500000 }, "autoStandardizeHeaders": { "title": "Auto-Standardize Header Names", "type": "boolean", "description": "Automatically map alias column names (e.g. 'Email Address', 'e-mail', 'Contact Email' -> 'email') so files with slight variations merge into one format.", "default": true }, "removeDuplicates": { "title": "Remove Duplicate Rows", "type": "boolean", "description": "Enable to remove duplicate records across merged datasets.", "default": true }, "dedupColumn": { "title": "Deduplication Column (Optional)", "type": "string", "description": "Column name to base deduplication on (e.g. 'email' or 'phone'). Leave blank to deduplicate on exact full row match." }, "trimWhitespace": { "title": "Trim Whitespace & Clean Text", "type": "boolean", "description": "Trim leading and trailing spaces from all text fields.", "default": true }, "lowercaseEmails": { "title": "Lowercase Email Addresses", "type": "boolean", "description": "Convert all email addresses to lowercase (e.g., USER@GMAIL.COM -> user@gmail.com).", "default": true }, "splitFullNames": { "title": "Split Full Name into First & Last Name", "type": "boolean", "description": "Split 'Full Name' or 'name' columns into 'first_name' and 'last_name' columns.", "default": false }, "extractDomains": { "title": "Extract Company Domain", "type": "boolean", "description": "Extract domain names from email addresses or website URLs into a 'domain' column.", "default": false }, "outputFormat": { "title": "Output File Format", "enum": [ "csv", "xlsx", "json" ], "type": "string", "description": "Format for output files (CSV, XLSX, or JSON).", "default": "csv" } } }, "runsResponseSchema": { "type": "object", "properties": { "data": { "type": "object", "properties": { "id": { "type": "string" }, "actId": { "type": "string" }, "userId": { "type": "string" }, "startedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "finishedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "status": { "type": "string", "example": "READY" }, "meta": { "type": "object", "properties": { "origin": { "type": "string", "example": "API" }, "userAgent": { "type": "string" } } }, "stats": { "type": "object", "properties": { "inputBodyLen": { "type": "integer", "example": 2000 }, "rebootCount": { "type": "integer", "example": 0 }, "restartCount": { "type": "integer", "example": 0 }, "resurrectCount": { "type": "integer", "example": 0 }, "computeUnits": { "type": "integer", "example": 0 } } }, "options": { "type": "object", "properties": { "build": { "type": "string", "example": "latest" }, "timeoutSecs": { "type": "integer", "example": 300 }, "memoryMbytes": { "type": "integer", "example": 1024 }, "diskMbytes": { "type": "integer", "example": 2048 } } }, "buildId": { "type": "string" }, "defaultKeyValueStoreId": { "type": "string" }, "defaultDatasetId": { "type": "string" }, "defaultRequestQueueId": { "type": "string" }, "buildNumber": { "type": "string", "example": "1.0.0" }, "containerUrl": { "type": "string" }, "usage": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "integer", "example": 1 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } }, "usageTotalUsd": { "type": "number", "example": 0.00005 }, "usageUsd": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "number", "example": 0.00005 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } } } } } } } } } ```