Start a new topic

RegEx to split a string?

Every month, I get a file of person records to import into RE. It's all pretty straightforward except for the phone number field, which can contain one, two or three phone numbers with the phone type indicated in parentheses.  Up till now I've been using Excel's text to columns to split the field, but I'd really rather split the string using (I think) RegEx in Importomatic.  I'm a novice with RegEx, though, so I need some help.  Here's an example of what I'm working with:

 

"(555) 123-4567 (Home) (555) 765-4321 (Work) (555) 123-0001 (Cell)"

"(555) 123-0002 (Home) (555) 765-0001 (Cell)"

"(555) 789-4321 (Cell)"

"(555) 789-1234 (Home) (555) 123-1234 (Work)"

"(555) 987-0123 (Home)"

 

Am I correct in thinking that I can set up a Dictionary using RegEx to split strings like these into phone number fields in Importomatic?  And if so, how?

 

thanks much,

Katie

Katherine Fritz, bCRE
Database Coordinator
Pennsylvania SPCA
350 E. Erie Avenue | Philadelphia, PA 19134
Phone: 215.426.6300 x228
E-mail: kfritz@pspca.org
 | Website: www.pspca.org

With a 97% live release rate the Pennsylvania SPCA is the largest no-kill animal welfare organization in the state. 

Make a donation today to save a life.


Hmm, tough one!

 

I would make a dictionary for each field (Home, Cell, Work) that looks like:

 Replacement Value  Value to match
 $1 .*(\(\d{3}\) \d{3}-\d{4}) \(Home\).* 
 -- BLANK -- .*[a-zA-Z]+.*

<style type="text/css" id="telerik-reTable-1"> .telerik-reTable-1 { border-width: 0px; border-style: none; border-collapse: collapse; font-family: Tahoma; } .telerik-reTable-1 tr.telerik-reTableHeaderRow-1 { margin: 10px; padding: 10px; color: #3F4D6B; background: #D6E8FF; text-align: left; font-size: 10pt; font-style: normal; font-family: Tahoma; text-transform: capitalize; font-weight: bold; border-spacing: 10px; line-height: 14pt; vertical-align: top; } .telerik-reTable-1 td.telerik-reTableHeaderFirstCol-1 { padding: 0in 5.4pt 0in 5.4pt; color: #3a4663; line-height: 14pt; } .telerik-reTable-1 td.telerik-reTableHeaderLastCol-1 { padding: 0in 5.4pt 0in 5.4pt; color: #3a4663; line-height: 14pt; } .telerik-reTable-1 td.telerik-reTableHeaderOddCol-1 { padding: 0in 5.4pt 0in 5.4pt; color: #3a4663; line-height: 14pt; } .telerik-reTable-1 td.telerik-reTableHeaderEvenCol-1 { padding: 0in 5.4pt 0in 5.4pt; color: #3a4663; line-height: 14pt; } .telerik-reTable-1 tr.telerik-reTableOddRow-1 { color: #666666; background-color: #F2F3F4; font-size: 10pt; vertical-align: top; } .telerik-reTable-1 tr.telerik-reTableEvenRow-1 { color: #666666; background-color: #E7EBF7; font-size: 10pt; vertical-align: top; } .telerik-reTable-1 td.telerik-reTableFirstCol-1 { padding: 0in 5.4pt 0in 5.4pt; } .telerik-reTable-1 td.telerik-reTableLastCol-1 { padding: 0in 5.4pt 0in 5.4pt; } .telerik-reTable-1 td.telerik-reTableOddCol-1 { padding: 0in 5.4pt 0in 5.4pt; } .telerik-reTable-1 td.telerik-reTableEvenCol-1 { padding: 0in 5.4pt 0in 5.4pt; } .telerik-reTable-1 tr.telerik-reTableFooterRow-1 { background-color: #D6E8FF; color: #4A5A80; font-weight: 500; font-size: 10pt; font-family: Tahoma; line-height: 11pt; } .telerik-reTable-1 td.telerik-reTableFooterFirstCol-1 { padding: 0in 5.4pt 0in 5.4pt; border-top: solid gray 1.0pt; text-align: left; } .telerik-reTable-1 td.telerik-reTableFooterLastCol-1 { padding: 0in 5.4pt 0in 5.4pt; border-top: solid gray 1.0pt; text-align: left; } .telerik-reTable-1 td.telerik-reTableFooterOddCol-1 { padding: 0in 5.4pt 0in 5.4pt; text-align: left; border-top: solid gray 1.0pt; } .telerik-reTable-1 td.telerik-reTableFooterEvenCol-1 { padding: 0in 5.4pt 0in 5.4pt; text-align: left; border-top: solid gray 1.0pt; } </style>

Replacing Home with Work and Cell for the other two dictionaries.

This will look for that particular number in the string and return that or, if it was not found, then it will blank out any values that have a letter (which would mean that it wasn't picked up by the first part).

Then in your import you have 3 virtual fields, one for each phone type.  You would apply the "copy" function pointing to the column with the raw data on each of the fields.  Then also apply the appropriate dictionary.

 

That should produce either a number (if it was found) or BLANK if that phone type was not found.

Apparently my browser did not like the style I selected for that table!
Thank you very much. I will report back to let you know how it goes!

Katie

Katherine Fritz, bCRE
Database Coordinator
Pennsylvania SPCA
350 E. Erie Avenue | Philadelphia, PA 19134
Phone: 215.426.6300 x228
E-mail: kfritz@pspca.org | Website: www.pspca.org
With a 97% live release rate the Pennsylvania SPCA is the largest no-kill animal welfare organization in the state.

Make a donation today to save a life.
It worked very nicely - thank you, Wayne!

Katie

Katherine Fritz, bCRE
Database Coordinator
Pennsylvania SPCA
350 E. Erie Avenue | Philadelphia, PA 19134
Phone: 215.426.6300 x228
E-mail: kfritz@pspca.org | Website: www.pspca.org
With a 97% live release rate the Pennsylvania SPCA is the largest no-kill animal welfare organization in the state.
Make a donation today to save a life.
yay! good job getting it to work.
Login or Signup to post a comment