CSV: Create spreadsheet for each unique value

Use this action to filter an Excel XLSX or CSV spreadsheet by each unique value in a column, and create a new spreadsheet with all the rows for each unique value. The original spreadsheet will be unaffected, this action only creates new spreadsheets.

To split up a data table the same way, use the Tables: Create table for each unique value action.

Use case

This action can quickly split up a spreadsheet based on unique values. For example, you could split up a spreadsheet with regional sales information by the region column to create new spreadsheets for the EMEA, APAC, and AMER region.

This creates 3 new spreadsheets off of the 3 regions in the region column. In each spreadsheet, the EMEA, APAC, and AMER rows are distributed into their new regional spreadsheet.

How to configure this action

Given a spreadsheet with fruit type, price, and quantity, we will create new spreadsheets based on the quantity of each fruit, grouping the unique values into new spreadsheets:

Fruit Price Quantity
Apple 10 10
Banana 40 10
Pear 20 2
Orange 10 2
Mango 12 5
Durian 17 5
Coconut 11 5

Set the Column name to Quantity to segment the table based on quantity values.

The result will be a data table field with 3 rows for the 3 unique quantity values, 10, 2, and 5. Each row in the output table lists the unique value, number of rows where the value was the same, and the new table’s ID:

screen readers look here

Each .CSV in the Spreadsheet file column is a downloadable .CSV of each new spreadsheet. For example, file 10.csv is a separate spreadsheet with just the rows where the quantity was 10, and any row with a different value is not included in the this file.

  • 10.csv only has the rows where quantity is 10
  • 2.csv has the rows where quantity is 2
  • 5.csv where quantity is 5.

Fields for this action

  • Spreadsheet file

    • Identify the name of the previously uploaded file or use a reference field.
  • Header row number

    • Enter the row number that contains the names of the columns. Leave blank if the worksheet does not have a header row. If blank, all rows including the first row will be segmented.
  • Column name

    • Enter the column name or number to segment by each unique value. To use column numbers, numbering starts at 1.
    • The example above used the Quantity column to segment the table.

      💡   Tip: If no header row number is specified, columns must be referenced by number. Non-number values will be ignored.

  • Output field name

    • Enter the name of the field in which the result should be saved.
    • The step name is used by default.

What will this output?

This action will create new spreadsheets for each unique value in a data table. The action outputs a data table field that lists these unique table’s ID in a field with the output field name defined during configuration.

This action can segment a data table of up 10,000 rows. Any table with more than 10,000 rows will be partially segmented up to this limit. The excess rows will not be segmented.

Output fields for this action

  • Output field name

    • The output will be a data table field with a row for each unique value, the count of unique values, and the table ID where each their stored.
    • Unique Value Count Spreadsheet File
      A unique value The count of unique values moved to the new table A new downloadable CSV file.
  • Segmentation status

    • This field will return the status of the action.
      • If the action was able to fully segment the spreadsheet, the status will be Full.
      • If the limit is reached, the table is segmented up to the 10,000th row, the status will be Partial.

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.

Need more help?

If you're signed in to Catalytic Community, you can ask other users a question. You'll be redirected to Community where you can add more info.