Virtual Columns

Virtual Columns are one of CSVbox’s most powerful data transformation tools. They run small snippets of JavaScript that merge, split, re-format, correct, and enrich incoming data during an import. Use them to process your data, do calculations, or merge values together before the data reaches your end.

How it works

Add a virtual column

For any sheet (template), add a virtual column in the CSVbox dashboard.

Attach a JavaScript snippet

Attach a JavaScript snippet to the virtual column.

Users submit the CSV file

After users submit the CSV file, the JavaScript runs to populate data in the virtual columns.

Data is pushed to the destination

Data from the uploaded CSV and the virtual columns is pushed to the data destination.

Example

Merge
Re-format
Static

Merge data from the first_name and last_name columns into a new virtual column named full_name.Here is the JavaScript attached to the full_name virtual column:

try
{
	return csvbox.row["first_name"] + ' ' + csvbox.row["last_name"];
}
catch(err)
{
  	return 'error: ' + err.name + ' | ' + err.message;
}

Normalize the incoming order_date into UTC format.

try
{  	const date = new Date(csvbox.row["order_date"]);

	return date.toUTCString();
}
catch(err)
{
  	return 'error: ' + err.name + ' | ' + err.message;
}

Pass any static value to the virtual column.

try
{
	return 'Amazon';
}
catch(err)
{
  	return 'error: ' + err.name + ' | ' + err.message;
}

Adding virtual columns

Open the Columns tab

Go to the edit sheet page → Columns tab, and click the Add Virtual Column button.

Name the column

Add the virtual Column Name.

Provide the JavaScript code

Enter the Javascript code.

Attach dependencies (optional)

Attach Dependent libraries.

Save the column

Click Save.

Add Virtual Column dialog — Add Virtual Column

Data rows

Data rows are the core object that virtual columns are built around. For each incoming CSV data row, you can read its data and manipulate it with JavaScript to populate the virtual column. After the user submits the CSV, the JavaScript runs for each data row in sequential order to populate the virtual columns. Use the csvbox.row object to work with each row, and csvbox.row["column_name"] to read a specific column of the data row. You can use data from a single column, multiple columns, or no columns to populate virtual columns.

Inserting data into virtual columns

The value returned by the JavaScript snippet is added to the virtual column.

// Extracting first name
try
{  	var fullName = csvbox.row["full_name"].split(' ');

	// returning value
	return  fullName[0];
}
catch(err)
{
	//returning error
  	return 'error: ' + err.name + ' | ' + err.message;
}

If the JavaScript returns a NULL value, an empty string is added to the virtual column.

Receiving virtual data

Virtual column data is available at all data destinations along with the data from the regular columns.

API/Webhook, Zapier, etc.
MySQL, Bubble, Airtable etc
S3, FTP etc

In the API response JSON object, the virtual data appears inside the _virtual_data object, as shown below. The _virtual_data object is visible only if virtual columns are added to the sheet. In the example below, check lines 12 and 31.

[
  {
    "import_id": 79418895,
    "sheet_id": 55,
    "sheet_name": "Products",
    "row_number": 1,
    "row_data": {
          "Name": "TP-Link TL-WN822N Wireless Adapter",
          "SKU": "AS-100221",
          "Price": "33.00",
          "Quantity": "3",
          "_virtual_data":{
		"SKU_prefix": "AS",
		"Compare_Price": "43.00"
           }
    },
    "custom_fields": {
      "user_id": "1002"
    }
  },
  {
    "import_id": 79418895,
    "sheet_id": 55,
    "sheet_name": "Products",
    "row_number": 2,
    "row_data":{
          "Name": "EPower EP-600PM Power Supply Cooling Fan",
          "SKU": "SS-103824",
          "Price": "95.35",
          "Quantity": "8",
           "_virtual_data":{
		"SKU_prefix": "SS",
		"Compare_Price": "105.35"
           }
        },
    "custom_fields": {
      "user_id": "1002"
    }
  },
]

The virtual data is also available on the client-side JSON object.

Dependencies

In the JavaScript snippet, you can use an external library hosted on a CDN. You can also call any external API endpoint to fetch real-time data. Add the library script tag and any custom scripts to the Dependencies section, then use them in the main JavaScript snippet. For each import, the dependent scripts run only once, whereas the main JavaScript snippet runs once for each row.

