How to use Virtual Fields

Combine, Transform, Replace, or Add New Values

Quick Links:

  1. How To - Video Overview
  2. Extract part of a spreadsheet or email (subject line, sender email address, email body text) value
  3. Change / Transform a value
  4. Combine multiple values
  5. How to have an empty value for a column
  6. Common Regex to extract info
  7. Replace / Swap a value from a list
  8. Equations & Advanced (IF, SUM, ROUND, MATH, ELSE, ELSIF, etc...)
  9. Add a new static value not in your spreadsheet

How To - Video Overview

Step 1: Have an admin navigate to the Sheet import page.

Click the "Virtual Output Fields" link in the top navigation.

Step 2: Create a new Virtual Output Field Rule

Select between 3 options:

  1. Combine/Extract/Transform single or multiple values
  2. Add a new field with a static text value
  3. Replace a value with If _____ then _____ case statements

Step 3: Use the Virtual Fields

  • If going to another app supported by EasyCSV, then map the Virtual Fields to a field in the destination app.
  • If posting to Zapier or IFTTT, use the virtual fields in your Zap or Applet.
  • If posting to Google Sheets, then add a new column header with the text from the Virtual Field Output Key field.
  • If posting to your own app, use the "virtual_fields" part of the JSON being posted to your endpoint

Extract part of a value from Spreadsheet or Email values

Step 1: Create a Virtual Field

Create a new Virtual Field using the tab "Combine column values"

Step 2: Select the column or EasyCSV field

Choose which field you want to extract data from.

Step 3: Choose an action

You can use Extract with a Regular Expression (Regex) to extract part of a value or some built-in functions we offer. Here are the built-in functions:

Action Use Case Example
Extract First Word Get first word from a spreadsheet value
Extract First Word After Substinrg Get first word after a word in the cell value. Substring you specify: Report

Spreadsheet value: Monthly Report December

Final Value extracted: December
Extract Last Word Get last word from a spreadsheet value
Extract the Nth value from comma-seperated list (1 = first item) Get a value from a list of values Index/Number you specify: 3

Spreadsheet value: cat,dog,horse,fish

Final Value extracted: horse
Extract text with a Regular Expression (regex) Get a value using a Regular Expression Helpful, common examples
Extract URL Campaign (utm_campaign) Get a value from the utm_campaign query string param or a URL/Link Spreadsheet value: https://website.com/?utm_campaign=october_newsletter&utm_medium=email

Final Value extracted: october_newsletter
Extract URL Medium (utm_medium) Get a value from the utm_medium query string param or a URL/Link Spreadsheet value: https://website.com/?utm_campaign=october_newsletter&utm_medium=email

Final Value extracted: email
Extract URL Source (utm_source) Get a value from the utm_source query string param or a URL/Link Spreadsheet value: https://website.com/?utm_source=google&utm_content=landing_page&utm_term=selling

Final Value extracted: google
Extract URL Content (utm_content) Get a value from the utm_content query string param or a URL/Link Spreadsheet value: https://website.com/?utm_source=google&utm_content=landing_page&utm_term=selling

Final Value extracted: landing_page
Extract URL Term (utm_term) Get a value from the utm_term query string param or a URL/Link Spreadsheet value: https://website.com/?utm_source=google&utm_content=landing_page&utm_term=selling

Final Value extracted: selling
Extract URL domain Get the root domain from a URL/Link Spreadsheet value: https://website.com/?utm_source=google&utm_content=landing_page&utm_term=selling

Final Value extracted: website.com
Extract email domain Get the root domain from an email Spreadsheet value: donna@gmail.com

Final Value extracted: gmail.com
Count Characters Get the number of characters of the value (includes space between words) Spreadsheet value: cats

