Data range import, splitting

[stephen.arnold]

UPS furnishes a text file with ZipCode ranges associated with a service code (URC.txt). I need to split out the range into a service code DB with each individual ZipCode associated with a singular value for lookup-printing on a label. How can we automate this process as manually, its quite cumbersome and time-consuming and the file is updated periodically.

file resembles

"US","01051","01053"," MA 011 9-05"

"US","01054","01054"," MA 011 9-03"

"US","01056","01059"," MA 011 9-02"

"US","01060","01063"," MA 011 9-01"

Is there a convenient calculation to split out these ranges into a file with fields:

Country Zip Service Code

US 01051 MA 011 9-05

US 01052 MA 011 9-05

US 01053 MA 011 9-05

US 01054 MA 011 9-03

US 01056 MA 011 9-02

US 01057 MA 011 9-02

US 01058 MA 011 9-02

US 01059 MA 011 9-02

US 01060 MA 011 9-01

etc

Thanks in advance...Stephen

[comment]

I need to split out the range into a service code DB with each individual ZipCode associated with a singular value for lookup-printing on a label.

Can you clarify your purpose? I am not sure what "lookup-printing on a label" means.

[stephen.arnold]

Sorry if I wasn't plain enough. I need to populate a FMP-DB with those three fields (Country, Zip, Service Code) so that I can use a lookup based on a ship-to zipcode in an address and from that lookup, populate a field on a UPS-approved shipping label. If its not necessary to split the range, but rather allow a lookup to use the range to find the Service code resultant - fine....Stephen

[comment]

I think you can solve this very simply. Just add a calculation field (result is Text) to the same table where the imported data is =

Let ( [

v = SerialIncrement ( Extend ( StartZip ) ; Get ( CalculationRepetitionNumber ) - 1 )

]; 

Case ( v ≤ Extend ( EndZip ) ; v )

)

Set the number of repetitions to the highest expected range span. Then use this field as the matchfield for the lookup.

[comment]

I'm wondering if the repeater method (which is cool, by the way) is more efficient

It used to be - I am not sure it still is. The truth is that you are absolutely right - I got side-tracked by the original question. I may not do things without reason, but that doesn't mean it's always the right reason. :