Example

Suppose you want to use the data from the incoming USD_amount column to create a new GBP_amount virtual column. The amount should be converted from USD to GBP using real-time exchange rates.You can use the Money.js library for currency conversions and Open Exchange Rates to fetch real-time conversion rates. Here is the sample dependency code:

Dependent Scripts

//Load jQuery via CDN
<script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"> </script>

//Load Money.js via CDN
<script src="https://cdnjs.cloudflare.com/ajax/libs/money.js/0.0.1/money.min.js" integrity="sha512-dfZQaBBlTXvL+AUQKi7dd/9kb/KhreymnZYVdinjigqTdZUrAnLUNJRV34DUPFCdyek9mMBns3rzTlipnBKhTg==" crossorigin="anonymous" referrerpolicy="no-referrer"> </script>

//Fetch real-time conversion rates via ajax call and initialize fx object
<script type="text/javascript" >

    // Load exchange rates data via AJAX:
    $.getJSON(
        // NB: using Open Exchange Rates here, but you can use any source!
        'https://openexchangerates.org/api/latest.json?app_id=fa4744d77e844adb9cc3533b7ae162f3',
        function(data) {
            // Check money.js has finished loading:
            if (typeof fx !== "undefined" && fx.rates) {
                fx.rates = data.rates;
                fx.base = data.base;
            } else {
                // If not, apply to fxSetup global:
                var fxSetup = {
                    rates: data.rates,
                    base: data.base
                }
            }
        }
    );
</script>

Corresponding JavaScript snippet for the GBP_amount virtual column:

Javascript snippet

try
{
	//USD to GBP
	return fx.convert(csvbox.row["USD_amount"], {from: "USD", to: "GBP"});

}
catch(err)
{
	//returning error
  	return 'error: ' + err.name + ' | ' + err.message;
}

Variables

In the virtual column JavaScript snippet, you have access to data variables in the csvbox object. The following data is available:

`csvbox.row`

Contains the row data. Access each cell in the row by providing the column name. Examples:

csvbox.row["first_name"]
csvbox.row["order_id"]
csvbox.row["price"]

The column name must exist on the sheet, or an error is thrown.

`csvbox.user`

Contains the custom user attributes defined while initializing the importer. Examples:

csvbox.user["user_id"]
csvbox.user["team_id"]
csvbox.user["isAuthenticated"]

`csvbox.import`

Contains data specific to the current import. The following data is available:

csvbox.import["sheet_id"]
csvbox.import["sheet_name"]
csvbox.import["original_filename"]
csvbox.import["import_start_time"]
csvbox.import["destination_type"]
csvbox.import["total_rows"]
csvbox.import["row_number"] //current row number starting with 1

`csvbox.virtual`

Contains data from the preceding virtual columns.

csvbox.virtual["final_name"]
csvbox.virtual["age"]

Only data from the virtual columns defined before the current virtual column is available for use.

`csvbox.environment`

Contains the environment variables passed during importer initialization.

// Example Usage
if(csvbox.environment["user_id"] && csvbox.environment["user_id"] == "abc123") {

 // code

}

Use console.log(csvbox); to print all the available variables in the debugging console.

Getting Started

Dashboard Settings

Sheet Settings

Advanced Installation

Data Destinations

Account

Resources

Legal

How it works

Example

Adding virtual columns

Data rows

Inserting data into virtual columns

Receiving virtual data

Dependencies

Variables

`csvbox.row`

`csvbox.user`

`csvbox.import`

`csvbox.virtual`

`csvbox.environment`

​How it works

​Example

​Adding virtual columns

​Data rows

​Inserting data into virtual columns

​Receiving virtual data

​Dependencies

​Variables

​csvbox.row

​csvbox.user

​csvbox.import

​csvbox.virtual

​csvbox.environment

How it works

Example

Adding virtual columns

Data rows

Inserting data into virtual columns

Receiving virtual data

Dependencies

Variables

`csvbox.row`

`csvbox.user`

`csvbox.import`

`csvbox.virtual`

`csvbox.environment`