CSV and Excel Merge, Dedupe and Clean Files

What it does

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

How to use it

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

Input

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

Output

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

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

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

became two dataset items:

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

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

Pricing

This Actor is priced per event, with two charges:

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

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

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

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

Your data

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

File links and robots.txt

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

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

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

Your responsibility

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

Run records

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

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

Limits

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