Product: ImportOmatic

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 How to Create a Dictionary.


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/YY(or YYYY) Date Column - Allows timestamp removal from date column with a 2 or 4 digit year.


                Replacement Value: --BLANK--

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


4. 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: ^


5. 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: $


6. 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}$)


7A. 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: ^.+$


*** 7B. If you experience issues with the #7 regular expression you may use the following instead: 


                Replacement Value: (Whatever Desired Value is)

                Value to Match on: [s\S]+


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


                Replacement Value: -BLANK-

                Value to Match on:  \d{2}/\d{2}/\d{2}


9. How To Add a period after the middle initial


                Replacement Value: $1.

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


10. 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})


11. 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


12. 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


13. 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)


14. 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)


15. 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})


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


                Replacement Value: $2/$3/$1

                Value to Match On: (\d{4})/(\d{1,2})/(\d{1,2})


17. 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}$)


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


                Replacement Value: $2

                Value to Match On: (\d{1,2})/(\d{1,2})/(\d{4})


19. 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)


                Replacement Value line 1: (desired value when field is blank, for example "Single")

                Value To Match On line 2: --BLANK-- 

                RegEx OFF


                Replacement Value line 2: (desired value when field is not blank)

                Value To Match On line 2: ^(?!Single).*$    <---NOTE: The text value here is the replacement value from the first line.

                RegEx ON


20. 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 line 1: recurring

                Value to match on line 1: (Value in data file)

                Regex OFF 


                Replacement Value line 2: --BLANK-- 

                Value to match on line 2: ^(?!recurring).*$    <---NOTE: The text value here is the replacement value from the first line.

                Regex ON


21. Add a decimal before the last two digits of a number (for example, 2500 should be 25.00)

                

                Replacement Value: $1.$2

                Value to Match On: (\d+)(\d{2})


22. Parse First Name from a field that contains first name/last name or first/middle/last  (for example, Bob from Bob Smith or Bob Joseph Smith)


                Replacement Value: $1

                First Value to Match On (note that there are two, but the replacement value is the same for both): (\w.*)(\s)(\w.*)(\s)(\w.*)

                Second Value to Match On: (\w.*)(\s)(\w.*)

                    


23. Parse Last Name from a field that contains first name/last name or first/middle/last  (for example, Smith from Bob Smith or Bob Joseph Smith)


                Replacement Value line 1: $5

                Value To Match On line 2: (\w.*)(\s)(\w.*)(\s)(\w.*)

                RegEx ON


                Replacement Value line 2: $3

                Value To Match On line 2: (\w.*)(\s)(\w.*)

                RegEx ON


24. Return a certain value if a string contains a specific word (for example, return "In honor of" if the word "honor" is found in the sentence "This gift honors Joe Smith")

                

                Replacement Value: In honor of

                Value to Match On: .*honor.*


25. Limit number of characters:

                

                Replacement Value: --BLANK--

                Value to Match On: (?<=.{X}).*  <---NOTE: X is the number of characters, so if you wanted to remove everything after the first 7 characters the value to match on would be (?<=.{7}).*





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.