CSV: Apply formulas to columns

Use this action to apply a formula to one or more columns for each row in a CSV file, and save the results to a new CSV file. Formulas are written in Javascript.

To apply formulas to a data table the same way, use the Tables: Apply formulas to columns action. CSVs process faster and support higher row limits.

This is an advanced action that requires Javascript. This article provides an overview of the Javascript in the row formula function, but doesn’t cover Javascript extensively.

How to configure this action

This action takes a Javascript formula from the row formula field and executes it for every row. For example, the table below has a Price and Quantity column, and two empty columns, Total and Formatted Total.

Fruit Price Quantity Total Formatted Total
Apple 10 5    
Banana 40 8    
Pears, Bananas 20 2    

Row formulas use basic Javascript notation to calculate new values based on rows and columns, like adding two values together, then saving the result to a column.

A simplified example of the formula notation is Destination column = Column A + Column B, like Total Column = Price + Quantity. All columns used in the formula must exist in the table; new columns can’t be added with this action. Here are some example row formulas:

Example row formula Row formula effect Result
columns['total'] = columns[2] * columns[3] Set the Total column to Price (col2) * Quantity (col3) 50
columns['Formatted Total'] = '$' + columns[2] * columns[3] + '.00' Set the Formatted Total column to Price (col2) * Quantity (col3) and prepend $ and append .00 $50.00
columns['total'] = columns['total'].replace(/,/ig, "") Remove all commas from the Total column. Pears Bananas
columns['total'] = columns[2] * columns[3];columns['Formatted Total'] = '$' + columns[2] * columns[3] + '.00 Update the Total and Formatted Total columns in one row formula 50, $50.00

Using the last example,

columns['total'] = columns[2] * columns[3];
columns['Formatted Total'] = '$' + columns[2] * columns[3] + '.00'

The entire Total and Formatted Total columns are set in one row formula, with the result of:

Fruit Price Quantity Total Formatted Total
Apple 10 5 50 $50.00
Banana 40 8 320 $320.00
Pears, Bananas 20 2 40 $40.00

Apply formulas to all columns

Be default, this action applies a formula to every row in a column. It’s possible to also apply a formula to every row in a column, for every column. This will effectively apply a formula to all data in the table.

To do so, start with the following formula template:

var i = 1
while (i < 100) {
    columns[i] = "X";
    i++;
}

Then, change "X" to adjust what happens to each column. For example, to replace all quotation marks in all columns, you could use this formula:

var i = 1
while (i < 100) {
    columns[i] = columns[i].replace(/“/g, ’’);
    i++;
}

Fields for this action

  • CSV file

    • Choose a CSV file you want to apply formulas to. Use a field reference to a file uploaded in a prior task or instance.
  • Header row

    • Select True or False from the drop down.
      • Select True if the file includes column header on the first row.
      • Select False to include the first row in the formula if the file does not have a header row.
  • Row formula

    • Enter the row formula to apply to the CSV column. Use a combination of columns, text, and fields.
    • Reference columns in a formula by their name columns['Column Name'] or order columns[1], with 1 being the first column.
    • To quickly add row numbers to every row in a CSV, see the Add row numbers to make joining tables easier below.

      💡   Tip: Use a JavaScript editor like https://repl.it/languages/javascript to write up the formula, then copy and paste the code over.

  • Output field name

    • Enter the name of the field in which the new CSV should be saved.

Add row numbers to make joining CSVs easier

Use the formula notation: Destination column = rowIndex to quickly assign row numbers to every row. Then, easily join CSVs by using a related column between them with the CSV: Update file with another file.

Using the syntax above, we add a row number to the column Row Number with the following formula:

columns['Row Number'] = rowIndex
Row Number Fruit Price
1 Apple 10
2 Banana 40
3 Pear 20

What will this output?

The expected output for this action is a new CSV file with any row formulas applied. The name of the output field will be the Output field name defined during configuration.

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

Output fields for this action

  • Output field name

    • A file field for the for the newly created file.

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.