# Excel & CSV Merger: Consolidate, Pivot & Batch Convert (`dws_build/excel-csv-merger-consolidate-pivot-batch-convert`) Actor Combine, pivot, and batch convert, rename, or format many Excel and CSV files in one run. - **URL**: https://apify.com/dws_build/excel-csv-merger-consolidate-pivot-batch-convert.md - **Developed by:** [DWS Build](https://apify.com/dws_build) (community) - **Security:** Limited permissions - **Stats:** 2 total users, 1 monthly users, 45.5% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $20.00 / 1,000 processed files 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 ## DWS Spreadsheet Premium Combine, pivot, and batch-process Excel and CSV files in one run. Point it at up to 50 files by URL and get back download links for the results. - **Consolidate** many files or sheets into one table, either stacked (rows appended, columns lined up by header name) or merged side by side on a key column. - **Pivot** across every input sheet: group by one or more columns and get sum, count, mean, min, and max. - **Convert, rename columns, and format** every file in a batch: xlsx, csv, or json out; one rename map for all files; bold and frozen header, auto column width, and number or date formats per column. Reads `.xlsx`, `.xlsm`, `.xls`, and `.csv`. Writes `.xlsx`, `.csv`, or `.json`. ### How to give it files The Actor runs in the cloud, so it cannot read files on your computer. Give it files in one of two ways: 1. **URLs (recommended).** Any direct-download http(s) link: S3 or other signed links, an Apify key-value store record, a Dropbox link ending in `?dl=1`, or a Google Drive direct-download link. A share page that shows the file in a browser is not a download link; the Actor will tell you if it gets a web page instead of a spreadsheet. 2. **Base64** for small API calls: `{"base64": "...", "name": "jan.csv"}`. A name with the file extension is required. ### Limits - **30 MB per file and 30 MB per run in total** (all files together), and up to 50 files per run. Base64 input is limited to 5 MB per file and 10 MB per run. The caps keep runs fast and cheap. For a bigger job, split the files over several runs. - The first row of each sheet is the header row. - **Output keeps cell values only. Where a cell had a formula, you get its last computed value, not the formula. Original styles, merged cells and charts are not preserved.** - CSV output adds a leading apostrophe to text that starts with `=`, `+`, `-`, or `@` (when it is not a number), so the text cannot run as a formula when the file is opened in a spreadsheet app. ### Example inputs These examples use the sample files in [eschaq/dws-spreadsheet-samples](https://github.com/eschaq/dws-spreadsheet-samples), so you can paste one into the input, click **Start**, and see it work. Then replace the URLs with your own files. **Consolidate: stack regional or monthly exports into one sheet** ```json { "operation": "consolidate", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/north.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/south.xlsx" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/west.xls" } ], "consolidate": { "strategy": "stack_rows", "addSourceColumn": true }, "outputFormat": "xlsx" } ``` Columns are matched by name, ignoring case, extra spaces, and the difference between spaces, underscores and hyphens, so `Cust ID` and `cust_id` merge into one column. A `_source` column says which file and sheet each row came from. Columns that still look alike after that (`CustID` and `Cust ID`) are kept apart and listed in the warnings. To join files side by side on a key instead, use `"strategy": "merge_on_key", "keyColumn": "SKU"`. **Pivot: revenue by region across every file** ```json { "operation": "pivot", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/sales.xlsx" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/legacy.xls" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/march.csv" } ], "pivot": { "groupBy": ["Region"], "values": ["Revenue"], "aggregations": ["sum", "count", "mean"] }, "outputFormat": "xlsx" } ``` Pivot reads every sheet of every input file by default. The optional `sheets` field limits it to the sheets you name in workbooks; CSV files are always included. Cells that are not numbers in a value column are skipped and counted in the warnings. **Batch: rename columns and format every file** ```json { "operation": "format", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/orders_1.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/orders_2.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/orders_3.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/orders_4.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/orders_5.csv" } ], "renameColumns": { "Cust ID": "customer_id", "Order Date": "order_date" }, "format": { "boldHeader": true, "freezeHeader": true, "autoWidth": true, "columnFormats": { "order_date": "yyyy-mm-dd", "Amount": "#,##0.00" } }, "outputFormat": "xlsx" } ``` In `convert`, `rename_columns`, and `format` runs, a filled-in **Rename columns** map and a filled-in **Format options** block both apply: rename first, then format. That is still one file-processed charge per file. `columnFormats` uses the column names after renaming. Formatting needs xlsx output. In any xlsx output (convert, consolidate, pivot, and the batch operations), ISO dates stored as text become real date cells: `2026-01-05`, with an optional time (`2026-01-05 13:45:00`), or the T form without a time zone (`2026-01-05T13:45:00`, fractional seconds allowed). Values with a time zone (`Z` or `+02:00`) stay text. Ambiguous dates such as `01/02/2026` stay text, and the warnings name the column. ### Output Each output file is saved to the run's key-value store. The run's dataset holds one short report: ```json { "operation": "consolidate", "outputFormat": "xlsx", "filesIn": 3, "filesLoaded": 3, "sheetsIn": 3, "rowsIn": 9, "rowsOut": 9, "outputs": [{ "key": "consolidated.xlsx", "url": "https://api.apify.com/v2/key-value-stores/.../records/consolidated.xlsx", "rows": 9 }], "warnings": [], "chargedEvents": { "consolidate-run": 1 }, "stoppedAtChargeLimit": false } ``` The report never contains row data, only counts, up to 20 warnings, and download URLs. A file that cannot be downloaded or read is a warning; the other files are still processed. ### Use it from Claude Add Apify's hosted MCP server with this Actor as a tool: ``` https://mcp.apify.com?tools=dws_build/dws-spreadsheet-premium ``` Claude can then run it with file URLs and read the download links from the report. Files on your own computer need to be at a URL first (for example uploaded to cloud storage). For single files on your own machine (reading, searching, filtering, writing cells), the free [DWS Spreadsheet MCP](https://github.com/eschaq/excel-mcp-server) runs locally and needs no upload. ### Pricing Pay per event: | Event | When it is charged | |---|---| | Consolidate run | Once, after the combined file is saved | | Pivot run | Once, after the pivot file is saved | | File processed | Once per input file in convert, rename, or format runs, after that file's output is saved | Nothing is charged for input errors, files that fail to download or parse, or work that did not finish. If a run reaches your charge limit, it stops before downloading the next file, reports what finished, and exits normally. # Actor input Schema ## `operation` (type: `string`): What to do with the files. consolidate: combine sheets into one. pivot: group and aggregate across all sheets. convert, rename_columns, format: apply to each file separately; in any of these three, Rename columns and Format options both apply when filled in (rename first, then format). ## `files` (type: `array`): Files to process (.xlsx, .xlsm, .xls, .csv), as direct-download links. Each item is {"url": "https://...", "name": "optional label"} or {"base64": "...", "name": "jan.csv"}. Up to 50 files and 30 MB in total. Base64 is limited to 5 MB per file and 10 MB per run. Example: \[{"url": "https://example.com/jan.xlsx"}, {"url": "https://example.com/feb.csv", "name": "February"}]. ## `sheets` (type: `array`): Sheet names to read from each workbook. Leave empty to read all sheets. A CSV is one sheet named after the file. ## `outputFormat` (type: `string`): File format of every output, for all operations (convert uses it as the target format). json is an array of row objects per sheet. The format operation requires xlsx. ## `consolidate` (type: `object`): Used when operation is consolidate. stack_rows appends rows and aligns columns by header name. merge_on_key joins sheets side by side on a key column. ## `pivot` (type: `object`): Used when operation is pivot. groupBy: key columns. values: numeric columns to aggregate. aggregations: any of sum, count, mean, min, max. ## `renameColumns` (type: `object`): A map of old header name to new header name, applied to every file, such as {"Cust ID": "customer_id"}. Required for rename_columns. Also applies in convert and format runs when filled in. ## `format` (type: `object`): Excel formatting applied to every file: boldHeader, freezeHeader, autoWidth (true or false, all default to true), and columnFormats, a map of column name to Excel number format, such as {"Revenue": "#,##0.00", "Date": "yyyy-mm-dd"}. Used by the format operation, and also by convert and rename_columns when filled in. Needs xlsx output. ## Actor input object example ```json { "operation": "consolidate", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/north.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/south.xlsx" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/west.xls" } ], "outputFormat": "xlsx", "consolidate": { "strategy": "stack_rows", "addSourceColumn": true }, "pivot": { "groupBy": [ "Region" ], "values": [ "Revenue" ], "aggregations": [ "sum" ] } } ``` # Actor output Schema ## `report` (type: `string`): No description ## `files` (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 = { "operation": "consolidate", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/north.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/south.xlsx" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/west.xls" } ], "consolidate": { "strategy": "stack_rows", "addSourceColumn": true }, "pivot": { "groupBy": [ "Region" ], "values": [ "Revenue" ], "aggregations": [ "sum" ] } }; // Run the Actor and wait for it to finish const run = await client.actor("dws_build/excel-csv-merger-consolidate-pivot-batch-convert").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 = { "operation": "consolidate", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/north.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/south.xlsx" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/west.xls" }, ], "consolidate": { "strategy": "stack_rows", "addSourceColumn": True, }, "pivot": { "groupBy": ["Region"], "values": ["Revenue"], "aggregations": ["sum"], }, } # Run the Actor and wait for it to finish run = client.actor("dws_build/excel-csv-merger-consolidate-pivot-batch-convert").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 '{ "operation": "consolidate", "files": [ { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/north.csv" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/south.xlsx" }, { "url": "https://raw.githubusercontent.com/eschaq/dws-spreadsheet-samples/main/west.xls" } ], "consolidate": { "strategy": "stack_rows", "addSourceColumn": true }, "pivot": { "groupBy": [ "Region" ], "values": [ "Revenue" ], "aggregations": [ "sum" ] } }' | apify call dws_build/excel-csv-merger-consolidate-pivot-batch-convert --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "type": "http", "url": "https://mcp.apify.com/?tools=fetch-actor-details,dws_build/excel-csv-merger-consolidate-pivot-batch-convert" } } } ``` 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/w7HU1ArfcBQuJFH3h/builds/Q2cgu3Bu8y2etHcsa/openapi.json