Check CSV files before you merge them

Compare headers, widths and conflicting keys before concatenating extracts.

Merging is a decision you make before you concatenate

Stacking two CSV extracts with a text editor is easy to do and hard to undo. The second file’s header becomes a data row, a column that existed only on one side shifts every field after it, and a repeated catalog code looks like a duplicate you should delete. TableMender does not merge files. It has no multi-file picker, no column mapper, and no “union these headers” command. The work on this page is the inspection you do on each extract, and on an already stacked file, before you trust a combined table. The cleaning rules that do exist are the ones in the duplicate-key guide and the empty-record guide. They operate on one parsed table.

A safe merge needs the same delimiter, the same encoding, the same column meaning in the same order, and a key you can explain. “Same number of columns” is not enough. sku and product_id can both be the third field and still be different identifiers. A quantity on one extract and a price in the same position on the other will add without an error message in a spreadsheet. The preflight is how you notice that before the add.

product_id,warehouse,quantity
NM-14,North,12
NM-27,North,3
sku,warehouse,quantity
NM-14,South,8
NM-40,South,1

This synthetic stack pretends two warehouse extracts were pasted together. The first line is a real header. The fourth line is the second file’s header, copied in as if it were a product. With “first record is a header” on, you get five data records and three columns. One of those data records is sku, warehouse, quantity. Nothing in the parser marks it as a header. Headers are a choice you make about the first record only.

Run the checks on the stacked sample

1. Open CSV clean and load merge-stacked.csv with Comma and the header on. Leave every cleaning rule off. Confirm six source records including the header, which means five data records, three columns, and a data row whose first field is the word sku.
2. Use the filter box for sku and also for product_id. Filtering changes the view, not the file. You are looking for a second header that survived as data. Clear the filter before you judge counts.
3. Turn on duplicate removal with every column selected. Preview. No full row is a duplicate. The tool removes zero rows. A stacked file can be wrong and still contain no identical lines.
4. Switch the key to the named column product_id only, and keep the recommended policy. NM-14 appears as North/12 and South/8. That is one conflicting group. The recommended policy removes zero rows and keeps both quantities. Explicit keep-first removes the later NM-14 and discards the South quantity of 8. It also keeps the false header row, because sku is not a repeated key. Fewer rows are not a cleaner merge.
5. Turn duplicate removal off. Turn on empty-record removal. Nothing in this sample is an empty record, so the count stays at zero. A missing quantity would have been an empty cell, not an empty record, and would remain.

Preview one file before you stack another →

Then load merge-width-mismatch.csv in CSV check and repair or in the cleaner. The header and the first product have three fields. The last line has four: a late note was added on only one extract. The uneven record is source record 3. Download stays blocked for the workbook and for a “successful” export of a rectangular table. The cleaner can still show the rows. It must not quietly drop the note or invent an empty note for the other rows. Fix the source so every record has the same fields, with an explicit empty note if the note is part of the merge.

A checklist for files you have not stacked yet

Do this on each extract, not only on the pile.

• Delimiter. One file of commas and one file of semicolons will not share a column edge. The locale guide shows a semicolon file whose decimal commas are data. Detect on each file. If the score is uncertain, choose the delimiter yourself and read a quoted field.
• Encoding. A leading byte order mark can make the first header product_id on one file and the same letters with a hidden prefix on the other, so a later join fails. The mojibake guide separates a real signature from characters that were saved after a bad decode. Preview both headers as text before you align them.
• Header names and order. Write the column list down. product_id,warehouse,quantity is not the same schema as sku,warehouse,quantity and not the same as product_id,quantity,warehouse. Matching names in a different order will silently swap values if you concatenate by position.
• Width. A wider extract needs a decision for every extra column: add an empty field to the narrower file, or drop the extra column on purpose. Do not let the first data row of the wider file become the only row with a tail.
• Key meaning. If NM-14 in the North extract and NM-14 in the South extract are the same catalog item in two places, you want both rows. If they are two exports of one warehouse that should not overlap, a conflict is a data error, not a duplicate to auto-remove.
• Empty records and edge whitespace. Remove a fully empty record only with that rule on, after you have checked that a quoted line break is not the “blank line” you noticed in a text editor. Trim only if edge spaces are accidental. Internal spaces stay.
• Types you plan to apply later. Dates, long digit strings and leading zeros should still be text when the files meet. Convert types after the stack is rectangular and reviewed, not inside each extract under different settings.

Common errors compared

Edge cases

A repeated header is only obvious when its cells still say sku or product_id. If the second file had no header, you will not see that clue. You have to know the record count of each input. Five data rows from a two-row file and a three-row file need another explanation: a blank line, a second header, or a row that was not in either source. This tool’s record count includes the header you declared. Subtract one when you compare with a “data rows” number from the exporter.

An empty line between the extracts often becomes a one-field record. That width does not match a three-column header, so export blocks. It can also be a fully empty record of the right width if the line contains the right number of separators. Those are different. The empty-record rule removes a record only when every field is empty after an optional trim. A row with a warehouse and no quantity stays.

Quoted commas and quoted line breaks change physical line counts without changing the logical table. Do not use a programmer’s “split on newline and comma” one-off to stack files that still have quotes. Parse them. The quoting guide shows a note that occupies two visual lines and one field.

Case and whitespace: North and north are different strings unless you turn on ignore-case for the key, and ignore-case does not rewrite the stored letters. North and North differ unless you trim. Apply those options on purpose, then look at the change count. They are not implied by a merge.

Files under the size limits can still be the wrong files. The limits are 5 MiB, 20,000 records including the header, 256 columns and 250,000 cells. A preflight on a 20-row extract does not prove the full extract has a stable header. Repeat the width and header check on the real file. The sample only gives you a pattern.

What the tools will not stitch together

You cannot drop two files on the page and receive a union. You cannot map sku onto product_id by typing a rename rule. You cannot add North’s 12 to South’s 8. The duplicate review, when you enable it, lists conflicting groups so you can see both quantities. The review file is a UTF-8 audit with source record numbers. It is not the cleaned catalog, and it is not a merged workbook.

Export scope still matters. “Matching records” downloads the filter, which is useful when you want a list of stray header rows to inspect, and dangerous when you thought you were downloading the whole merge. Read the export summary before you save. Restore original preview turns cleaning rules off and rebuilds from the parsed source. It does not split a stacked file back into the two extracts. You cannot restore what the text editor already destroyed unless you still have those extracts.

Excel workbook export keeps text by default, which is what you want while identifiers and the false header are still under review. Choosing Number for quantity is reasonable only after the stray header is gone. The word quantity in that false row is not a plain decimal, so a numeric quantity column correctly blocks download while the stray header remains. That block is a useful alarm. Do not clear it by deleting the row without looking at which file it came from.

Limits

This preflight does not prove business identity. Two rows can share a product id and a warehouse and still be different lots, or two different ids can be the same item under a renamed catalog. The tool compares the strings you mark as a key. It does not consult a product master.

It does not detect a swapped column when both columns are the same kind of token. If both extracts use three text fields and you reverse warehouse and quantity, counts stay even and duplicates look plausible. Read values, not only widths. The sample’s late note is obvious because it changes the width. A silent swap is worse, and only a value check catches it.

Do not send real customer files to anyone as a debugging attachment. The samples are enough to rehearse the clicks. When a real extract fails a check, fix the export or edit a private copy you are allowed to change. The browser tool does not upload the file, and it also does not remember the preflight after you close the tab.

← All guides