Fala meu amigo(a),

Nesse post vou compartilhar uma rotina que já utilizei em alguns clientes para fazer a Atualização da Base de CEP dos Correios.

Vou dividir esse assunto em dois posts:

Parte 1: O resultado da Rotina será apenas uma tabela com todas as informações do CEP;
Parte 2: O resultado da Rotina serão várias tabelas, separando as informações por cidade, bairro, CEP, etc.

A ideia desses dois posts é que você consiga fazer essa atualização de uma forma bem simples, apenas executando uma procedure e informando um parâmetro com o caminho dos arquivos:

USE CEP

EXEC [dbo].[stpAtualizacao_Cep] @Ds_Caminho_Pasta_Arquivos = 'C:\SQLServer\CEP\Fixo\'

USE CEP

EXEC [dbo].[stpAtualizacao_Cep] @Ds_Caminho_Pasta_Arquivos = 'C:\SQLServer\CEP\Fixo\'

Quer saber como? Então vem comigo. #gogogo

YouTube – Vídeo:

Segue abaixo um vídeo que gravei no YouTube mostrando na prática a execução dos scripts desse post:

https://www.youtube.com/watch?v=pCy1F__U9iM&t

Arquivos TXT:

Para fazer essa Atualização vamos precisar dos Arquivos que os Correios disponibilizam (Arquivos de Exportação de dados do Diretório Nacional de Endereços (e-DNE)) com todos os dados das cidades, bairros, CEPs, etc. Aqui sugiro que alguém da sua empresa entre em contato com os Correios para solicitar esses arquivos (não tenho todos os detalhes desse procedimento).

Depois disso, os Correios irão enviar um Arquivo ZIP, conforme o print abaixo:

Aqui vamos utilizar os Arquivos TXT da pasta “Fixo”. Essa pasta contém alguns tipos de arquivos distintos que irei explicar daqui a pouco e possui o arquivo “Leiautes_Fixo.doc” que também será muito importante!

Segue abaixo um exemplo do conteúdo de um Arquivo TXT:

Layout Arquivos:

Como podemos ver, cada linha do Arquivo TXT contém as informações de um CEP. Essas linhas utilizam um LAYOUT que está definido no Arquivo “Leiautes_Fixo.doc” (esse DOC possui dezenas de páginas).

Para facilitar a sua vida, criei uma planilha (arquivo “Mapeamento Tabela CEP x Layout – Parte 1.xlsx”) resumindo apenas as informações que vamos precisar. Isso vai poupar algumas horas do seu projeto para ler e entender todo esse documento, o que não é algo muito simples.

Essa planilha possui uma aba para cada tipo de arquivo que iremos importar para o banco de dados. Aqui eu faço o mapeamento das posições do Layout dos arquivos com as colunas das tabelas no banco de dados, ou seja, já estou dando tudo mastigado pra você economizar o seu tempo!

Scripts Importação – Procedure “[dbo].[stpAtualizacao_CEP]”:

“Beleza Luiz, você falou, falou, falou e não fez nada ainda!! Como eu vou conseguir fazer essa importação???”

Certo, então vamos começar a ver os scripts da atualização! #gogogo

OBS: Disponibilizei o LINK para DOWNLOAD dos arquivos no final do post OK.

Primeiro vamos criar uma base chamada “CEP” (ou o nome que você desejar, mas depois será necessário substituir nos próximos passos/arquivos também OK).

Arquivo: “01 – Cria Base [CEP].sql”

OBS: O Ponto de Atenção aqui é no caminho dos arquivos da base.

USE master

GO

-- Criar a base "CEP".
-- OBS: Alterar o caminho para um local existente no seu servidor.

CREATE DATABASE [CEP] 
	ON  PRIMARY ( 
		NAME = N'CEP', FILENAME = N'C:\SQLServer\Data\CEP.mdf' , 
		SIZE = 102400KB , FILEGROWTH = 102400KB 
	)
	LOG ON ( 
		NAME = N'CEP_log', FILENAME = N'C:\SQLServer\Log\CEP_log.ldf' , 
		SIZE = 30720KB , FILEGROWTH = 30720KB 
	)
