This project has moved. For the latest updates, please go here.

Delimited text file lookup

Summary

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.

Parameters

Parameter Description
File The full path to the delimited text file
File type Specifies the type of delimiter used in the text file. ACMA 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

Input Type

The transform accepts binary, string, integer and Boolean data types upon input. Binary values must be base64 encoded in the text file.

Multiple input values

The transform accepts multiple input values, and processes each of them individually

Return Type

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.

Usage

Sample CSV file

#Mapping file for job code to job title
JobCode,JobTitle
1001,Receptionist
1002,Manager
1003,Marketing Executive

Parameters

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

Last edited Feb 22, 2015 at 1:33 AM by ryannewington, version 1