Page 1 of 1

Lookup digits of phone database against CSV phone prefixes

Posted: Fri Aug 12, 2005 6:23 pm
by Moz
Hi,

Bit of a strange one for you. I have a large list of mobile telephone numbers and would like to put the network next to each mobile number.
I have a list of all prefixes for mobile networks in the UK and want to use this to lookup the prefix and determine the network.

The list of prefixes is in a CSV file example is:
07875 Orange
07876 Vodafone
07879 Vodafone
0788 Vodafone
07883 Three
07885 O2
07886 Three 
07888 Three
07889 O2
07890 Orange
07891 Orange
07896 Orange
07897 Three 
07898 Three 
07899 Vodafone
0790 T-Mobile
07900 Vodafone
07901 Vodafone
07909 Vodafone
07910 T-Mobile

An example list of mobile numbers in ascending order would be:
07900123123
07901123123
07903123123
07906123123
07908123123
07909123123
07910123123

I need the output of the list to look like this:
07900123123,Vodafone
07901123123,Vodafone
07903123123,T-Mobile
07906123123,T-Mobile
07908123123,T-Mobile
07909123123,Vodafone
07910123123,T-Mobile

The problem I have is that:
a) I can't work out how to get textpipe to lookup the prefix and then append the respective network
b) I've made a work around by extracting each of the netwok's ranges of prefixes into seperate files - thereby having one file with just the o2 prefixes, one file with just Orange prefixes etc. Then I run a "Special>Retain lines>Retain lines from list" filter individually against each of the network prefix files and thereby matching lines with the relative prefix and then saving these files seperately. This works to some degree, but if you look at the prefix list above you'll see that for example T-Mobile has all prefixes beginning 0790 EXCEPT those beginning 07900, 07901 and 07909 which are Vodafone. Therefore if Textpipe matches 0790 it'll flag this as T-Mobile and export to T-Mobile, without realising that it should be Vodafone.

Any help on this would be very much appreciated.

Thanks,

Moz

Posted: Fri Aug 12, 2005 7:02 pm
by Moz
Ahh - just had a thought. I should stop being lazy and put a bit of preperation in to start with.

In the problem mentioned above with the following example:
0790 T-Mobile
07900 Vodafone
07901 Vodafone
07909 Vodafone


I can pad this out to be:

07900 Vodafone
07901 Vodafone
07902 T-Mobile
07903 T-Mobile
07904 T-Mobile
07905 T-Mobile
07906 T-Mobile
07907 T-Mobile
07908 T-Mobile
07909 Vodafone

This would then work in the way described above (Match pattern from list).

I can then use a filter to match against pattern list from file for say o2, then make a subfilter to add text side by side and add ,o2 to the end, then move to the next filter to match against pattern list from file for orange, then make a subfilter to add text side by side etc etc.

Fingers crossed this will work.

Moz

PS. Sorry for the ramblings.

Posted: Fri Aug 12, 2005 10:12 pm
by Moz
It works perfectly.

In the great words of Hannibal - I love it when a plan comes together.

Moz

Posted: Wed Aug 17, 2005 12:21 pm
by DataMystic Support
Sometimes explaining things to someone else is the best way to resolve a problem!