Solved

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

  • 20 February 2018
  • 3 replies
  • 2 views
E
Badge

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

icon

Best answer by takashi 20 February 2018, 15:13

View original
2 Attachments

3 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @edhere, hope this works as expected.

0684Q00000ArK6CQAV.png

String expression in the StringConcatenator

@Value(AreaCode)-@Substring(@Value(Phone),@StringLength(@Value(AreaCode)))
E
Badge

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
Rank
Userlevel 2
Badge +17

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


Community Stats

28,661
Posts
109,324
Replies
36,528
Members
Terms & ConditionsCookie 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.