Способ преобразования почтовых индексов в адреса

Question

На рабочем месте моей матери она часто обращается к базе данных и вытаскивает номера для составления отчетов. От выбора данных Oracle до вывода отчетов в формате Excel она столкнулась с этой проблемой, когда ей дают почтовые индексы, и ей необходимо преобразовать их адреса (все в пределах США).

Наступает момент, когда ей приходится целый день искать адреса вручную на сайте UPS, поэтому она спросила меня, могу ли я что-нибудь с этим сделать. Поэтому я искал какой-то онлайн-инструмент для преобразования почтовых индексов в адреса для файлов Excel CSV и нашел один инструмент от Dino, но он не был последовательным.

Существуют ли другие существующие инструменты для преобразования почтовых индексов в адреса? Если нет, то я могу создать какой-нибудь быстрый инструмент. Что поднимает вопрос: что проще и проще? Конвертировать почтовые индексы в конце базы данных или создать инструмент, который позволяет использовать файлы и входные файлы Excel и автоматически добавлять адреса в файл Excel.

Answer 1

Используйте PowerShell и http://zipasaur.us для преобразования почтовых индексов в полные адреса

Если вы откроете URL-адрес, например http://zipasaur.us/zip/94105 , веб-сайт немедленно предоставит вам загружаемый файл.JSON-файл, который содержит все детали адреса. Но вы не хотите открывать браузер, вводить правильный почтовый индекс, загружать, открывать и конвертировать файл каждый раз.

Здесь PowerShell становится очень полезным. Сценарий ниже читает в формате CSV почтовых индексов, просит zipasaur.us преобразовать данные, преобразовывает и выводит все детали снова в виде CSV. Вы можете легко создавать и открывать файлы CSV в Excel.

---

Как настроить

1. Откройте новый текстовый файл и сохраните этот код как 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"
   

2. Составьте список входных почтовых индексов, которые вы хотите конвертировать. Сохраните его как input.csv в той же папке, что и ваш скрипт .ps1

3. Запустите сценарий PowerShell. В этом же месте появится новый файл output.csv

---

Что если я хочу больше, чем просто название города и штата?

zipasaur.us также предоставляет информацию о широте, долготе, ярлыке штата и названии округа. Например, эта строка кода добавит все детали.

$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

Answer 2

Почтовая служба США (USPS) предоставляет веб-API для выполнения этого более автоматизированным способом, чем использование веб-сайта вручную. API веб-инструментов USPS

Answer 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