-
Notifications
You must be signed in to change notification settings - Fork 2
Delimited text file lookup
This transform allows for the lookup of values in a delimited text file. The transform supports CSV and TSV files out of the box, but you can alternatively supply the transform with a custom delimiter to use to find the correct value.
Lookup transforms are used to find the incoming value in a table, and replace it with another value.
If the incoming value is not found in the lookup table, you can specify what you want the transform to return instead. You can choose between returning nothing, returning the original value, or a fixed default value
The transform supports looking up string, integer, and binary values. Binary values need to be represented in the text file in base-64 encoded format.
| Parameter | Description |
|---|---|
| File | The full path to the delimited text file |
| File type | Specifies the type of delimiter used in the text file. UMARE supports CSV and TSV files out of the box, but you can provide a custom delimiter if required |
| Custom delimiter regex | A regular expression pattern used to match the delimiters in the text file when using a custom delimiter type |
| Custom escape character | A character that is used to escape the delimiter when using a custom delimiter type |
| Ignore rows starting with | Allows comments to be supported in the file by specifying a character that will ignore the row if it is the first character found |
| File has a header row | Used to indicate the first row in the file should be ignored as it contains header/column names |
| Find input value in column # | Specifies the column that contains the incoming values in the transform. The first column is column 0 |
| Output value in column # | Specifies the column that contains the return value for the transform if a match is found in the input column |
| When no match is found | If the input value wasn't found in the file, you can return a specific value, return null, or return the original input value |
| Default value | When return a specific value is selected for the When no match is found parameter, and the input value wasn't found in the text file, this static value will be returned by the transform |
The transform accepts binary, string, integer and Boolean data types upon input. Binary values must be base64 encoded in the text file.
The transform accepts multiple input values, and processes each of them individually
The transform returns a string value for each input value found in the file, alternatively it no match is found, and return null is selected, then no value is returned.
#Mapping file for job code to job title
JobCode,JobTitle
1001,Receptionist
1002,Manager
1003,Marketing Executive
| Parameter name | Value |
|---|---|
| File type | Comma separated |
| Ignore rows starting with | # |
| File has a header row | True |
| Find input value in column | 0 |
| Output value in column | 1 |
| When no match is found | Use a specified value |
| Default value | Other |
| Input value | Output value |
|---|---|
| 1001 | Receptionist |
| 1004 | Other |
| 1002 | Manager |