What Are Some Commonly Used Regular Expressions?

Solution ID: 291286
Product: ImportOmatic
Published: 12/01/13
Description: Dictionaries allow multiple values from a data file to be translated into a single value within RE during the import process. Dictionaries can be utilized to minimize the amount of manual manipulation that takes place during file preparation. Regular Expressions can assist in expounding upon the functionality of the dictionary. Regular Expressions are a type of scripting language that can enhance dictionary functionality. This solution describes regular expressions that are commonly used with ImportOmatic Dictionaries. 
Environment: All
Versions: All

Answer: 

For more information on how to create a dictionary, please see this solution.

It is important to note that when you are using a regular expression, you must highlight the indicated Replacement Value and select the check box at the bottom of the Replacement Value pane "Use Regular Expression". Checking this box will tell the dictionary to allow the use of the regular expression. 



Note: If the Replacement value or Value to match on requires a --BLANK-- entry, click on the button with the plus sign to get this entry.

1. Add a Leading Zero to Zip Codes - Adds the leading zero back to zip codes if excel removed them

Replacement Value: 0$&
Value to Match on: ^\d{4}(-\d{4})?$

2. The Anything Dictionary - Allows use of an existing column that may currently be set to ignore in order to add a default blanket value to a file (ex. constituent code). This expression will add the value regardless of if there is a value in the column or not.

Replacement Value: (Whatever desired entry is)
Value to Match on: ^.*

3. How to Remove Timestamp from MM/DD/YYYY Date Column - Allows timestamp removal from date column with a 4-digit year.

Replacement Value: --Blank--
Value to Match on: (?<=[0-9]{4}).*

4. How to Remove Timestamp from MM/DD/YY Date Column - Allows timestamp removal from date column with a 2-digit year.

Replacement Value: --BLANK--
Value to Match on: (?<=\d+/\d+/\d+\s+).*

5. How to Prefix a Value to Incoming Data - Allows prefix of a value to an incoming string of data.

Replacement Value: (Whatever desired Prefix is)
Value to Match on: ^

6. How to Append a Value to Incoming Data - Allows append to a value on an incoming string of data.

Replacement Value: (Whatever desired Append is)
Value to Match on: $

7. How to Format a Data String of Numeric Values to a Date Format - Allows conversion of a string of numeric values to dd/mm/yyyy format.

Replacement Value: $1/$2/$3
Value to Match on: (\d+(?=\d{6}))(\d{2}(?=\d{4}))(\d{4}$)

8. How to Replace Anything That is Not Blank with a Value - Allows association of a replacement value with any value that is NOT BLANK in the data file.

Replacement Value: (Whatever Desired Value is)
Value to Match on: ^.+$

9. How to change the MM/DD/YYYY format to YY only:

Replacement Value: (Class Year)
Value to Match on:  \d{2}/\d{2}/\d{2}

10. How To Add a period after the middle initial

Replacement Value: $1.
Value To Match On: (^[A-Za-z]$)

11. How To change YYYY/MM date format to MMDDYYYY date format:

Replacement Value: $2/01/$1 
Value to match on: (\d{4}(?=\d{2}))(\d{2})

12. Strip off all formatting from phone numbers so RE can apply formatting - 
This removes from the string anything that is not a number.

Replacement Value = --BLANK--
Value to match on: \D

13. Format Canadian Postal Codes to a 7 character format:

Replacement Value: $1 $2
Value to match on: (\S{3})(\S{3})

If importing different zip codes from various countries in the same file, use:

Replacement Value: $1 $2
Value to match on: (^[A-Z]{1}\d{1}[A-Z]{1}(?=\d{1}[A-Z]{1}\d{1}))((?<=^[A-Z]{1}\d{1}[A-Z]{1})\d{1}[A-Z]{1}\d{1}$)

This will handle letter number letter number letter number zip codes and leave other zip codes as is

14. Format Canadian Postal Codes to a 6 character format - This will strip off the unwanted space in between the two sets of postal code data, leaving a 6 character format:

Replacement Value: --BLANK--
Value to match on: [ ] (make sure there is a space in between the two brackets)

15. Strip off any unwanted characters - Allows for the removal of any specific character from a string:

Replacement Value: --BLANK--
Value to match on: (character to be removed)

16. Change Date Format from MM/DD/YYYY to DD/MM/YYYY:

Replacement Value: $2/$1/$3
Value To Match On: 
^(\d{1,2})/(\d{1,2})/(\d{4})

17. Changes date format from YYYY/MM/DD to MM/DD/YYYY:

Replacement Value: $2/$3/$1
Value to Match On: 
(^\d{4})/((?<=/)\d{2}(?=/))/(\d{2}$)

18. Changes date format from MM/DD/YYYY (M/DD/YYYY, etc) to YYYY - this will strip out all characters before the last four digits:

Replacement Value: --BLANK--
Value to Match On: ^.*?(?=\d{4}$)

19. Changes the MM/DD/YYYY value to DD only:

Replacement Value: $2
Value to Match On: (^\d+)/((?<=/)\d+(?=/))/(\d{4}$) 

20. Conditionally import data based on whether data is present in the file or not (for example, "single" if there's no relationship last name and "married" if there is a relationship last name)

#1 Replacement Value: (desired value when field is blank)
#1 Value To Match On: ^\s*$      <---NOTE: The "s" here is lowercase.

#2 Replacement Value: (desired value when field is not blank)
#2 Value To Match On: ^\S+$     <---NOTE: The "S" here is UPPERCASE.

21. Replace a certain value to something specific, and everything else to --BLANK-- (for example, a certain value should be translated to recurring, and everything else should be blank)
Replacement Value 1: recurring
Value to match on 1: (Value in data file)
Regex OFF

Replacement Value 2: --BLANK--
Value to match on 2: 
^((?!recurring).)*$
Regex ON


Remember to select Save to save changes. 

***Note: Selecting to Delete a Replacement Value will also delete any associated Values to Match On as well. 

Refer to the ImportOmatic User Guide for additional information.

Feedback and Knowledge Base