Final Value extracted: 4
Extract Date (MM/DD/YYYY) Get a date in the MM/DD/YYYY format from a field Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 10/22/2022
Extract Date (DD/MM/YYYY) Get a date in the DD/MM/YYYY format from a field Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 22/10/2022
Extract Month Get the numeric representation of a date's month from a field Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 10
Extract Day of Month Get the numeric representation of a date's day of month from a field Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 22
Extract Day of Week Get the day of the week from a datetime value Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: Wednesday
Extract Year Get the year from a datetime value Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 2022
Extract 12 hour time (7:00 PM) Get the 12-hour time from a datetime value Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 8:59 PM
Extract 24 hour time (19:00) Get the 24-hour time from a datetime value Spreadsheet value: 10/22/2022 20:59:57

Final Value extracted: 20:59
Extract Phone Number (gets first phone number from text) Get the first number from any text. Spreadsheet value: My phone number is 612-123-4567

Final Value extracted: 612-123-4567
Remove character(s) from text (sub-string matching) Remove a single or string of char from text that matches a substring you specify. Substring you specify to remove: ,

Spreadsheet value: 1,299.50

Final Value extracted: 1299.50
Remove character(s) from text (regex matching) Remove a single or string of char from text that matches a regex pattern you specify. The regex pattern you specify must start with a "/" and end with a "/". Substring you specify to remove: /^0*/

Spreadsheet value: 00000123456789

Final Value extracted: 123456789

Change / Transform a spreadsheet value

Step 1: Create a Virtual Field

Create a new Virtual Field using the tab "Combine column values"

Step 2: Select the column or EasyCSV field

Choose which field you want to change/transform the value from every row.

Step 3: Choose an action

Action Use Case Example
Upcase Capitalize every letter of the value Original value: dog

New value: DOG
Downcase Changes every letter to lowercase of the value Original value: Dogs Are Fun

New value: dogs are fun
Sentence case (first word capitalized) Capitalize the first letter of the value. Original value: the moon looks nice

New value: The moon looks nice
Combine multiple values Take multiple values from different columns and combine them. How to combine multiple values

Combine multiple values

Step 1: Create a Virtual Field

Create a new Virtual Field using the tab "Combine column values"

Step 2: Select the columns or EasyCSV fields you want to combine

Use the "+ Add another spreadsheet column / field button" to select multiple fields to combine.

Step 3: Specify how you want to combine the values

In the "Join Delimiter" field type the characters you want in between the values you ware combining. Example: If I type ", " then have 3 columns to combine the final value may look like this "cat, dog, fish"

How to have an empty value for a column

Step 1: Create a Virtual Field

Create a new Virtual Field using the tab "Single Static Text Value"

Step 2: Enter in value

Enter in this exact text and the column will be blank for every row: {BLANK}

Common Regex to extract spreadsheet & email info

Use these Regex (Regular Expressions) examples to help extract parts of Spreadsheet values or email fields.

Use Case What column name to use for your Virtual Field and action Ruby Regular expression to use
Get the file name WITHOUT the file extension from an attached CSV file emailed to EasyCSV for import Column: EasyCSV - Import name
Action: Extract text with Regular Expression		 (.+?)(\.[^.]*$|$)

test example file name
Get the word immediately after some matching text in the email body sent to EasyCSV for an import Column: EasyCSV - Email Body Text
Action: Extract text with Regular Expression		 Let's say you want the word immediately after "Unique ID:" and there is a space seperating

(?<=Unique ID:\s)[^.\s+]*

test example email text
Get all words to the end of the line of text immediately after some matching text in the email body sent to EasyCSV for import Column: EasyCSV - Email Body Text
Action: Extract text with Regular Expression		 Let's say you want the line of words immediately after "Note:" and there is a space seperating

(?<=Note:\s)[^\n\r]*

test example email text
Get the start of a string until the 1st occurrence of a specific character Column: Any column value
Action: Extract text with Regular Expression		 Let's say you want the characters from the start of the string until the first "(" char

