Look Up

"Look Up" looks up a key, usually expressed with a macro, against a text file filled with one column of keys and up to five columns of values.

For example, given a file name of "report56.txt" (in the Lookup_Key parameter) and the path to a file containing the following keys and values (in the Lookup_FilePath parameter), the Look Up built-in script would return a value of "customer13.rpt" (in the Lookup_Value parameter). To obtain this Lookup_Value, the Look Up built-in script would compare Lookup_Key to the "file keys" and return the related value from the first matching line.

report44.txt,customer01.ttk

report56.txt,customer13.rpt

report66.txt,customer87.lml

Input Parameters

• LookUp_Key (Required) - The value that needs to be looked up.
• LookUp_FilePath (Required) - Specifies where the "lookup file" containing delimited keys and values may be found.
• LookUp_NumValues - Specifies the number of value columns that the script should attempt to look for within the lookup table. The default is 5 value columns.
• LookUp_CaseSensitive (Required) - Specifies whether or not key comparisons are case-sensitive or not. (The default value is "No".) This parameter applies no matter which value of LookUp_MatchType is selected.
• LookUp_Delimiter - Specifies an alternate column delimiter. (The default value is a comma, i.e., ",".) For example, if a single line of keys and values looked like "753|blue|large", this parameter would need to be set to a vertical bar, i.e., "|".
• LookUp_CommentChar - Specifies an alternate character to indicate a line is a comment line, not a data line. (The default value is a single quote, i.e., "'".) For example, if a single line of comment looked like "# This is a comment", this parameter would need to be set to a pound sign, i.e., "#".
• LookUp_ActionIfKeyNotMatched (Required) - Specifies what to do if LookUp_Key does not match any values in the table. There are four possible values if no match is found for LookUp_Key.
  • Throw_Error (Default) - This task run and/or file transfer will end with an error (#120).
  • Ignore_File - This task run and/or file transfer will end with an "ignore this file" status code. Normally, you should only use this option if your Lookup script has been set to run "per file" so individual files can be ignored.
  • Return_Key_in_Values - This task run and/or file transfer will continue without error and the value of LookUp_Key will be copied into all the LookUp_Value* parameters.
  • Return_Blank_Values - This task run and/or file transfer will continue without error and the LookUp_Value* parameters will be set to a blank value.
• LookUp_MatchType (Required) - Specifies how LookUp_Key should be looked up against keys in the file table. There are three possible values. The term "file key" refers to the values in column 1 in the lookup file.
  • Require_Exact_Match (Default) - This means that LookUp_Key must exactly match keys in the file. Example: LookUp_Key "dog.txt" will match file key "dog.txt", but not file key "do". Also, LookUp_Key "do" will not match file key "dog.txt".
  • Allow_Partial_Match_of_LookUp_Key - This means that LookUp_Key will be treated as a "partial key" and will match file keys if they appear anywhere in the file keys. Example: A LookUp_Key of "dog.txt" or "dog" will match file key "dog.txt", but not file key "do". Wildcards are permitted (see below).
  • Allow_Partial_Match_of_File_Keys - This means that file keys will be treated as "partial keys" and will match LookUp_Key if they appear anywhere in the lookup key. Example: LookUp_Key "dog" will match file key "do", but not file key "dog.txt". Wildcards are permitted (see below).
• LookUp_ReturnAs - Optional, alternate name for LookUp_Value return value. For example, if LookUp_ReturnAs="AltFolder" and LookUp_Value="\another\fol", then the macro "[Parm:AltFolder]" will be interpreted as "\another\fol" during the rest of the task run.

Wildcards

Lookup keys may contain wildcards, except when "Require_Exact_Match" is in effect. The wildcard syntax is similar to Windows filename masks, with some enhancements:

Some examples:

Output Parameters

• LookUp_Value - Value found by looking up LookUp_Key. If there are multiple data columns in the lookup table, this is the value from the first column. If LookUp_Key was not matched, this value is controlled by the LookUp_MatchType parameter.
• LookUp_Value2 - Similar to LookUp_Value, except from data column 2, if available.
• LookUp_Value3 - Similar to LookUp_Value, except from data column 3, if available.
• LookUp_Value4 - Similar to LookUp_Value, except from data column 4, if available.
• LookUp_Value5 - Similar to LookUp_Value, except from data column 5, if available.
• LookUp_ValueN - Similar to LookUp_Value, except from data column N, if available.
• (Value of LookUp_ReturnAs) - If a value has been provided in the LookUp_ReturnAs parameter, the value of LookUp_Value will also be returned as a parameter bearing that name. For example, if LookUp_ReturnAs="AltFolder" and LookUp_Value="\another\fol", then the macro "[Parm:AltFolder]" will be interpreted as "\another\fol" during the rest of the task run.

Notes

In most production cases, you will probably use macros such as "[OrigName]", "[OnlyName]", "[RelativePath]" or "[MID([OrigName], 2, 3)]" in your "LookUp_Key" parameter.

This built-in script may be run wherever processes are allowed, including alone in its own task.

Example #1

Fred would like look up a specific internal folder based on the username that uploaded a file. If a record for a particular username cannot be found, an error should be logged.

To perform this operation with MOVEit Central, Fred should...

1. Create a "lookup table" file containing content similar to the following and save it as "d:\customer2folder\fred.txt"

   ' Format is username,folder

   jack,D:\blue\2134

   diane,D:\red\3734

   american,D:\blue\3357

   kids,D:\red\1651

   heartland,D:\red\2162

2. Create a new task with a source, process, destination, and schedule.
3. Select "Look Up" as his process.
4. Set process parameters:
   • LookUp_Key = "[OrigUser]"
     (Username of user who uploaded the file; only works on MOVEit DMZ sources.)
   • LookUp_FilePath = "d:\customer2folder\fred.txt"
   • LookUp_CaseSensitive = "No"
   • LookUp_MatchType = "Require_Exact_Match"
   • LookUp_ActionIfKeyNotMatched = "Throw_Error"
5. Configure his destination to:
   • use the macro "[Parm:LookUp_Value]" in the "Path" field.

Given the contents of "fred.txt" displayed above, the following uploader usernames would cause the following values to be placed into the LookUp_Value parameter.

Example #2

Nancy would like to change the names that several of her files are saved as, but many file names will already bear the correct names.

To perform this operation with MOVEit Central, Nancy should...

1. Create a "lookup table" file containing content similar to the following and save it as "d:\correctoddfiles\nancy.txt"

   ' Format is incoming filename, corrected filename

   JHJ45KK,nice_J45.dat

   JTE_KTR_67,nice_K67.dat

   K_P0KX_R89,nice_L89.dat

2. Create a new task with a source, process, destination, and schedule.
3. Select "Look Up" as his process.
4. Set process parameters:
   • LookUp_Key = "[OrigName]"
   • LookUp_FilePath = "d:\correctoddfiles\nancy.txt"
   • LookUp_CaseSensitive = "Yes"
   • LookUp_MatchType = "Require_Exact_Match"
   • LookUp_ActionIfKeyNotMatched = "Return_Key_in_Values"
     (If there is no match, let the original file name "fall through".)
5. Configure her destination to:
   • use the macro "[Parm:LookUp_Value]" in the "Filename" field.

Given the contents of "nancy.txt" displayed above, the following filenames would cause the following values to be placed into the LookUp_Value parameter.

Example #3

Ed would like to set FTP mainframe parameters based on the names of files MOVEit Central has just downloaded. He wants to be able to handle both specific filenames (e.g., "four.txt"), general extension (e.g., ".txt") and provide a "catch-all" value.

To perform this operation with MOVEit Central, Ed should...

1. Create a "lookup table" file containing content similar to the following and save it as "d:\blocking\ed.txt"

   ' List specific filenames first

   four.txt,80,4

   ' Next, list specific extensions

   .txt,80,10

   .dat,133,5

   ' Finally, provide a catch-all case

   ' (this assumes all incoming filenames will contain a period)

   .,80,10

2. Create a new task with a source, process, destination, and schedule.
3. Select "Look Up" as his process.
4. Set process parameters:
   • LookUp_Key = "[OrigName]"
   • LookUp_FilePath = "d:\blocking\ed.txt"
   • LookUp_CaseSensitive = "No"
   • LookUp_MatchType = "Allow_Partial_Match_of_File_Keys"
     (This will allow "five.txt" to match the ".txt" file record.)
   • LookUp_ActionIfKeyNotMatched = "Return_Blank_Values"
5. Configure his FTP destination to:
   • use the macros "[Parm:LookUp_Value]" and "[Parm:LookUp_Value]" in the "additional commands to execute before transfer" field.

Given the contents of "ed.txt" displayed above, the following source file names would cause the following values to be placed into the LookUp_Value and LookUp_Value2 parameters.

Example #4

Ralph would like to scan an entire folder structure and only transfer files that match names in his lookup table file. Files that do match should usually be renamed.

To perform this operation with MOVEit Central, Ralph should...

1. Create a "lookup table" file containing content similar to the following and save it as "d:\onlysome\ralph.txt"

   ' List specific filenames first

   ur.txt,rrr[OnlyName].xtx

   ' Next, list specific extensions

   .txt,[OrigName]

   .dat,[OnlyName].tad

2. Create a new task with a source, process, destination, and schedule.
3. Select "Look Up" as his process.
4. Set process parameters:
   • LookUp_Key = "[OrigName]"
   • LookUp_FilePath = "d:\onlysome\ralph.txt"
   • LookUp_CaseSensitive = "No"
   • LookUp_MatchType = "Allow_Partial_Match_of_File_Keys"
     (This will allow "five.txt" to match the ".txt" file record.)
   • LookUp_ActionIfKeyNotMatched = "Ignore_Files"
5. Configure his destination to:
   • use the macro "[Parm:LookUp_Value]" in the destination "filename" field.

Given the contents of "ralph.txt" displayed above, the following source file names would cause the following files to appear on the destination with the following names.