Catalytic is now PagerDuty Workflow Automation

CSV: Update file with another file

Use this action to merge two CSV files into a new CSV. The action includes configurations that can limit which rows are added, how data is overwritten.

This action functions similarly to a SQL Join query, and can merge two distinct datasets that share a related column. This means you can merge to CSVs and add all the columns from both files into one file.

screen readers look here

Where two rows share the same Key column, like a Customer Reference Number, or Employee Number, the rows are combined together, providing a more complete look at your data.

Use case

If you use multiple systems, chances are each system records data independently. Use this action similarly to a SQL Join query to relate the data together based on a shared value.

  • Join the data from all retail locations based on SKU number to get a complete look at each SKU’s performance.
  • Combine a data table of support tickets with another table that lists all error codes to create a single CSV that lists tickets and error code details.

How to configure this action

This action updates and joins a Master file with an inbound file so long as there is a match between the Key column. The Key column is any column each CSV shares.

screen readers look here

More information on key columns:

  • If two CSV’s have no columns shared between them, they cannot be joined with this action.
  • You can only set a single key column.
  • If the master file key column has duplicate entries, only the first entry is matched to the inbound file. To match every row, every row must be unique.
    • For example, if the UserID 1234 appears in 2 rows in the master file, and 1 row in the inbound file, only the first instance of UserID 1234 is updated in the master file.

💡   Tip: Try using the CSV: Apply formulas to columns action to easily add a row index to each row in a CSV. In some use cases, the row index is a great Key column.

Fields for this action

  • Master file

    • Use a field reference to choose the master CSV file to update. The action compares the inbound file to the master file.
  • Inbound file

    • Use a field reference to choose the CSV file to compare to the master file.
  • Key column

    • Enter the name of the column each file shares. In the example above Row ID is the key column.
  • Add new rows?

    • Select True or False from the drop down to choose whether the action should add any new rows.
      • Select True to add any new Inbound file rows to the Master file.
        • In the example above, the row where Row ID is 4 is a new row—it does not exist in the Master file. Enter True to add this row.
      • Select False to not add this row.
  • Update existing rows?

    • Select True or False from the drop down depending on if a master file row cell should be updated when a matching inbound file cell has new data.
      • Select True to update a master file cell if a cell in the inbound file has new data.
        • For example, where a Master file row has a Start Date of 2018-09-12 and the matching Inbound file row also has a Start Date but with the value 2019-02-30, the cell is updated with the new Inbound file value 2019-02-30.
      • Select False to never update an existing cell, even if a cell from a matched row has new data.
  • Delete rows if not in inbound file?

    • Select True or False from the drop down depending on if master file rows should be deleted when an inbound file does not include the row.
      • Select True to delete master rows when the inbound file has no matching rows.
        • For example, where the Key Column is Row ID: If the Master file has a Row ID of 1, 2,3, and the inbound file has a Row ID of 2, and 3, the master row 1 would be deleted.
      • Select False to never delete an existing row, even if the inbound file does not include the row.
  • Update with null?

    • Select True or False from the drop down depending on if master file cells should be updated when a cell from matched inbound file row is null.
      • Select True to update a cell when a matching row from an inbound file has a null cell.
      • Select False to never update an existing row, even if the inbound file row has a null cell.
  • Header rows

    • Select True or False from the drop down to set if a header row exists.
      • Select True if the first row of the files include column headers.
      • Select False to include the first row if it is not a header row.
  • Output field name

    • Enter a name for the field in which the result should be saved.
    • The step’s name is used as the output field name by default.

Action configuration examples

Each following example includes a diagram to explain how different configurations change how the files are joined, and an explanation of the result. For all examples, the Row ID is the Key column.

screen readers look here
The above Master file and Inbound file are used for all examples.
Merge matched rows and keep all rows

This configuration is the most commonly used. Two files are merged even if there are unmatched rows.

screen readers look here
  • All new inbound rows are added
  • No rows are deleted from the master file
  • Master file cells are updated if the same cell in the inbound row has a new value
Merge matched rows, delete unmatched master rows, add new inbound rows

This configuration removes rows if they are not included in the Inbound file. Use this configuration to remove rows from the Master file that have also been removed from the Inbound file.

screen readers look here
  • All new inbound rows are added into the merged file
  • When a master file row does not exist in the inbound file, the row is deleted
  • Master file cells are updated if the same cell in the inbound row has a new value
Merge matched rows, keep unmatched master rows, do not add new inbound rows

This configuration does not add any Inbound file rows not also in the Master file. This is useful when the Inbound file may contain row entries that should not be added to the Master file.

screen readers look here
  • Only matched inbound rows are added
  • No rows are deleted from the Master file
  • Master file cells are updated if the same cell in the inbound row has a new value
Merge matched rows, delete unmatched master rows, do not add new inbound rows

This configuration only joins rows that exist in the Master and Inbound file. This does not add unmatched rows. This is useful when all unmatched rows in both files should be deleted.

screen readers look here
  • Only matched inbound rows are added
  • When a master file row does not exist in the inbound file, the row is deleted
  • Master file cells are updated if the same cell in the inbound row has a new value

What will this output?

Since row formulas may automatically replace data, a new CSV is always created. This action will never edit the Master of Inbound CSV files.

This action will output 3 unique CSV files:

  1. A new CSV file with the updated CSV
  2. A new CSV file that lists all rows in master, but not in the inbound file
  3. A new CSV file that lists all rows in the inbound file, but not in the master

The name of the output field where the updated CSV is stored will be the Output field name defined during configuration.

Output fields for this action

  • Output field name

    • The merged CSV file. The file name and extension is: Merged.csv.
  • Number of updated rows

    • The number of rows where an original value from the Master File was replaced.
  • Number of added rows

    • The number of rows added to the Master File.
  • Number of deleted rows

    • The number of rows removed from the Master File.
  • Rows in master but not in inbound

    • A CSV file with all the rows removed from the Master File. The file name and extension is: Deleted_rows.csv.
  • Rows in inbound but not master

    • A CSV file with all the new rows added to the Master File. The file name and extension is: Added_rows.csv.

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.