This function searches for the value of parameter a in the key column of a CSV or Excel file specified in parameter b. If a matching key is found, the function returns the replacement value that is stored in column c of the replacement file. If no match is found, the function returns the value specified in parameter d.

If you want to use an alternative key column (default is the first column), you can set it in parameter f. An alternative system encoding can be specified in parameter e. The CSV separating character is set in parameter g. After the parameter value is trimmed, the first character of the remaining characters is used. If you want to use TAB or SPACE, please set \TAB respectively \SPACE. If parameter b references an Excel file, parameter h can be used to set the name or number of the Excel sheet (index starts with 0).

Lines starting with # are comments. The CSV file has to be formally correct, i.e. if column n is referenced, all the data lines have to have n columns (n-1 separating characters). If that is not the case, the function execution will be aborted with an IndexOutOfBoundsException.

Important note

This function inherits the basic behaviour of function replace value(a, csv b, column c, default d, [encoding e, key column f, delimiter g]) and is capable of using CSV as well as Excel replacement files. Parameter h allows you to specify a particular Excel sheet. Calculations defined in Excel are executed.

Parameters

Parameter	Description
a	Search value.
b	Path and name of the CSV or Excel file.
c	Replacement column.
d	Default return value.
e	(optional) Encoding. Default: System encoding or 8859_1 if none found.
f	(optional) Alternative key column. Default: 1.
g	(optional) Column separator. Default: ,
h	(optional) Excel sheet. Default: 0.

Extraction of replacement file from ZIP file

The replacement file (CSV or Excel) can be extracted from a ZIP file. Parameter b expects the following syntax:

File path of ZIP file	Contained replacement file	Parameter b
./conf/xyz/replacement.zip	ordernumber.xlsx	./conf/xyz/replacement.zip!ordernumber.xlsx
./conf/xyz/replacement.zip	EN/ordernumber.csv	./conf/xyz/replacement.zip!EN/ordernumber.csv

This feature can be used to provide several interrelated replacement files in one ZIP file and enables a simultaneous substitution of all the replacement files.

Note: Please avoid whitespaces in file paths.