Virtual Columns

Create new columns by applying custom data transformation logic.

Virtual Columns are one of CSVbox’s most powerful data transformation tools.

You can run small snippets of JavaScript that can merge, split, re-format, correct, and enrich incoming data during a data import.

Super useful if you need to process your data, do calculations, merge things together, etc before receiving it at your end. The sky is the limit!

How it works

1. For any sheet (template), add a Virtual Column via the CSVbox dashboard.

2. Attach a Javascript snippet to the Virtual Column.

3. After the users submit the CSV file, the Javascript will run to populate data in the Virtual Columns.

4. Data from the uploaded CSV as well as the Virtual Column will be pushed to the data destination.

Example

Merge

Merge data from the first_name and the last_name columns into a new Virtual Column named full_name.

Here is the attached Javascript for the full_name Virtual Column:

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

Re-format

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;
}

Static

Pass any static value to the Virtual Column.

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

Adding Virtual Columns

1. Go to the edit sheet page > Columns tab > Click Add Virtual Column button.

2. Add Virtual Column Name.

3. Provide Javascript code.

4. Attach Dependent libraries (optional).

5. Click Save.

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 via Javascript to add to the Virtual Column.

After the user submits the CSV, the Javascript will run for each data row in sequential order to populate Virtual Columns.

The csvbox.row object is available to work with each row. You can use csvbox.row["column_name"] to read the specific column of the data row. Utilize data from a single column, multiple columns, or no columns to process via Javascript and 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(' ');

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

If Javascript returns a NULL value, an empty string will be added to the Virtual Column.

Receiving Virtual Data

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

API/Webhook, Zapier etc

In the API response JSON object, the dynamic data will be displayed inside the _virtual_data object as shown below. The _virtual_data object will be visible only if the 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"
    }
  },
]

MySQL, Bubble, Airtable etc

Virtual Columns will be available in the column mapping modal. You can push Virtual Columns to any receiving data field of your choice.

Mapping Virtual Columns

S3, FTP etc

In the AWS S3 file store, the Virtual Columns will be added as new columns in the uploaded file.

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

Dependencies

In the Javascript snippet, you can utilize an external library that is hosted via a CDN. You can also call any external API endpoint to fetch real-time data. Simply add the library script tag and/or any custom scripts to the 'Dependencies' section and start using it in the main Javascript snippet.

For each import, the dependent scripts will be run only once. Whereas the main Javascript snippet will be executed once for each row.

VIrtual Column Processing

Example

Consider you want to use the data from the incoming USD_amount column to create a new GBP_amount Virtual Column. The currency amount should be converted from USD to GBP using real-time exchange rates.

We can utilize the Money.js library for currency conversions and Open Exchange Rates for fetching 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 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 included in the csvbox object. The following data is available:

csvbox.row

It contains row data. Each cell in the row can be accessed 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 will be thrown.

csvbox.user

It contains the custom user attributes defined while initializing the importer. Examples:

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

csvbox.import

This refers to the current import-specific data. 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

It contains data from the preceeding virtual columns.

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

Data from only the virtual columns that are defined before the current virtual column are available for use.

console.log(csvbox);

With this statement, you can print all the available variables in the debugging console,