^[^\(]*

test example text
Get all words BEFORE a substring/word Column: Any column value
Action: Extract text with Regular Expression		 Let's say you want the characters from the start of the string until the first " - " substring

^.*(?=(\s-\s))

test example text
Get all words AFTER the 1st occurrence of a substring/word Column: Any column value
Action: Extract text with Regular Expression		 Let's say you want the characters from the start of the string until the first " - " substring

^.*(?=(\s-\s))

test example text
Get all words AFTER the the first SPACE Column: Any column value
Action: Extract text with Regular Expression		 Let's say you want the characters after the first space

\s(.*)

test example text
Get the characters within 2 surrounding characters like (parentheses) Column: Any column value
Action: Extract text with Regular Expression		 Let's say you want the characters within "(" and ")" chars

\((.*)\)

test example text
Get all text between two substrings Column: Any column value
Action: Extract text with Regular Expression		 Let's say you want the name and email from the raw_body of a forwarded email

(?<=From: ).*?(?=\\nSent:)

test example text (?<=From: ).*?(?=\\nSent:)
Extract from email from a forwarded email Column: EasyCSV Raw Email Body
Action: Extract text with Regular Expression		 Let's say you want just the email from the raw_body of a forwarded email

(?<=\<)(.*)(?=>\\nSent:)

test example text
Select all HTML tags AND remove them Column: HTML Product Description
Action: Replace Character(s)		 Let's say you want just the text from a field that has HTML tagsin it

/<(?:"[^"]*"['"]*|'[^']*'['"]*|[^'">])+>/

test example text
Get last word from the subject line of an emailed import to EasyCSV Column: EasyCSV - Email Subject Line
Action: Extract last word
Remove Preceding Zeroes for a number Column: Column that has a Number or SKU with preceding Zeroes
Action: Remove Character(s)		 Remove preceding zeroes using remove characters option. This will use a regular expression to match the characters you want to remove. In the EasyCSV field you will put:

/^0*/

test example text

How can I test my Regular Expression?

Use this handy Ruby Regular Expression Tester

Replace / Swap a value for a new value from a list

Step 1: Create a Virtual Field

Create a new Virtual Field using the tab "If value is ____ then change to ____ (Case Statement)"

Step 2: Select the column that contains the value you want to swap

Use the dropdown with the label "IF" to select the column that will contain the value you want to possibly swap for different values.

Step 3: Specify cases of where you want to swap the value for a new one

Add multiple cases where you can specify if the value "exactly matches" or simply "includes" a string/text. If the match happens then EasyCSV will swap that value for the New/Output Value.

You can have up to 10 cases for matching and swapping of the value. If you need more please contact support@easycsv.io

If no match happens for the value in the row, EasyCSV will return the value from the CSV/spreadsheet that is being imported. EasyCSV only swaps out values that match the cases you set up. You can also set a final else value.

For the output value you can use the special syntax: {FIELD_VAL} which will be replaced with the spreadsheet column value at import time. For example, you can set the output value to be: {FIELD_VAL} dollars. And if the column value of the row's column was "5" the final case statement value would be come: 5 dollars

Step 4: Use the Virtual Field instead of the column name

  • If going to another app supported by EasyCSV, then map the Virtual Field to a field in the destination app.
  • If posting to Zapier or IFTTT, use the Virtual Field in your Zap or Applet. You will have to pull a new example row in Zapier to see this new Virtual Field you created.
  • If posting to Google Sheets, then add a new column header with the text from the Virtual Field Output Key field.
  • If posting to your own app, use the "virtual_fields" part of the JSON being posted to your endpoint

Equations & Advanced Virtual Fields (IF, SUM, ROUND, MATH, ELSE, ELSIF, etc...)

Great ways to manipulate cell data in your spreadsheet files before you send it to the destination. Do equations on a field to add 15% to a price. Do text manipulation. Advanced IF/ELSE IF/ELSE or CASE statements. And more!

Step 1: Create a Virtual Field

Create a new Virtual Field using the tab "Equation".

Step 2: Write & test your equation

Write an equation just like you do in a cell in Microsoft Excel or Google Sheets. You can use values from the rows being imported or Virtual Fields you have already made.

* You will be able to try writing an Equation and seeing the result on any EasyCSV plan. You will ONLY be able to save the field on the Business Plan and above as this is a premium feature.

