Você pode converter um endereço em um código postal em uma planilha?

Question

Dada uma coluna de endereços com cidade e estado, mas sem zip em uma planilha, gostaria de colocar uma fórmula em uma segunda coluna que forneça o CEP. Você conhece uma maneira de fazer isso?

Estou lidando com endereços dos EUA, mas as respostas referentes a outros países também são interessantes.

ATUALIZAÇÃO: Acho que estou principalmente esperando que haja uma maneira de fazer isso no Google Spreadsheets. Eu percebo que você precisa acessar um grande banco de dados de CEP para fazer isso, mas parece que esse banco de dados já está dentro do Google Maps. Se eu colocar um endereço lá sem CEP, eu recebo um endereço com o CEP. Se o Google Maps puder fazer essa pesquisa, talvez haja uma maneira de fazer isso acontecer no Planilhas também.

    

Answer 1

As planilhas do Google têm uma série de funções para informações externas dados . Se você puder encontrar (ou criar) um site que faça a pesquisa passando parâmetros, você pode colocar uma fórmula semelhante a esta em:

Cell A1 (Address): 123 Main St
Cell B1 (City): Springfield
Cell C1 (State): MO

Cell D1 (combined address): =concatenate(A1,B1,C1)

Cell E1 (imported zip code): 
   =importData(concatenate("http://zipfinder.com/search?addr=",D1))

Esta é uma ideia hipotética. As funções concatenate, importData e outras funções para dados externos existem no Google Spreadsheets. "zipfinder.com" não existe. Existem muitos sites que ajudarão você a encontrar um código postal de um endereço. A parte complicada é encontrar um que aceite dados de endereço no URL e retorne algo simples o suficiente para o Google Spreadsheets usar.

    

Answer 2

Não haverá nada incorporado em uma planilha, pois os bancos de dados de endereços verificados são muito caros.

Você pode encontrar um serviço on-line que fará isso para você. Por exemplo, no Reino Unido, o site Royal Mail permite que você procure códigos postais (o equivalente no Reino Unido a códigos postais), mas você está limitado a 15 pesquisas por dia. Isto é para uso pessoal. As empresas receberão mais, mas você precisa se inscrever e pagar por isso.

Serviços semelhantes existirão em outros países.

    

Answer 3

Você precisa de algum tipo de ferramenta ou serviço de pesquisa de endereço externo. Com apenas Cidade / Estado, muitas vezes não há maneira de determinar o CEP correto, pois muitas cidades têm vários CEPs e muitas cidades têm ruas com vários CEPs, então você não pode adicionar apenas a rua e usar uma cidade / estado / zip fórmula ou pesquisa. Se você precisar encontrar códigos postais para endereços, terá que desmontar e pagar por um serviço ou ferramenta. Não há uma boa maneira de contornar isso.

    

Answer 4

É difícil fazer no Excel. Você precisaria chamar um serviço da Web usando o kit de ferramentas SOAP ou usar o Visual Studio. É preciso saber o que a Microsoft está pensando.

É muito mais fácil fazer uma planilha do Google Docs e exportar para o Excel:

Você pode usar o serviço da Web XML da GeoCoder.ca para procurar o CEP por endereço. No Google Docs, você usaria essa função:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//postal")

(onde A2 é o endereço da rua.)

Você também pode obter a latitude e a longitude dessa maneira:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//geodata")

Observe que o GeoCoder tem um controle sobre as solicitações por dia para o serviço gratuito.

    

Answer 5

Sim - agora você pode encontrar os códigos postais na sua planilha do Google Docs usando o seguinte URL:

Etapa 1: em esta página de destino , insira o endereço que você deseja pesquisar.

Etapa 2: Pesquisei o endereço "1245 5TH ST" em Washington, DC e a URL ficou assim:

https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=1245+5TH+ST&address2=&city=Washington&state=DC&urbanCode=&postalCode=&zip=

Etapa 3: copie e cole a seguinte fórmula e substitua CELL, CITY, STATE:

=Mid(importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15),Find("DC",importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15))+2,11)

Lembre-se de que o URL na Etapa 3 deve corresponder ao da Etapa 2. A célula será dinâmica com base no valor da célula de destino.

A função na etapa 3 extrai 11 caracteres após o valor STATE. Portanto, você terá que ajustar a função MID para obter os valores corretos. Isso funcionou perfeitamente para mim.

Uma última coisa: o Google permite apenas 50 funções de importação por folha. Assim, com o método acima você pode encontrar apenas 25 CEPs de uma só vez.

    

Answer 6

Você pode escrever uma função personalizada do Script de aplicativos com o serviço do Google Maps, usando uma abordagem semelhante a esta resposta . Para fazer isso, abra Ferramentas ... Editor de scripts ... e cole essa função em:

function getZIP(address) {
  var geo = Maps.newGeocoder().geocode(address);
  var resultComponents = geo.results[0].address_components;
  for (var i = 0; i < resultComponents.length; i++) {
     if (resultComponents[i].types.indexOf('postal_code') > -1) {
       return resultComponents[i].long_name;
    }
  }
}

Você pode chamar isso em uma célula de planilha, por exemplo, ele retorna corretamente 20500 para a Casa Branca:

=getZIP("1600 Pennsylvania Ave. NW, Washington, DC")

Ao contrário de algumas outras abordagens, também funciona para locais fora dos EUA, por exemplo ele corretamente retorna 75007 para a Torre Eiffel:

=getZIP("Champ de Mars, 5 Avenue Anatole France, Paris, France")

    

Answer 7

No Canadá, o site do Canada Post permite pesquisa de códigos postais por endereço (e não tenho conhecimento de nenhum limite diário), mas isso não ajuda na integração com sua planilha, infelizmente. Estou interessado em ver possíveis soluções para isso também. Sempre usei pesquisa de código postal manual para os que não conheço.

    

Answer 8

No Reino Unido, você precisaria acessar o arquivo de endereço de código postal (PAF), que lista todos os endereços no país. O acesso direto ao PAF custa dinheiro, e não uma quantia insignificante. Pagamos algo como £ 200 por assento, por ano, para acesso ilimitado aos dados atualizados do PAF, mas, a cada mês, estamos enviando mais de 10 mil solicitações para ele.

A maioria dos outros territórios é semelhante nesse sentido, então eu imagino que você não encontrará uma maneira legítima de acessar os dados zip da maneira que desejar.

Mesmo que você pudesse encontrar um site que permitisse a consulta RESTful de seus dados para inserir um zip diretamente em uma planilha, eles sem dúvida implementariam limites rígidos no número de consultas que poderiam ser feitas em qualquer período definido.

    

Answer 9

Existem alguns serviços on-line que processam uma planilha do Excel (ou um arquivo delimitado por tabs / CSV) e adicionam códigos ZIP ausentes. O preço pode variar de alguns dólares a US $ 100 ou mais, dependendo do provedor de serviços, alguns dos quais têm taxas de configuração, taxas de processamento e taxas de pedidos urgentes.

Alguns provedores de serviços podem enviar o arquivo processado de volta para você em alguns minutos, enquanto outros que reivindicam "processamento urgente" podem levar mais de três dias úteis. Com base na sua pergunta, você não quer investir na compra de software e gastar milhares de dólares em uma solução de software no local. Se for esse o caso, um provedor on-line é sua melhor aposta.

No interesse da divulgação completa, eu sou o fundador da SmartyStreets. Oferecemos uma verificação de endereço totalmente automatizada e on-line. Você pode arrastar e soltar seu arquivo do Excel em nosso site e nós o teremos processado e devolvido dentro de um ou dois minutos, dependendo do tamanho.