At my mother's workplace, she often accesses the database and pulls out numbers in order to form reports. From selecting data Oracle to outputting reports in Excel format, she has come across this problem where she is given zipcodes and needs to convert them addresses (all within the USA).
It is getting to a point where it can take her a whole day looking up addresses manually on the UPS website, so she asked me if there was anything I can do about it. So I searched for some online tool to convert zipcodes to addresses for Excel CSV files and I found one tool by Dino, but it was not consistent.
Are there any other existing tools to convert zip to addresses? If not, I can perhaps create some sort of quick tool. Which brings up the question: What is easier and more simple? To convert zipcodes on the database end or to create a tool which allows excel files and inputs and autoadd the addresses into the excel file.
23 Answers
The United States Postal Service (USPS) provides web APIs for doing this in a more automated way than manually using the website. USPS Web Tools APIs
Use PowerShell and to convert zip codes to full addresses
If you open an URL like , the website will immediately provide you a downloadable .JSON file which contains all Address details. But you don't want to open a browser, fill in the correct zip code, download, open and convert the file every time.
Here becomes PowerShell very useful. The script below reads in a CSV of zip codes, asks zipasaur.us to convert the data, converts and outputs all details again as CSV. You can easily create and open CSV files with Excel.
How to setup
Open a new text file and save this code as
convert.ps1$url = "" $list = Get-Content(".\input.csv") $web = New-Object System.Net.WebClient ForEach ($zip in $list ) { $file = $web.DownloadData("$url/$zip") $string = [System.Text.Encoding]::ASCII.GetString($file) $array = $string -split '[,:]' | foreach {$_ -Replace '[}""{]',"" } $newfile += $array[1] + ',' + $array[7] + ',' + $array[11] + "`n" } $newfile | out-file ".\output.csv"Put together an input list of ZIP codes you want to convert. Save it as
input.csvat the same folder as your .ps1 scriptRun the PowerShell script. A new
output.csvfile at the same location will appear
What if I want more than just the city and state name?
zipasaur.us also provides information about latitude, longitude, state shortcut and county name. For example, this code line will adds all details.
$newfile += $array[1] + ',' + $array[3] + ',' + $array[5] + ',' + $array[7] + ',' + $array[9] + ',' + $array[11] + ',' + $array[13] + "`n"$array[0] = code $array[1] = 94105 $array[2] = lat $array[3] = 37.7864 $array[4] = lng $array[5] = -122.3892 $array[6] = city $array[7] = San Francisco $array[8] = state $array[9] = CA $array[10] = state_full $array[11] = California $array[12] = county $array[13] = San Francisco
Export a list of zip codes from excel as .csv and run a query for each of those zip codes. The results can be formatted as .csv and imported back into excel.
Here is a crude example with bash wget and sed demonstrating .csv output:
$ sh zip.sh 94105 94506 94706 94906 44123
94105; San Francisco, CA;
94506; Danville, CA;
94706; Albany, CA;
94906; null, null;
44123; Euclid, OH;$ cat zip.sh
#!/bin/sh
# code="94105"
# lat="37.7864"
# lng="-122.3892"
# city="San Francisco"
# state="CA"
# state_full="California"
# county="San Francisco"
while [ $# -gt 0 ]; do unset code unset city unset state eval ` wget --quiet -O- |\ sed -E ' s/","/"\n/g; s/":"/="/g; s/\{"//; s/"\}/"/;' ` 2>/dev/null if [ "x$code" != "x" ]; then echo "$code; $city, $state; " else echo "$1; null, null; " fi shift
done
