Batch Zip Code lookup

1

Hello friends,
I have a data base with 100 records. I have the address, city, and state. Is there a way to batch lookup the zip codes for these records?

2

Assuming you have 4 fields named Address, City, State and ZIP you can run this loop procedure to fill in the Zip field. Note that the commented out line will return a full zip 9 while the uncommented line returns only the zip 5 code. Choose the one you want.

local theZip
noshow
startdatabasechange "ALLRECORDS","Get Zip Codes"
firstrecord
loop
    zipinfoplus Address,"",City,State,"",theZip
    // getdictionarvalue theZip,"ZIP9",Zip,Zip
    Zip=getdictionaryvalue(theZip,"ZIP9",Zip)[1;5]
    downrecord
until info("stopped")
showpage
endnoshow

I have included the startdatabasechange statement so that you can undo this if it goes wrong (for Panorama X only). Obviously bad addresses will not return a zip code.

3

Thanks so much Gary. I will try this.
Are the zip codes provided within Panorama or does it obtain from the USPS?
Also, should I assume this procedure will not work on Pan 6?

4

Gary’s process gets info from the web and will work fine in Panorama 6 except there was an extra parameter in the GetDictionaryValue line. It should be…

Zip=GetDictionaryValue(theZip,"ZIP9")[1;5]
5

Thank you Robert.
I wish all user forums worked this well. The responses are quick and to the point.

6

Robert is correct as far as Panorama getting the info from the USPS web site. As far as the getdictionaryvalue statement and the getdictionaryvalue( function are concerned, Panorama X allows an extra optional parameter for a default value that Panorama 6 does not include. The Pan 6 version would then look like this:

local theZip
noshow
firstrecord
loop
    zipinfoplus Address,"",City,State,"",theZip
    // getdictionarvalue theZip,"ZIP9",Zip
    Zip=getdictionaryvalue(theZip,"ZIP9")[1;5]
    downrecord
until info("stopped")
showpage
endnoshow

The extra parameters have been removed along with the startdatabasechange statement.

7

Hello Gary,
I must be doing something wrong. I created a new database in Pan 6 with the fields of Address, City, State, Zip. They are all text fields. I have cut and pasted your procedure into a new procedure in the database. I included 3 dummy records with correct addresses, city, state, and no zip. I run the procedure and after about 10 seconds it fills the Zip field with a hyphen.
Any clues would be appreciated.

8

I had only actually tested this in Panorama X previously. Trying now in Panorama 6 produced the results you note and checking further seems to point to the zipinfoplus statement no longer working properly. In the source code if I put a displaydata statement to display the page info returned from the posturl code it comes up empty. Something is surely amiss with the source code.

9

Thanks Gary,
I bought the license for 5 years ($300) for Pan X. I have been using Panorama since the late 1980’s. I love Jim and the product he provides. With all of that being said I have gone back to Pan 6. I will wait until a version of Pan X has the features that were in Pan 6.
I have a database of about 1,600 records that would need zip codes from valid addresses. My question is: Could I do the conversion in Pan X so this procedure would work and then export back into Pan 6?

10

You could fill the Zip field in Panorama X and then transfer that data back to your Panorama 6 original file. You could get the new info using arraybuild and either transfer the data to the clipboard or to a text file using filesave. In Panorama 6 you could then get the contents of the text file into a variable using the fileload( function (or use the clipboard it that is how you gathered it) and then use a formulafill to repopulate the Zip field. There may be other schemes as well that are more direct.

11

Adding the lines

global curlUserAgent
curlUserAgent = "Mozilla/5.0"

to the source code got it working. That seemed to be the only important difference between the Pan X and Pan 6 versions of that custom statement.

12

So, to get my earlier code to work in Panorama 6 you can either add Dave’s code to the start of the source code of the zipinfoplus statement for a permanent fix or add it to the start of my code. If you add it to the source code and save that file you will always have it correct. If, however, you just add it to my example you will have to add it every time you use zipinfoplus in the future.

13

Thank you Gary and Dave for your perseverance.
I need a favor from you. I really want to get this to work in Pan 6.
Could you please give me the complete procedure code that will give the Zip+4 number?
I am afraid I am going to make a mistake. Please don’t forget the 2 lines of code that Dave suggested.
Thanks again for all your help. I appreciate this forum and you guys.

14 
global curlUserAgent
curlUserAgent = "Mozilla/5.0"
local theZip
noshow
firstrecord
loop
    zipinfoplus Address,"",City,State,"",theZip
    // getdictionarvalue theZip,"ZIP9",Zip
    Zip=getdictionaryvalue(theZip,"ZIP9")[1;5]
    downrecord
until info("stopped")
showpage
endnoshow
15

Just noticed this part - here is the adjusted code for zip9:

global curlUserAgent
curlUserAgent = "Mozilla/5.0"
local theZip
noshow
firstrecord
loop
    zipinfoplus Address,"",City,State,"",theZip
    getdictionaryvalue theZip,"ZIP9",Zip
    downrecord
until info("stopped")
showpage
endnoshow
16

Hello Gary,
Using Pan 6, I tried several times pasting your code into the procedure and I keep getting a compile error. I can’t seem to isolate the offending line.

17

It’s a typo, which I just corrected. getdictionaryvalue was spelled getdictionarvalue.

18

Pressing command-E highlights the error.

19

Everything is working great now. Thank you for all the help.
I thought I would have to go to Zip Code Lookup at USPS and do it one by one.