Merge CSV files – also when the columns differ

Same columns or not

Merging files that share a header is a simple stack: the rows of the second file go below the rows of the first. The harder, and more common, case is files that almost match – one has an extra column, another calls it “E-mail address” where the rest say “email”, a third lists the columns in another order. Simple mergers that append lines blindly then produce rows that no longer line up.

The Merge step matches columns by name, ignoring case, spaces and punctuation, so “Email”, “E-mail” and “e mail” are one column. The result can contain all columns of all files (cells are left empty where a file has no such column), only the columns every file shares, or only the source’s columns. For names that differ more, add synonym groups, one per line: email = mail address. You can match by position instead of name when the files have no meaningful headers.

What you are told

For each file the studio notes how many result columns it lacked and how many of its columns did not make it into the result. Add “a column with the file name” if you need to know which row came from which file – useful for monthly exports.

Limits

Every file must have a header row as its first line, and files are read with automatic settings (they can be of different encodings and delimiters). Merging stacks rows; to add columns from another file by a key, use join on a column. After merging, remove duplicate rows is often the next step.