Catalytic is now PagerDuty Workflow Automation

CSV: Lookup Data in Columns

🕙  4 minute read

Use this action to search a column in an CSV file for matching data and return data from other columns in the same row.

This action makes it easy to find data in a table by row, like looking up the price of product by the product name.

Use Case

  • Find matching data or names between two spreadsheets
  • Collect information from specific cells

How to configure this action

This action searches a Lookup column for a Search term. If the action finds a match, it returns data from that row based on the Columns to return.

For example, to look up the Quantity and Vendor ID for Apples from a table full of fruit types, we need to search the Fruit column for the Apple row and return data from that row for the Quantity and Vendor ID columns.

Fruit Price Quantity Vendor ID
Apple 10 5 ABC
Banana 40 8 DEF
Pear 20 2 GHI

For example, to find the price and quantity of an Apple from the table above:

  1. Set the Lookup column to 1, the first column.
  2. Set the Search term to Apple, the column value we’re looking for.
  3. Set the Columns to return to 3,4, to return the third and fourth columns, Quantity and Vendor ID.
  4. Set the Return field name to apple lookup to add a prefix to the output fields.
  5. The output of this action would be two fields; the first field is named apple-lookup--quantity and has the value 5 and the second field is named apple-lookup--vendor-id and has the value ABC.

💡   Tip: The correct way to reference columns in the lookup column and columns to return fields depends on the Column reference type.

Fields for this action

  • CSV file

    • This is the CSV file you want to filter. Use a field reference to a file uploaded in a prior task or instance
  • Header row number

    • Enter the row number that contains the names of the columns. Leave blank if the file does not have a header row.
    • ✅   Heads-up: If blank, columns can only be referenced by Excel or column order reference style.
  • Column Reference Type

    • Enter the column reference type to use during configuration; Column Name, Excel, or Column Order. Subsequent fields must be configured according to the column reference type you choose. There are three reference types:
      • Column name: If a header row is set, you can reference columns directly by their name in {{column-name}} format.
      • Excel: You can reference columns by their Excel letter, such as A, B, C
      • Column order: You can reference columns by their order, such as 1, 2, 3.
    • If Header row number is not specified, only Excel or Column Order references can be used.
  • Lookup column

    • Enter the column number to search through using the search term. For example, if the search term is Apple, the action will search this column for that term. Enter the column to lookup using Column Names, Excel style references (A, B, C, etc) or column order (1, 2, 3, etc).
  • Search term

    • Enter the term to search the lookup column name for. For example. if the lookup column contains fruit names, enter the fruit to search for here.
      • By default, the action performs an exact match using the term. To use REGEX, set the Advanced search type field to REGEX and enter the expression in this field.
  • Columns to return

    • Select which columns to return from matched rows.
      • To return specific columns, input column references as a comma-delimited list in any order. Use the column reference type specificed above.
      • Leave blank to return all columns.
  • Advanced search type

    • Enter the type of search.
      • Leave empty to perform an exact match search on the column that uses the Search term field value.
      • Enter REGEX to perform a REGEX search on the column that uses the Search term field value as the regular expression.
  • Return field name

    • Enter the name for the field to store the returned data. The name you specify will become the output field prefix for each field.
    • Leave blank to omit the prefix—returned fields will have the same names as the column names.

What will this output?

This action will output all matching fields chosen as Columns to return. The name of each output field will depend on the column reference type,

  • If using the Column Order reference type, the returned fields will be column1, column2, etc.
  • If using the Excel reference type, the returned fields will be columnA, columnB, etc.
  • If using the Column name reference type, the returned fields will be the header row names.

If a Return field name is defined during configuration, the value is added as an output field prefix.

Output fields for this action

  • Return field name (2nd, 3rd, etc.)

    • Each output field will store the result from the column defined during configuration. If no match is found, or there is no data in the row, the field will be blank.

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.