CSV_KEY_LOOKUP

Parameters (String <list or value>, String <csv file path>, String key <column name> , String <value column name>);

Returns: String;

Function to perform a key-value pair mapping for a list of values using a csv file.
The first parameter is the input list. This should be a comma separated list of values or can be a single value.

If the list is NULL, NULL will be returned

The list can use double quotes to delimit values. The output from a lookup using the multi match option 'Concatenate CSV' is suitable. The function will also work for a single value as long as it does not contain commas, or if it does, that it is delimited by double quotes.

The second parameter is the path to the csv file that will be used to map the values in the list.

This must be the full path to a csv file.

The file must contain a header row as its first row.

The third parameter is the column name that will act as the key.

This must match one of the header column names. The values in this column should be unique.

The csv file will be searched to find the row where each value in the input list matches the value for this column.

The fourth parameter is the column name that will give the value.

This must match one of the header column names.

This function will return a comma separated list of the values matching the key values in the list.
The values in the list will be delimited with double quotes. If a match is not found for a key, it will not be added to the list. Therefore the returned list can be smaller in size than the input list.

Example:

The referenced csv file (C:\Temp\letters_to_numbers.csv) has letters A-Z in the 'letter' column and the numbers 1-26 in the 'number' column.

CSV_KEY_LOOKUP('\"A","B","C"', 'C:\Temp\letters_to_numbers.csv', 'letter', 'number') = '"1","2","3"'