How to use Virtual Fields
Combine, Transform, Replace, or Add New Values
Quick Links:
- How To - Video Overview
- Extract part of a spreadsheet or email (subject line, sender email address, email body text) value
- Change / Transform a value
- Combine multiple values
- Common Regex to extract info
- Replace / Swap a value from a list
- Add the import time or random unique ID as a value when it's not in your spreadsheet
- 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:
- Combine/Extract/Transform single or multiple values
- Add a new field with a static text value
- 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 | Remove a single or string of char from text. |
Substring you specify to remove: , Spreadsheet value: 1,299.50 Final Value extracted: 1299.50 |
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 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 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 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. 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
Add the import time or random unique ID as a value when it's not in your spreadsheet
Step 1: Create a Virtual Field
Create a new Virtual Field using the tab "Static Value"
Step 2: Type one of the values below
The values below will be automatically replaced at import time with a new value.
Value | Use Case | Example | |
---|---|---|---|
ez_random_id_{char length you want} | Generate a random string of characters to act as a unique id for the character length you want |
Original value: ez_random_id_10 New value: a1b2c3d4e5 |
|
ez_current_date_mmddyy | Date import is executed |
Original value: ez_current_date_mmddyy New value: 4/22/2021 |
|
ez_current_datetime_mmddyy | Date & time import is executed |
Original value: ez_current_datetime_mmddyy New value: 4/22/2021 5:43 PM UTC |
|
ez_current_date_ddmmyy | Date import is executed |
Original value: ez_current_date_ddmmyy New value: 22/4/2021 |
|
ez_current_datetime_ddmmyy | Date & time import is executed |
Original value: ez_current_datetime_ddmmyy New value: 22/4/2021 5:43 PM UTC |
|
ez_current_date_yymmdd | Date import is executed |
Original value: ez_current_date_yymmdd New value: 2021/4/22 |
|
ez_current_datetime_yymmdd | Date & time import is executed |
Original value: ez_current_datetime_yymmdd New value: 2021/4/22 5:43 PM UTC |
|
ez_current_datetime_yyyy-mm-dd | Date & time import is executed |
Original value: ez_current_datetime_yyyy-mm-dd New value: 2021-4-22 |
|
ez_next_month_end_yyyy-mm-dd | Last day of next month |
Original value: ez_next_month_end_yyyy-mm-dd New value: 2021-5-31 |
|
Need a different date/time format? Contact EasyCSV support and we will add it for you. Let us know what format you need when contacting. |
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.