User guide

Joining and appending files

Combine two open files by matching a column, or stack one under the other.

Opening the Join panel

Click Join in the toolbar to start. You can also press Cmd+K (Ctrl+K on Windows and Linux) and type ‘join’. You need at least two files open in tabs to use this feature.

Picking files and keys

Pick a source file and a target file from the dropdowns. Then pick the column to match on in each file. BigXL guesses a likely pair automatically. It looks for exact name matches first. Then it looks for close matches like ‘Order ID’ against ‘order_id’. Finally it looks for anything that looks like an id column. You can change the suggested columns manually using the dropdowns.

Join types

You can choose how to combine the files based on what rows you want to keep.

Only rows matching in both (Inner join) keeps rows whose key exists on both sides. Example: joining employees.csv with salaries.csv to see only employees who have a salary record.

Keep rows from the left (Left join) keeps every row from the source file. It fills in blanks where the target has no match. Example: keeping all employees and showing their salary if one exists.

Keep rows from the right (Right join) works the same way but from the target file’s side.

Keep rows from both (Full outer join) keeps every row from either side. It fills in blanks where there is no match on the other side.

Preview before confirming

The panel shows a preview before you confirm. It tells you how many rows matched. It also shows how many exist only on the left and how many exist only on the right. You can see a preview of what the combined table will look like.

Duplicate key warning

A key value might appear more than once on the target side. If this happens, the join multiplies rows. BigXL warns you about this before you confirm. This helps you avoid accidentally creating a file with millions of extra rows.

Appending rows instead of joining

Choose Append rows from both files when you want to stack one file under another. This is useful for combining twelve months of the same export format. You do not match columns for this. Columns line up by name automatically. A column that only exists in one file is filled in as blank for the rows from the other.

Comparing instead of combining

The same panel offers Compare the two files, which answers a different question: not how to merge them, but what changed between them. Use it for two versions of the same export where you need the rows that are new, gone or different. See Comparing two files.

The result

Joining or appending creates a new tab named after both files. It looks like employees_salaries.csv. Neither source file is touched. The result behaves like any other file. You can filter, sort, chart, and export it.

Matching names that are spelled differently

A normal join needs the two keys to be identical. Real lists rarely are: your payments say ACME, Inc. while the vendor list says Acme Inc, or one file has a typo. Open Match names against another file from the Data menu, or press Cmd+K and type “match names”. Open both files in tabs first.

Pick the other file, the name column in this file and the name column in the other. BigXL compares the different names, not every row, so it stays quick on large files. A name matches when it is:

• exact, written the same way in both files,
• same after tidying, differing only in capitals, spacing, punctuation or word order, or
• close, a small typo such as Meridian Freigt for Meridian Freight.

Names that hold different numbers never match, and a short name is never guessed at. The panel says how many rows match and lists examples of the names that did not match exactly, so you can judge the quality before making anything.

The result opens in a new tab. Every row of your file is kept, with a match column saying how each one matched, a column showing the name it was matched to, and the other file’s columns. Filter the match column to “close, check” to review the doubtful ones. Rows with no match are left blank rather than guessed. If the other file has several rows for one name, each of them appears, just as in a join. Neither file is changed.

Match names is part of BigXL PRO.