Start a new topic

Format Date for Schedule Month and Schedule Day of Month

Hi there,


Looking to import some Annual Recurring Gifts into a Batch through IOM and I need help setting up RegEx dictionaries to get the Schedule Month and Schedule Day of Month to populate properly.


The Date field in my csv file is formatted as such:

2021-09-22T13:23:08.543704-07:00[US/Pacific]


(In order to take one field (Date) and map it to two different fields (Schedule Month) and (Schedule Day of Month), you will of course have to use a virtual field and the Copy Field function for one of those.)

But here are some RegExes for those:

GetMonthFromDate:

(In this sort of RegEx, 

     "$1" will be set equal to the part in parentheses.  

     "^" means "be sure to match from the BEGINNING of the string". 

     "\d" means "digit".  

     "-" is just a literal hyphen

     "T" is just a literal T (I'm not sure if it's case sensitive. Considering how many variations of Regular Expressions there are and that RegEx is a Unix concept being implemented on a Windows platform, it's about a 50/50 chance.)

     ".*" means "match everything else whatever".  We have to have that or else the rest of the string after the match will also be returned, and we don't want that.)


Replacement value:

$1

set to be Regular Expression

Match on:

^\d\d\d\d-(\d\d)-\d\dT.*

image



GetDAYFromMonth:

(This is the same as the previous except that we move the parentheses.)

Replacement value:

$1

set to be Regular Expression

Match on:

^\d\d\d\d-\d\d-(\d\d)T.*

image


If you have any questions or concerns, please don't hesitate to ask.


1 person likes this

Thank you. I've never used RegEx before so I have no idea how any of it works.


So I don't have to set up a different Replacement value for each month/day? I just use the $1 and it will know to spit out the correct Month/Day based on what it pulls from the spreadsheet?

Sorry I couldn't resist putting in a lot of extra detail. :-)  RegEx are very difficult to learn and understand, but this one is simpler than most.

The short answer is: Yes because the parentheses are in different places in the two RegExes and the $1 will be "whatever is in the parentheses".

If you compare the two RegExes next to each other, you will see the parentheses are in different places.

This is the one for when you want the two digit month:
^\d\d\d\d-(\d\d)-\d\dT.*
vs day:
^\d\d\d\d-\d\d-(\d\d)T.*

And the "$1" will be "whatever was between the parentheses". 


-------
So for one of the fields, say Schedule Month, you map Schedule Month to the incoming Date field and put the GetMonthFromDate dictionary on it.

For the other field, it will be a bit more effort since you have to re-use the Date field.  You will create a virtual field, use the copy function to copy the incoming Date into it, map it to Schedule Day of Month and put the second dictionary GetDAYFromDate on it.


-----------------------
If the "$1" is a little too odd to feel comfortable with, here are two other versions of the same dictionaries that do the same thing.  Only in this case, they do it be deleting the parts you don't want leaving just the part that you do want:

image


image



1 person likes this

Hi. This dictionary for the Month didn't work because Raiser's Edge is expecting the work January, not the number 1 for the month, etc. 

Login or Signup to post a comment