在我母亲的工作场所,她经常访问数据库并提取数字以形成报告。从选择 Oracle 数据到以 Excel 格式输出报告,她遇到了这个问题:她获得了邮政编码并需要将其转换为地址(均在美国境内)。
她现在需要花一整天的时间在 UPS 网站上手动查找地址,所以她问我是否可以做些什么。于是我搜索了一些在线工具,用于将邮政编码转换为 Excel CSV 文件的地址,并找到了 Dino 的一个工具,但它并不一致。
有没有其他现有的工具可以将邮政编码转换为地址?如果没有,我或许可以创建某种快速工具。这引出了一个问题:什么更简单、更轻松?在数据库端转换邮政编码,还是创建一个允许 excel 文件和输入并自动将地址添加到 excel 文件的工具。
答案1
美国邮政服务 (USPS) 提供了 Web API,以比手动使用网站更自动化的方式执行此操作。USPS 网络工具 API
答案2
使用 PowerShell 和http://zipasaur.us将邮政编码转换为完整地址
如果您打开类似这样的 URL http://zipasaur.us/zip/94105
,网站将立即为您提供一个可下载的 .JSON 文件,其中包含所有地址详细信息。但您不想每次都打开浏览器、填写正确的邮政编码、下载、打开并转换文件。
此时,PowerShell 就变得非常有用。下面的脚本读取邮政编码的 CSV 文件,要求 zipasaur.us 转换数据,然后转换并再次将所有详细信息输出为 CSV 文件。您可以使用 Excel 轻松创建和打开 CSV 文件。
如何设置
打开一个新的文本文件并将此代码保存为
convert.ps1
$url = "http://zipasaur.us/zip" $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"
整理要转换的邮政编码输入列表。将其保存
input.csv
为同一文件夹作为你的 .ps1 脚本运行 PowerShell 脚本。
output.csv
同一位置将出现一个新文件
如果我想要的不仅仅是城市和州名称怎么办?
zipasaur.us 还提供有关纬度、经度、州缩写和县名的信息。例如,此代码行将添加所有详细信息。
$newfile += $array[1] + ',' + $array[3] + ',' + $array[5] + ',' + $array[7] + ',' + $array[9] + ',' + $array[11] + ',' + $array[13] + "`n"
$array[0] = 代码 $array[1] = 94105 $数组[2] = 纬度 $数组[3] = 37.7864 $数组[4] = lng $数组[5] = -122.3892 $array[6] = 城市 $array[7] = 旧金山 $array[8] = 州 $array[9] = CA $array[10] = state_full $array[11] = 加利福尼亚州 $array[12] = 县 $array[13] = 旧金山
答案3
将 Excel 中的邮政编码列表导出为 .csv,然后针对每个邮政编码运行查询。结果可以格式化为 .csv 并重新导入 Excel。
这是一个使用 bash wget 和 sed 演示 .csv 输出的粗略示例:
$ 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 http://zipasaur.us/zip/$1 -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