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:
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 102.csv
has the rows where quantity is 25.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
.
- If the action was able to fully segment the spreadsheet, the status will be
- This field will return the status of the action.
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.