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.

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:

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:

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

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.