%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 togther into the output file (as long as they are not hidden, see also ±hide 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 is defined, the command will use a column of the additional file with the same name as this column. 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.

Usage examples

Say we have a table of VAT rates:

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

And there is another 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

Now we will 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	Name	Rate
12	Bread	0.75	1	bla	Reduced VAT	7
15	Milk	1.50	1	blub	Reduced VAT	7
18	Trousers	49.99	2	bar	Normal VAT	19
23	Golden Watch	2000.00	3	what	Luxus VAT	25
55	Pair of Shoes	99.90	2	brand	Normal VAT	19

Optionally:
Having done this, one can also calculate the gross price in an additional column, and then rearrange the columns 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