A dica de hoje foi motivada por uma dúvida enviada por um ex-aluno meu que participou do treinamento que ministrei sobre Geoprocessamento com Software Livre, em julho, para a Empresa Brasileira de Correios e Telégrafos. Aprenda a usar o comando COPY no PostgreSQL. A questão da vez é: Como importar dados escritos em um arquivo TXT para uma tabela de um banco de dados no PostgreSQL?
COPIAR ARQUIVOS DE TEXTO PARA POSTGRESQL
Neste exemplo vamos inserir, no banco dados, uma tabela contendo informações sobre fazendas.
OBS: O tutorial a seguir considera que você já tem o PostgreSQL corretamente instalado e um banco de dados criado. Os códigos SQL mostrados a partir do passo 2 (dois) devem ser escritos na janela que é acessada ao selecionar a palavra tabelas e clicando-se em seguida no ícone executar consultas SQL arbritrárias, destacado na figura abaixo.
PASSO 1: Salve um arquivo chamado fazendas.txt em um diretório de seu computador. Neste exemplo criei o arquivo em D:.
01; ‘Sol Nascente‘; 2009-10-01
02; ‘Parque Belo‘; 2007-03-15
03; ‘Pedra Bonita‘; 2001-08-25
04; ‘Medeiros‘; 2011-06-22
PASSO 2: Crie uma tabela com o nome fazendas com os campos descritos no comando SQL a seguir e em seguida tecle F5:
CREATE TABLE fazendas (
id_faz integer,
nome_faz varchar(25),
data_compra date);
O comando acima cria a tabela e fazendas e três campos, onde id_faz é um código identificador da fazenda, nome_faz é o nome da propriedade e data_compra a data em que ela foi comprada por seu dono atual. Logicamente, esta tabela ainda está vazia.
PASSO 3: No terminal SQL digite a linha de código abaixo adequando o caminho destacado em verde de acordo com a pasta onde você salvou o arquivo fazendas.txt (Tecle F5 na sequência).
COPY fazendas FROM ‘D:/fazendas.txt‘ using delimiters ‘;‘;
Entenda que no comando acima você está copiando para a tabela fazendas o arquivo *.txt desejado e informando que este arquivo usa como delimitador o ponto e vírgula (;).
Se apareceu alguma mensagem de erro, verifique se há algum problema, por exemplo, com as aspas ou com o caminho utilizado até o arquivo. Caso a mensagem seja de sucesso, digite o comando SQL abaixo para visualizar o conteúdo da tabela.
SELECT * FROM fazendas;
O resultado da operação é ilustrado na imagem abaixo (o conteúdo da inteira tabela fazendas).
Conseguiram realizar o procedimento? Alguma dúvida? Deixem seus comentários sobre este tutorial e leia também:
59 respostas
Fui rodar o script abaixo, uso banco de dados PostgreSQL:
COPY public.teste FROM path_arquivo using delimiters ‘,’;
e deu o seguinte erro:
must be superuser to copy to or from a file
Estou com problema,
ERROR: could not open file “D:/teste.csv” for reading: No such file or directory
SQL state: 58P01
Poderia por favor me ajudar, agradeço muito!
Ola,
Gostaria de saber se além de importar os dados do arquivo txt, existe alguma forma de importar o NOME.txt do arquivo pra um campo numa tabela.
Estou tentando importar uma planilha do excel e também tentei com o arquivo .txt, mas os dois dao o mesmo erro:
ERROR: could not open file “C:\Users\SETICADMIN\Documents\testes-sql\teste001.txt” for reading: Permission denied
SQL state: 42501
modifiquei a pasta de destino, mas agora ta dando esse erro:
ERROR: invalid input syntax for integer: “1”
CONTEXT: COPY teste001, line 1, column tes_codigo: “1”
ERROR: invalid input syntax for integer: “1”
Realmente não conseguir resolver esse problema…
Bom dia. Li os comentários e me deparei com o mesmo problema de muitos, mas não encontrei a solução referente ao erro de codificação. Meu banco é LATIN1 e retorna o erro: ERROR: character with byte sequence 0xe2 0x80 0x98 in encoding “UTF8” has no equivalent in encoding “LATIN1”
Não posso alterar o encoding para UFT8. Como importar o .txt utilizando encoding LATIN1 ?
SET CLIENT_ENCODING TO ‘WIN1252’; no windows
SET CLIENT_ENCODING TO ‘LATIN1’; no Linux
Não funciona, já tentei de tudo, pesquisei no google e todos dizem a mesma coisa mais não funciona. Observação estou usando VAGRANT e já tentei de odo jeito. 🙁
ERROR: could not open file “D:/profissoes.txt” for reading: No such file or directory
SQL state: 58P01
Estou com o mesmo problema
ERROR: could not open file “D:/teste.txt” for reading: No such file or directory
SQL state: 58P01
ele nao consegue encontrar o arquivo, vc conseguiu resolver ??? poderia me Ajudar por favor.
Estou com o mesmo problema,
ERROR: could not open file “D:/teste.csv” for reading: No such file or directory
SQL state: 58P01
Você conseguiu resolver? Poderia por favor me ajudar, agradeço muito!
Não estou conseguindo importar estou recebendo a mensagem abaixo
ERROR: missing data for column “coluna2”
SQL state: 22P04
Context: COPY impostos, line 1: “01012100;x;0;–Reprodutores de raca pura;26.75;27.35;”
Banco
CREATE TABLE impostos (
coluna1 varchar(500),
coluna2 varchar(500),
coluna3 varchar(500),
coluna4 varchar(500),
coluna5 varchar(500),
coluna6 varchar(500));
Arquivo
01012100;;0;–Reprodutores de raca pura;26.75;27.35;
01012900;;0;–Outros;26.75;28.45;
01013000;;0;-Asininos;26.75;29.50;
01019000;;0;-Outros;26.75;29.50;
01022110;;0;Prenhes ou com cria ao pé;14.80;15.40;
01022190;;0;Outros;14.80;15.40;
Talvez seja problema de codificação. Tentei importar uma tabela, com codificação padrão em UTF-8, e não consegui. A importação encontrava erro já na linha 1. Alterei para LATIN1 e ocorreu sem problemas.
Rodei o seu arquivo aqui e resolvi o problema adotando a codificação LATIN1 e excluindo o último “;” de cada linha no arquivo .txt. Utilizei os dados dessa forma:
01012100;;0;–Reprodutores de raca pura;26.75;27.35
01012900;;0;–Outros;26.75;28.45
01013000;;0;-Asininos;26.75;29.50
01019000;;0;-Outros;26.75;29.50
01022110;;0;Prenhes ou com cria ao pé;14.80;15.40
01022190;;0;Outros;14.80;15.40
Bom dia,
Estou importando um txt para um banco postgresql que eu criei pelo rails e estou importando pelo pgAdmin, o problema é que o indice tem que ser gerado automatico pois meu banco naum tinha indice antes, o scaffold gerou um campo indice para mim, quando coloco para importar da erro pois o primeiro campo que estou importando não é numero, alguma ideia para me ajudar?
Para este procedimento, trocando o TXT, por XLS(excel), é igual ao exemplo acima?
Uma sugestão no seu caso é salvar a planilha em txt com delimitadores “;”. Assim tu consegue importar seguindo o exemplo do Anderson. Tu faz isso na opção “Salvar como” do Excel.
Estou com uma base de dados de 2gb em arquivo txt para passar pro Postgres, esse comando me ajudará pacas…
Só que no teste deu esse erro:
ERRO: não pôde abrir arquivo “D:\fazendas.txt” para leitura: Invalid argument
SQL state: XX000
Dhiego,
Seu arquivo se chama realmente “fazendas” e está no diretório “D:”?
Verifique isso.
Abraço!
olá muito boa a dica mas estou a uns dias procurando na internet e nao consigo achar nada relacionado, preciso fazer a importação de um arquivo em txt que tem um cabeçalho como faço para ignorar o cabeçalho e importar somente os dados no postgreSQL?
Mauro,
E você não pode criar uma cópia desse arquivo removendo o cabeçalho?
Abraço!
Olá Anderson o arquivo em questão não pode ser modificado ele tem mais ou menos 15 linhas com informações e só apartir da 16º linha que começam os dados
abraço!
Inclua no copy o comando header csv
assim ele copiará o conteúdo ignorando o cabeçalho do arquivo.
Abraço.
Anderson, utilizei o copy e deu certo. Há alguma forma de fazer replace através do arquivo? Estou utilizando o comando update, mas está muito lento. Obrigado!
Olá Anderson, tudo bem?
ao importar o arquivo txt “fazendas” para meu PostGis, dá o seguinte erro:
“ERRO: sequência de bytes é inválida para codificação “UTF8″: 0x91
CONTEXT: COPY fazendas, linha 1”
Eu devo alterar o Encoding do banco?
Abraços, Gil.
como faço para importar um arquivo txt que está da seguinte forma:
CKI1072 2013-09-0610:232013-05-17 dantas/marcos 0001
seu arquivo está separado por espaço ou tabulação,
inclua no delimitador do seu copy a diretiva e” ” ou e”\t”
Abraço.
Excelente dica, quebrei a cabeça o dia inteiro tentando importar um arquivo gigante para o banco, resolvi o problema em 5 min com sua dica rs!
Que legal saber disso. Obrigado pelo comentário Samir.
Abraço!
Parabéns, me ajudou bastante consegui importar várias tabela, estou tentado importar outro txt e tem a alguns campos que tem data e outros está 00/00/0000 como faço para importar pois dá erro quando encontrada data inválida.
Anderson, poderia me dizer se essa versão 1.11, já vem incluido PostgresSQL. Postgis se não qual a versão mais adequada para trabalhar com a versão 1.11.
Angela, tudo bem?
Você está confundindo as coisas. O PostgreSQL não vem integrado na instalação do gvSIG. Você tem que baixá-lo e depois de instalar, habilitar a extensão PostGIS. Entendeu? Abraço!
Anderson, poderia me indicar uma versão instável do gvsig.
Angela, tudo bem?
A versão mais recente e estável é a 1.11 final: http://www.gvsig.org/web/projects/gvsig-desktop/official/gvsig-1.11/downloads
Bom poder contar com sua visita. Abraço.
sim quase 900 mil rsrs
são a base de dados dos correios.
dentro dos txt esta vendo assim
INSERT INTO bairros (cd_bairro, cd_cidade, ds_bairro_nome) VALUES (‘1′, ’16’, ‘Abrahão Alab’);
está prontinho uma mão na roda, mas nao estou conseguindo fazer de forma automatica agora ja ate tentei copiar e colar, porem são muitos registros e nao vai, agora de punhado em punhado vai.
obrigado pela atenção 😀
Boa tarde.
Tenho uns arquivos que estao em txt sobre o correios.
porem eles contem um insert into para cada endereço, nao estou conseguindo inserir atravez do copy, existe outra forma?
abraços
Olá Welington, tudo bem?
São muitos registros?
Abraço!
Esqueci de colocar o erro ai
ERROR: invalid input syntax for type double precision: “1164,71”
CONTEXT: COPY importa_pedido, line 5, column pedido_vlliqabe: “1164,71”
Um abraço
Você não pode fazer uma substituição desse caracter usando a função em um editor de texto?
Parabens pela dica está me ajudando muito, só que me apertei em um probleminha
Meu arquivo texto que estou levando pra tabela tem um campo valor. nao ta deixando eu importar por causa da virgula que deveria ser ponto. tem como contornar essa situação?
MEU AMIGO SEU EXEMPLO ESTA MUITO BEM ESPLICADO, TENTEI FAZER, MAS RECEBI UMA MENSSAGEM DE ERRO (ERROR: character 0xe28098 of encoding “UTF8” has no equivalent in “LATIN1”) E POR MORREU A HISTORIA, SE VC PUDER ME INFORMAR COMO CONTORNAR ESTA SITUAÇÃO FICAREI MUITO AGRADECIDO
MOACIR TEIXEIRADE MELO
Olá Moacir, tudo bem?
Seu banco de dados foi criado com qual codificação (UTF8 ou Latin 1)?
Pode ser isso. Abraço!
Oi, eu estou tendo este mesmo problema ao fazer o insert de alguns arquivos na base de dados.
Os arquivo que estou fazendo os inserts foram retirados da mesma base de dados. Vejo que não deveriam ocorrer erros, porém ocorre o erro:
ERROR: character 0xe28098 of encoding “UTF8” has no equivalent in “LATIN1”
********** Erro **********
ERROR: character 0xe28098 of encoding “UTF8” has no equivalent in “LATIN1”
SQL state: 22P05
Alguém sabe como resolver isso?
Olá bem interessante este seu post Anderson parabens…
De acordo com algumas perguntas acima conclui, que se os campos do arquivos .txt, devem estar na mesma ordem da tabela existente no postgreSQL. Supondo que eu queira
atualizar apenas um ou dois campos da minha tabela como seria a escrita do arquivo .txt
devo manter espaços em branco ‘ ‘
01;sol nascente;”;”;”;
????
Nestes casos eu recomendo o uso de um SQL com o comando UPDATE.
AbraçO!
Anderson,
A dica foi simples, porém muito importante!
Foi necessário alterar apenas as aspas do comando “COPY fazendas FROM ‘D:/fazendas.txt‘ using delimiters ‘;‘;” por aspas simples ( ‘ ).
Parabéns!
Anderson,
Gostaria de complicar um pouco seu exemplo. Um tempo atrás tive problemas com este mesmo comando na importação de uma tabela que tinha um campo de descrição Observação (normalmente com mais de 200 caracteres e com caracteres especiais, por exemplo, tabulações). Você não acha que a omissão da linha de título pode trazer alguns problemas na importação de tabelas longas, como mais de 30 campos?
Olá Ernesto, tudo bem?
Não, em geral não há esta limitação. Mas deve-se tomar cuidado para que os dados estejam na ordem correta das colunas na tabela de atributos para que, por exemplo, não haja erro ao tentar inserir dados do tipo nominal em um campo do tipo data.
Agradeço por seu comentário tão interessante.
Até aqui não tive problemas como o mencionado por você. Abraço!
Muito obrigada, pelo passo a passo. Estava precisando de tal informaçao, para importar arquivo xls, mas sua dica já resolveu meu problema.
Parabéns, Anderson, pelo excelente trabalho.
Forte abraço,
Valtania
Oi Valtania, tudo bem?
Fico muito feliz por ter ajudado de alguma forma por meio deste tutorial. Um Abraço!
Muito bom este tutorial, simples e objetivo. Parabéns Anderson e obrigado pela ajuda que sempre vem nos dando no desenrolar do geoprocessamento.
Lembrando que as pessoas que trabalham com o .csv(ponto por vírgula) também pode utilizar esse procedimento com o comando COPY!!!
Olá Anderson,
Primeiramente, meus sentimentos com relação ao seu sogro. Que o consolo do nosso Deus esteja sobre você, sua esposa e familiares.
Estava estranhando o “longo” período sem postagem. Pois sei que mesmo atarefado você encontra tempo para as postagens. No entanto, o motivo acima mais que justifica.
Gostaria particularmente de agradecer este post. Era uma dúvida que eu tinha quando do treinamento. Mas você como sempre esclarecendo todos as dúvidas!
Um abraço,
Olá Vinícius, como você está?
Agradeço de coração seu comentário. Fico também feliz por este tutorial ter sido útil. Mantenha contato.
Um Abraço!
BLZ de informação Anderson, otimas nformções vc tem postado. Parabéns!
Gostaria que vc me envia-se ou informar-se como os dados no TXT estavam ou devem está descrito.
Olá Isaías, tudo bem?
Os dados no TXT devem estar como mostrados no passo 1, sem nome de cabeçalho e usando como delimitador ponto e vírgula.
Dados do tipo texto devem estar entre aspas. Ex: ‘Anderson Medeiros’. Qualquer dúvida, entre em contato.
Obrigado pelo comentário.
Abraço!
Muitooo bomm. Um certo tempo estava com uma dificuldade devido a um grande numero de dados. 🙂 Deve-se ter cuidado com arquivos que contenham dados incompletos.
vlw pelo post anderson!
Obrigado pelo comentário e elogio Jarbas. Que bom que ele foi útil para você.
Um Abraço!