Cheatsheet - Common Examples

Use Case / Function Example(s) - {COLVF} will be replaced by the column name in your files
Round a numerical value round({COLVF})
round({COLVF}, 2) <- rounds to 2 decimals
rounddown({COLVF})
roundup({COLVF})
round(3.75) <- 4
roundup(1.234, 2) <- 1.24
Math ({COLVF} + 5) * 0.28
(15 + 5) * 1.28
Numeric
(functions for many columns in the same row or your file)		 sum({COLVF01}, {COLVF02}, {COLVF03})
min({COLVF01}, {COLVF02}, {COLVF03})
max({COLVF01}, {COLVF02}, {COLVF03})
avg({COLVF01}, {COLVF02}, {COLVF03})
Comparison (true / false) {COLVF} < 10
>, <=, >=, <>, !=, = also available
String / Text len("{COLVF}")
left("{COLVF}", 2) <- left('ABCD', 2) = 'AB'
right("{COLVF}", 2) <- right('ABCD', 2) = 'CD'
mid("{COLVF}", 2, 2) <- mid('ABCD', 2, 2) = 'BC'
find("substring", "{COLVF}") <- find('BC', 'ABCD') = 2
concat("abc-", "{COLVF}") <- concat('abc-', '987654') = 'abc-987654'
substitute_all("{COLVF}", "old_string", "new_string") <- substitute_all('ABCDBC', 'BC', 'XY') = 'AXYDXY'
contains("substring", "{COLVF}") <- contains('BC', 'ABCD') = true
escape_html("{COLVF}")
unescape_html("{COLVF}")
IF / ELSE statement if ({COLVF} < 10, 10, 20) <- IF {COLVF} is 5, then this value would be 10
IF {COLVF} is 15, then this value would be 20
if ({COLVF} > 2 and {COLVF} < 10, "cheap", "expensive")
if (contains("US", "{COLVF}") or contains("United States", "{COLVF}"), "US", "Other")
xor, not - also available
IF / ELSE IF (ELSIF) / ELSE statement * To do ELSE IF like statements we need to nest multiple IF statements.
if ({PRICE} > 100, "Expensive", if ({PRICE} > 50, "Mid-range", "Affordable"))
CASE statement 
CASE {COLVF}
WHEN 'banana' THEN '$2.00'
WHEN 'apple' THEN '$3.00'
ELSE '$5.00'
END
Date Arithmetic {COLVF} + duration(30, days)
{COLVF} + duration(2, months)
{COLVF} + duration(1, year)
{COLVF} 7 duration(7, days)
Date must be in format YYYY-MM-DD. Use a Virtual Field first to massage a date from the file if it's in a different format.
Date Comparison {COLVF} < 2023-01-02 <- true/false
Date must be in format YYYY-MM-DD. Use a Virtual Field first to massage a date from the file if it's in a different format.
Advanced String / Text double_split_take("{COLVF}", 1st_split_char, 2nd_split_char, take_index, prepend_text, append_text, join_char)
double_split_take("{COLVF}", ",", "|", 1, "CWR | ", "", ",")
double_split_take("Outdoor | Accessories, Paddlesports | Accessories, Automotive/RV | Accessories", ",", "|", 1, "CWR | ", "", ",") = CWR | Outdoor,CWR | Paddlesports,CWR | Automotive/RV
This advanced quation is great for spliting a list, then splitting the items in the list to take part of each item and prepend or append some text. Then join them back together in one string as a list again. Great for Vendor/Supplier Tags manipulation.
Need help writing an equation? Contact EasyCSV support via webchat or email and let us know what you are hoping to do. We can help you write any equation :)

Add a new static value not in your spreadsheet

Step 1: Have an admin navigate to the Sheet import page.

Click the "Virtual Output Fields" link in the top navigation.

Step 2: Create a new Virtual Output Field Rule

Create a new Virtual Field using the tab "Single Static Text Value"

Step 3: Specify the value of the field

This field will be set to the static text you specify for every row being sent to the destination App/API/Zapier/IFTTT.