Look Up

Look Up looks up a key, usually expressed with a macro, against a text file that is 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 returns a value of "customer13.rpt" (in the Lookup_Value parameter). To obtain this Lookup_Value, the Look Up built-in script compares Lookup_Key to the "file keys" and returns 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 to look up.
• LookUp_FilePath (Required) - Specifies the path where the lookup file containing delimited keys and values is located.
• LookUp_NumValues - The number of value columns that the script looks for within the lookup table. Default is 5 value columns.
• LookUp_CaseSensitive (Required) - Specifies whether key comparisons are case-sensitive. Default is No. This parameter applies for all values value of LookUp_MatchType.
• LookUp_Delimiter - Specifies an alternate column delimiter. Default value is a comma (,). For example, if a single line of keys and values is "753|blue|large", set this parameter to a vertical bar (|).
• LookUp_CommentChar - The alternate character to indicate that a line is a comment line, not a data line. Default is a single quote ('). For example, if a single line of comment is "# This is a comment", set this parameter to a pound (hash) sign (#).
• LookUp_ActionIfKeyNotMatched (Required) - Specifies the action to take if LookUp_Key does not match any values in the table. Values:
  • Throw_Error (Default) - End this task run and/or file transfer with an error (#120).
  • Ignore_File - End this task run and/or file transfer with an "ignore this file" status code. Normally, use this option only if your Lookup script has been set to run "per file" so individual files can be ignored.
  • Return_Key_in_Values - Continue this task run and/or file transfer without error, and copy the value of LookUp_Key into all the LookUp_Value* parameters.
  • Return_Blank_Values - Continue this task run and/or file transfer without error and set the LookUp_Value* parameters to a blank value.
• LookUp_MatchType (Required) - Specifies how LookUp_Key should be looked up against keys in the file table. The term "file key" refers to the values in column 1 in the lookup file. Values:
  • Require_Exact_Match (Default) - LookUp_Key must exactly match keys in the file. Examples:

    LookUp_Key "dog.txt" matches file key "dog.txt", but does not match the file key "do".

    LookUp_Key "do" does not match file key "dog.txt".

  • Allow_Partial_Match_of_LookUp_Key - LookUp_Key is treated as a "partial key" and matches file keys if they appear anywhere in the file keys. Wildcards are permitted (see list of wildcards below).

    Example: A LookUp_Key of "dog.txt" or "dog" matches file key "dog.txt", but does not match the file key "do".

  • Allow_Partial_Match_of_File_Keys - File keys are treated as "partial keys" and match LookUp_Key if they appear anywhere in the lookup key. Wildcards are permitted (see list of wildcards below).

    Example: LookUp_Key "dog" matches file key "do", but not file key "dog.txt".

• LookUp_ReturnAs - Optional, alternate name for LookUp_Value return value.

  Example: If LookUp_ReturnAs="AltFolder" and LookUp_Value="\another\fol", then the macro "[Parm:AltFolder]" is interpreted as "\another\fol" during the rest of the task run.

  Wildcards

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

  Wildcard char	Meaning
  *	Match zero or more of any character.
  ?	Match exactly one of any character.
  @	Match exactly one alphabetic character.
  #	Match exactly one digit.
  anything else	Match exactly that character.

  Examples:

  dog#	Matches "dog3" and "Mydog8acat". Does not match "dog" or "dogs3"
  dog*7	Matches "dog7", "dog37" and ")dog/7PP". Does not match "dog" or "do7".

  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 is also be returned as a parameter that has that name. For example, if LookUp_ReturnAs="AltFolder" and LookUp_Value="\another\fol", then the macro "[Parm:AltFolder]" is 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 can be run wherever processes are allowed, including alone in its own task.

  Example #1

  Fred wants to look up a specific internal folder based on the username that uploaded a file. If a record for a particular username cannot be found, log an error.

  To perform this operation with MOVEit Automation:

  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 the process.
  4. Set process parameters:
     • LookUp_Key = "[OrigUser]"
       (Username of user who uploaded the file; only works on MOVEit Transfer sources.)
     • LookUp_FilePath = "d:\customer2folder\fred.txt"
     • LookUp_CaseSensitive = "No"
     • LookUp_MatchType = "Require_Exact_Match"
     • LookUp_ActionIfKeyNotMatched = "Throw_Error"
  5. Configure the destination to:
     • use the macro "[Parm:LookUp_Value]" in the "Path" field.

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

  Uploader Username	LookUp_Value
  diane	D:\red\3734
  heartland	D:\red\2162
  congress	(NONE - ERROR)

  Example #2

  Nancy wants to change the names that several of her files are saved as, but many file names already have the correct names.

  To perform this operation with MOVEit Automation:

  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 the 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 the destination to:
     • use the macro "[Parm:LookUp_Value]" in the "Filename" field.

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

  Source File Name	LookUp_Value
  JHJ45KK	nice_J45.dat
  hello.txt	hello.txt
  K_POKX_R89	nice_L89.dat
  K_pokx_R89	K_pokx_R89

  Example #3

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

  To perform this operation with MOVEit Automation:

  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 the 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 allows "five.txt" to match the ".txt" file record.)
     • LookUp_ActionIfKeyNotMatched = "Return_Blank_Values"
  5. Configure the 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 cause the following values to be placed into the LookUp_Value and LookUp_Value2 parameters.

  Source File Name	LookUp_Value	LookUp_Value2
  four.txt	80	4
  five.txt	80	10
  six.dat	133	5
  seven.rpt	80	10

  Example #4

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

  To perform this operation with MOVEit Automation:

  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 the 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 the 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 cause the following files to appear on the destination with the following names.

  Source File Name	Destination File Name
  four.txt	rrrfour.xtx
  five.txt	five.txt
  six.dat	six.tad
  seven.rpt	(file ignored)