GO

-- Alterar o Recovery Model da base para SIMPLE.
ALTER DATABASE [CEP] SET RECOVERY SIMPLE

USE master

-- Criar a base "CEP".

-- OBS: Alterar o caminho para um local existente no seu servidor.

CREATE DATABASE [CEP]

ON PRIMARY (

NAME = N'CEP', FILENAME = N'C:\SQLServer\Data\CEP.mdf' ,

SIZE = 102400KB , FILEGROWTH = 102400KB

)

LOG ON (

NAME = N'CEP_log', FILENAME = N'C:\SQLServer\Log\CEP_log.ldf' ,

SIZE = 30720KB , FILEGROWTH = 30720KB

)

-- Alterar o Recovery Model da base para SIMPLE.

ALTER DATABASE [CEP] SET RECOVERY SIMPLE

Depois disso, vamos criar a procedure que será utilizada depois para fazer a Atualização. Basta dar um F5 no arquivo abaixo:

Arquivo: “02 – Cria Procedure [dbo].[stpAtualizacao_CEP].sql”

Vou tentar resumir aqui alguns trechos importantes dessa procedure:

Ela vai receber um parâmetro com o caminho da pasta dos arquivos.

Depois cria a tabela “Cep_New” que será o resultado da procedure.

Agora começa a parte da importação dos dados de cada tipo de arquivo.

Eles seguem o mesmo processo, fazendo um BULK INSERT para importar os dados e popular uma tabela temporária.

Por fim, insere os registros na tabela “Cep_New” seguindo o Layout específico de cada tipo de arquivo (aqui utilizo a função SUBSTRING e informo a posição inicial do campo e o tamanho).

Cep Geral (Localidades):

Em algumas localidades (“cidades”) os Ceps não são individuais, ou seja, um único cep irá representar vários endereços. Isso pode acontecer em cidades pequenas ou no interior.

Segue abaixo um exemplo do site dos Correios. Aqui os campos Logradouro e Bairro ficam em branco.

Repare também que várias colunas irão ficar com o valor NULL na nossa importação.

USE CEP

SELECT * 
FROM Cep_New
WHERE Nr_Cep IN ('13920000','13930000','13940000')

USE CEP

SELECT *

FROM Cep_New

WHERE Nr_Cep IN ('13920000','13930000','13940000')

A Recomendação dos Correios nesses casos é que você deixe o campo do endereço aberto para edição na aplicação/sistema, pois como falei anteriormente, um mesmo CEP Geral será utilizado para vários endereços distintos.

Resumindo:

De forma resumida, você deve fazer o seguinte:

OBS: Antes de executar os scripts abaixo, validar os parâmetros que precisam ser alterados (caminho dos arquivos).

Executar o arquivo “01 – Cria Base [CEP].sql”.
Executar o arquivo “02 – Cria Procedure [dbo].[stpAtualizacao_CEP].sql”.
Executar o arquivo “03 – Executa Procedure [dbo].[stpAtualizacao_CEP].sql”. Nos meus testes o tempo de execução foi de menos de 30 segundos! Bem rápido!!!

USE CEP

EXEC [dbo].[stpAtualizacao_Cep] @Ds_Caminho_Pasta_Arquivos = 'C:\SQLServer\CEP\Fixo\'

USE CEP

EXEC [dbo].[stpAtualizacao_Cep] @Ds_Caminho_Pasta_Arquivos = 'C:\SQLServer\CEP\Fixo\'

Pronto! Depois desses três passos vamos ter a tabela “Cep_New” atualizada!

“Show Luiz! Mas essa tabela deve ficar gigante e ocupar muito espaço né?”

Nesse caso, a tabela ficou com um pouco mais de 1 milhão de registros e com apenas 150 MB.

Atualizando a Base de Produção:

Como falei anteriormente, o resultado da Rotina de Atualização do CEP será a tabela “Cep_New” com os dados atualizados. Como não sei como cada ambiente utiliza a tabela de CEP, optei por gerar essa tabela separada para que você mesmo possa definir a melhor forma de atualizar o seu ambiente.

Segue abaixo algumas sugestões:

