Skip to main content
Solved

Update phone numbers based on Excel file (RegEx, AttributeManager or FeatureMerger?)

  • February 20, 2018
  • 3 replies
  • 11 views
E
Forum|alt.badge.img

Hi all,

 

 

I have to bulk update a whole bunch of phone numbers.

 

The current format is ten digits, starting with a 0. I need to convert these numbers to area code + local number.

The area code is either three or four digits followed by a dash (-).

 

 

The area codes are stored in enclosed file "PhoneUpdating"

 

The input and output phone numbers are in "PhoneOutput"

 

 

What I'd like FME to do:

When the first three or four digits in the "Phone" column match with the thee or four digits found in AreaCode: then replace these with the data found in AreaCodeUpdated. (output examples in green)

 

 

Example files:

Thanks again,

 

Ed

Best answer by takashi

Hi @edhere, hope this works as expected.

0684Q00000ArK6CQAV.png

String expression in the StringConcatenator

@Value(AreaCode)-@Substring(@Value(Phone),@StringLength(@Value(AreaCode)))
View original
2 Attachments
Did this help you find an answer to your question?

3 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • February 20, 2018

Hi @edhere, hope this works as expected.

0684Q00000ArK6CQAV.png

String expression in the StringConcatenator

@Value(AreaCode)-@Substring(@Value(Phone),@StringLength(@Value(AreaCode)))
E
Forum|alt.badge.img
  • edhere
  • Author
  • February 21, 2018
takashi wrote:

Hi @edhere, hope this works as expected.

0684Q00000ArK6CQAV.png

String expression in the StringConcatenator

@Value(AreaCode)-@Substring(@Value(Phone),@StringLength(@Value(AreaCode)))
Thanks @takashi , it works nicely!

 

It seems I need to add a two digit (06) area code as well. 

 

Should I add another tester at the beginning that creates a separate flow for string length 2 and direct that to another FeatureMerger that joins @Left(@Value(Phone),2) ?

 

 

Thanks,

 

Ed

 

takashi
Influencer
  • takashiInfluencer
  • February 21, 2018
takashi wrote:

Hi @edhere, hope this works as expected.

0684Q00000ArK6CQAV.png

String expression in the StringConcatenator

@Value(AreaCode)-@Substring(@Value(Phone),@StringLength(@Value(AreaCode)))
Yes. Separate the AreaCode records into three streams for 4, 3, and 2 digits, and then add a FeatureMerger as you mentioned.

 

0684Q00000ArMIhQAN.png

 

If you feel it's too cluttered, the InlineQuerier could be an alternative.

 

0684Q00000ArMKfQAN.png

 

SQL

 

select B.AreaCode, B.AreaCode||'-'||substr(A.Phone,5) as PhoneOutput
from A inner join B on B.AreaCode = substr(A.Phone,1,4)
union all
select B.AreaCode, B.AreaCode||'-'||substr(A.Phone,4)
from A inner join B on B.AreaCode = substr(A.Phone,1,3)
union all
select B.AreaCode, B.AreaCode||'-'||substr(A.Phone,3)
from A inner join B on B.AreaCode = substr(A.Phone,1,2)

 

Reply


Helpful Members This Week

Community Stats

31,224
Posts
118,408
Replies
38,827
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.