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. Common Regex to extract info
  6. Replace / Swap a value from a list
  7. 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

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"

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 last word from the subject line of an emailed import to EasyCSV Column: EasyCSV - Email Subject Line
Action: Extract last word

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.

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

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.