OBS: Recomendo que você agende uma Janela de Manutenção para fazer essa alteração no horário de menor movimento no seu ambiente.

Se você utiliza uma base separada para o CEP (somente para consultas), você poderia renomear essa nova tabela e depois renomear a base para ser a nova produção também.
Se a sua tabela de CEP atual fica dentro de uma base que possui várias outras coisas que não podem ser perdidas, você poderia fazer a atualização de duas formas:

Fazer um TRUNCATE TABLE e importar os dados da nova tabela (aqui você não teria os dados antigos caso precise fazer um ROLLBACK).
OU RENOMEAR A TABELA ATUAL e importar a tabela da base que está atualizada (aqui você teria a tabela antiga, sugiro que exclua depois de alguns dias).

Essas são apenas algumas sugestões. Fique à vontade para fazer da melhor maneira para o seu ambiente.

Bônus – Próximos Posts:

O último cliente que implantei essa rotina foi bem interessante, pois além de ambientes SQL On-Premises, eles também possuíam instâncias “SQL Server no Amazon RDS” e “SQL Server on Linux”.

Esses ambientes específicos tinham algumas particularidades e pretendo falar sobre elas nos próximos posts também. #aguarde

Parte 1 – Vantagens x Desvantagens:

Por fim, vou listar aqui algumas vantagens e desvantagens dessa primeira solução:

Vantagens:

Como o resultado da atualização é apenas uma tabela com todas as informações, o processo fica mais simples. Isso também irá facilitar as consultas, pois podemos fazer um SELECT em uma única tabela.

Desvantagens:

A desvantagem é que os dados não ficam armazenados da forma mais eficiente. Com essa estrutura de tabela única, nós iremos repetir vários dados. Repare como repetimos o texto com o nome da localidade e bairro várias vezes.

Também não conseguiríamos fazer apenas uma listagem das cidades ou bairros de forma separada com um SELECT simples. A Solução 2 que vou explicar no próximo post irá resolver essa situação. =)

Download Arquivos – Atualização CEP – Parte 1 – Uma Tabela:

Segue abaixo o link para baixar os arquivos:

https://github.com/luizvitorf/SQLServer/tree/master/Scripts/CEP/Atualiza%C3%A7%C3%A3o%20CEP/Parte%201%20-%20Uma%20Tabela

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

4 comentários em “Casos do Dia a Dia – Correios – Atualizar Base CEP – Parte 1 – Uma Tabela”

Junior Galvão - MVP 29 de janeiro de 2021 Responder

Luiz, bom dia.

Parabéns pelo post, nossa entre os anos de 2000 e 2010 tive que fazer muito deste procedimento no SQL Server, na época era o SQL Server 2000 e 2005.

Abraços,
- Luiz Lima Autor do post29 de janeiro de 2021 Responder
  
  Fala Galvão, blz? Pois é, esse processo é recorrente e ter uma rotina pronta pra agilizar a nossa vida é uma mão na roda. =)
  
  Abraço,
  Luiz Vitor
Anderson Aroucha de Carvalho 29 de janeiro de 2021 Responder

Sensacional meu amigo!
- Luiz Lima Autor do post29 de janeiro de 2021 Responder
  
  Obrigado seu lindo! =) #ehnoix
  
  Abraço,
  Luiz Vitor

YouTube – Vídeo:

Arquivos TXT:

Layout Arquivos:

Scripts Importação – Procedure “[dbo].[stpAtualizacao_CEP]”:

Arquivo: “01 – Cria Base [CEP].sql”

Arquivo: “02 – Cria Procedure [dbo].[stpAtualizacao_CEP].sql”

Cep Geral (Localidades):

Resumindo:

Atualizando a Base de Produção:

Bônus – Próximos Posts:

Parte 1 – Vantagens x Desvantagens:

Vantagens:

Desvantagens:

Download Arquivos – Atualização CEP – Parte 1 – Uma Tabela:

Compartilhe isso:

4 comentários em “Casos do Dia a Dia – Correios – Atualizar Base CEP – Parte 1 – Uma Tabela”

Deixe uma respostaCancelar resposta