%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
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:ID | Name | Rate |
---|---|---|
0 | VAT-free | 0 |
1 | Reduced VAT | 7 |
2 | Normal VAT | 19 |
3 | Luxus VAT | 25 |
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 |
- articles.csv %merge[VAT]=[ID]@vat-rates.csv 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 |
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
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 |