%merge

Merges all fields from an additional CSV file to the current row.

Description

This command merges additional columns from another CSV file to the current row of the input file.

For each line of the input file, it uses a local and a remote key field, comparing both fields to find the correct data (CSV row) in the additional file. Then it appends that row's fields to the local data fields.

All "+"commands before the %merge command refer to the input file, while all "+"commands afterwards refer to the additionally merged file.
All "-" column or field commands will then refer to the combined input and merged fields in the output file. At last, all fields will then be written together into the output file (as long as they have not been removed afterwards, see also ±delete and ±columns).

Pattern

%merge=filename %merge[localfield]=filename %merge[localfield]=[remotefield]@filename

If all field names are omitted, the command will use the first column of the input file as the local key, and, if exists, a column with the same name in the additional file as the remote key.
If there is no column with an identical column name, the first column in the additional file will be compared with the first column of the input file.

If only the local key field or expression is defined, the command will use the identically named field or the same expression for the additional file. If such column does not exist, the first column of the additional file will be used as remote key.

If only the remote key is given, it will be compared to the first column of the input file.

If neither local nor remote key are defined, the first columns of each table will be used as key for matching.

Usage examples

Say we have a table of articles:

articles.csv
ID	Description	Price	VAT	foo
12	Bread	0.75	1	bla
15	Milk	1.50	1	blub
18	Trousers	49.99	2	bar
23	Golden Watch	2000.00	3	what
55	Pair of Shoes	99.90	2	brand

And there is another table about VAT rates:

vat-rates.csv
ID	Name	Rate
0	VAT free	0
1	Reduced VAT	7
2	Normal VAT	19
3	Luxus VAT	25

As we can see, the column VAT in table articles.csv matches with the column ID in table vat-rates.csv .

Using this, we can merge the respective VAT rates into the articles table:

articles.csv %merge[VAT]=[ID]@vat-rates.csv articles-with-vat.csv

This will be the resulting table:

articles-with-vat.csv
ID	Description	Price	VAT	foo	ID	Name	Rate
12	Bread	0.75	1	bla	1	Reduced VAT	7
15	Milk	1.50	1	blub	1	Reduced VAT	7
18	Trousers	49.99	2	bar	2	Normal VAT	19
23	Golden Watch	2000.00	3	what	3	Luxus VAT	25
55	Pair of Shoes	99.90	2	brand	2	Normal VAT	19

Optionally:
Having done this, one can also calculate the gross price in an additional column.
At last, the columns can be rearranged for a more concise article file, if desired.

articles.csv %merge[VAT]=[ID]@vat-rates.csv -add[GrossPrice]="{([Price]+[VAT]%).2}" -columns=ID,Description,Price,Rate,GrossPrice articles-with-vat.csv

This will be the resulting table:

articles-with-vat.csv
ID	Description	Price	Rate	GrossPrice
12	Bread	0.75	7	0.80
15	Milk	1.50	7	1.60
18	Trousers	49.99	19	59,49
23	Golden Watch	2000.00	25	2500.00
55	Pair of Shoes	99.90	19	118,88