(Resolvido) Importar um arquivo txt para o meu bd

[claudio elias]

preciso de uma ajudinha, alguém sabe como importa para o mysql um arquivo em txt de tal forma

cpf            nome   mes      ano   total            
------------------  -------- ------  -------  ------
000.000.000-00    fulano     7     2009   00.00

são mais de 10.000 mil linhas desta forma e tenho um bd com a tabela usuario com estes campos cpf, nome, mes, ano, total

Editado Agosto 26, 2009 por Denis Courcy

[neto.joaobatista]

preciso de uma ajudinha, alguém sabe como importa para o mysql um arquivo em txt de tal forma

cpf            nome   mes      ano   total            
------------------  -------- ------  -------  ------
000.000.000-00    fulano     7     2009   00.00

são mais de 10.000 mil linhas desta forma e tenho um bd com a tabela usuario com estes campos cpf, nome, mes, ano, total

Use LOAD DATA INFILE:

LOAD DATA LOCAL INFILE "arquivo.txt" INTO TABLE sua_tabela FIELDS TERMINATED BY '\t'

Supondo é claro, que seus campos no .txt estão separados com tabs

[]'s

J. Neto

Editado Julho 9, 2009 por neto.joaobatista

[claudio elias]

Só um perguta este comando eu execulto ele aonde no prompt do mysqly, eu uso o PhpMyAdmin do Xampp

[Denis Courcy]

Só um perguta este comando eu execulto ele aonde no prompt do mysqly, eu uso o PhpMyAdmin do Xampp

Tanto faz.

Não sou fã do phpmyadmin.

Normalmente uso as ferramentas do mysql.

Como os campos do seu arquivo txt estão separados?

[claudio elias]

o arquivo esta separado desta forma

cpf                   nome                                                                    mes         ano         total            
------------------ ------------------------------------------------------------ ----------- ----------- --------------------- 
000.000.000-00 fulano                                                                    7            2009        28.00
000.000.000-00 fulano                                                                    7            2009        28.00
000.000.000-00 fulano                                                                    7            2009        28.00

você falou que tanto faz, como que fazo isto no PhpMyAdmin

a ordem e esta cpf, nome, mes, ano, total e o estaçamento e o que esta nos pontilhado da mesma forma só que na mesma linha

[Denis Courcy]

Oi, 'claudio elias'

Não fui feliz em minha pergunta de como estão separados os campos em seu arquivo.txt

Eu qgostaria que você informasse se usa caracters especiais para separação dos campos ou se esles estão separados somente com caracteres de espaço. Ou seja, se é um arquivo delimitado (por um caracter em particular) ou se é do tipo largura fixa em que a separação dos campos é pelo caracter de espaço.

Quanto ao phpmyadmin eu não uso. Não trabalho com php.

Mas creio que seja com um comando simples de sql.

Use a linha de comando do mysql. Para este fim, ela é melhor .

[claudio elias]

Obrigado pela Atenção.

os campos do arquivo.txt estão separados com espaçamento um dos outros

[Denis Courcy]

Obrigado pela Atenção.

os campos do arquivo.txt estão separados com espaçamento um dos outros

OK. Então, você deverá usar uma "muleta" para conseguir o que quer.

O primeiro passo será criar uma tabela temporária com um registro do tipo char (não varchar) com o tamanho da linha do arquivo que você quer importar.

O segundo passo é usar o comando

LOAD DATA LOCAL INFILE "arquivo.txt" INTO TABLE <tabela-temporaria> 
LINES TERMINATED BY '\r\n'

se o arquivo foi gerado no DOS/Windows ou o comando

LOAD DATA LOCAL INFILE "arquivo.txt" INTO TABLE sua_tabela" LINES TERMINATED BY '\n'

se o arquivo txt foi gerado no linux/unix. O terceiro passo será usar o comando

INSERT INTO suatabela (<seus-campos>)
SELECT substring(<campo>, x, y), ..., substring(<campo_n>, x_n, y_n) FROM <tabela-temporaria>;

O quarto passo será destruir a tabela temporária.

É importante que você faça testes em um banco de teste para não se arrepender se algo der errado.

[claudio elias]

Eu importei o arquivo.txt para a tabela mas ocorreu um pequeno problema os campos do txt vai tudo para o 1º campo da tabela , assim não separado cada colona para cada campo da tabela

da uma olhad como ficou a tabela depois do arquivo importado

CREATE TABLE `teste` (
  `id` int(11) NOT NULL auto_increment,
  `cpf` char(255) collate latin1_general_ci NOT NULL,
  `nome` char(255) collate latin1_general_ci NOT NULL,
  `mes` char(255) collate latin1_general_ci NOT NULL,
  `ano` char(255) collate latin1_general_ci NOT NULL,
  `total` char(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `cpf` (`cpf`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7;

--
-- Extraindo dados da tabela `teste`
--

INSERT INTO `teste` (`id`, `cpf`, `nome`, `mes`, `ano`, `total`) VALUES
(1, '00000000000  nomenomenomenomenomenomenomenomenom                          5           2009        28.0000', '', '', '', ''),
(2, '00000000000  nomenomenomenomenomenomenomenomeno                           5           2009        66.0000', '', '', '', ''),
(3, '00000000000  nomenomenomenomenomenomenomenomenom                          5           2009        70.1800', '', '', '', ''),
(4, '00000000000  nomenomenomenomenomenomenomenomenom                          5           2009        73.0000', '', '', '', ''),
(5, '00000000000  nomenomenomenomenomenomenomenomenom                          5           2009        78.0000', '', '', '', ''),
(6, '00000000000  nomenomenomenomenomenomenomenomenom                          5           2009        67.0000', '', '', '', '');

[neto.joaobatista]

Eu importei o arquivo.txt para a tabela mas ocorreu um pequeno problema os campos do txt vai tudo para o 1º campo da tabela , assim não separado cada colona para cada campo da tabela

Bom, tem certeza que não esqueceu nenhum passos que o Denis falou ?

1. Passo:

OK. Então, você deverá usar uma "muleta" para conseguir o que quer.

O primeiro passo será criar uma tabela temporária com um registro do tipo char (não varchar) com o tamanho da linha do arquivo que você quer importar.

mysql> create table `temp` ( linha char(255) DEFAULT NULL ) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

2. Passo:

O segundo passo é usar o comando

LOAD DATA LOCAL INFILE "arquivo.txt" INTO TABLE <tabela-temporaria> 
LINES TERMINATED BY '\r\n'

se o arquivo foi gerado no DOS/Windows ou o comando

LOAD DATA LOCAL INFILE "arquivo.txt" INTO TABLE sua_tabela" LINES TERMINATED BY '\n'

se o arquivo txt foi gerado no linux/unix.

mysql> load data local infile 'lista.txt' into table temp lines terminated by '\n';
Query OK, 7 rows affected (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

Testando:

mysql> select * from temp;
+-------------------------------------------------------------------------------------------------------------------------+
| linha                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| 000.000.000-00 fulano                                                                    1            2009        28.00 |
| 000.000.000-00 ciclano                                                                   2            2009        29.00 |
| 000.000.000-00 beutrano                                                                  3            2009        30.00 |
| 000.000.000-00 neto                                                                      4            2009        31.00 |
| 000.000.000-00 joao batista                                                              5            2009        32.00 |
| 000.000.000-00 denis courcy                                                              6            2009        33.00 |
| 000.000.000-00 claudio elias                                                             7            2009        34.00 |
+-------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

Ok, Arquivo importado... 3. Passo:

O terceiro passo será usar o comando

INSERT INTO suatabela (<seus-campos>)
SELECT substring(<campo>, x, y), ..., substring(<campo_n>, x_n, y_n) FROM <tabela-temporaria>;

mysql> insert into
    -> teste( cpf , nome , mes , ano , total )
    -> select
    -> rtrim( substr( linha ,   1 , 14 ) ) cpf,
    -> rtrim( substr( linha ,  16 , 74 ) ) nome,
    -> rtrim( substr( linha ,  90 , 12 ) ) mes,
    -> rtrim( substr( linha , 103 , 12 ) ) ano,
    -> rtrim( substr( linha , 115 , 12 ) ) total
    -> from
    ->   temp;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Testando:

mysql> select * from teste;
+----+----------------+---------------+-----+------+-------+
| id | cpf            | nome          | mes | ano  | total |
+----+----------------+---------------+-----+------+-------+
|  7 | 000.000.000-00 | fulano        |   1 | 2009 | 28.00 |
|  8 | 000.000.000-00 | ciclano       |   2 | 2009 | 29.00 |
|  9 | 000.000.000-00 | beutrano      |   3 | 2009 | 30.00 |
| 10 | 000.000.000-00 | neto          |   4 | 2009 | 31.00 |
| 11 | 000.000.000-00 | joao batista  |   5 | 2009 | 32.00 |
| 12 | 000.000.000-00 | denis courcy  |   6 | 2009 | 33.00 |
| 13 | 000.000.000-00 | claudio elias |   7 | 2009 | 34.00 |
+----+----------------+---------------+-----+------+-------+
7 rows in set (0.00 sec)

Tudo funciona 100% !!! 4. Passo

O quarto passo será destruir a tabela temporária.

mysql> drop table temp;
Query OK, 0 rows affected (0.00 sec)

[]'s

J. Neto

Editado Julho 9, 2009 por neto.joaobatista

[Samir]

Oi, gostaria de saber se alguém conhece uma solução em Linux ou Unix para a seguinte situação!

Vou receber diariamente um arquivo "aparentemente" sem formato, mas é um TXT, onde recebi o manual dos campos onde marca-los para o EXCEL converter esse arquivo em CSV, com o EXCEL eu consigo resolver meu problema, mas o processo se torna muito manual, já que terei de colocar uma pessoa para abrir o arquivo no excel, colocar 35 marcadores de colonas entre os 450 caracteres por linha deste arquivo que normalmente tem mais de 5.000 linhas.

Atualmente estamos fazendo da forma lenta e nada prática, com o excel mesmo, já pensei em fazer alguma coisa como: ler cada caractere por linha e inserir um ; nas colunas pré-determinadas, já que o arquivo é contínuo, como o exemplo abaixo;

0202LOSIL IS 0000000000074100000000741000000007200000000072500000000730=000000000000073000000

007390018800000000024000000000000173916 X000PY00000000000XD0000000R41070 0000000000 0013400000001465000000014700005300000000017690000000000259565 10000HBL000000000000000134 73643XC 0009847 000493486 03940348500

Desde já, muito obrigado,

Samir

[Denis Courcy]

'Samir'

Se sua intenção é importar este arquivo para o mysql, a solução já foi dada nos posts acima.

Se a intenção é transformar este arquivo em csv para depois ser usado em um arquivo excel, então você deve importar este arquivo para um arquivo temporário, tal como explicado nos posts acima, converter as linhas do arquivo temporário para que passem a ter os caracteres de ";" e, por último, exportar o aruivo usando o comando SELECT expressão_select,... INTO OUTFILE 'nome_arquivo' [opções_exportação] FROM tabelas_ref.

Para converter as linhas da tabela temporária para que paswsem a ter o caracter de ";" use

UPDATE <nome-da-tabela-temporária>
SET <nome-do-campo> = CONCAT(substring(<nome-do-campo>, x1, y1), ';', substring(<nome-do-campo>, x2, y2), ';',...,substring(<nome-do-campo>, xn, yn));

Para exportar a tabela temporária já convertida para um arquivo txt use

SELECT <nome-do-campo>
INTO OUTFILE <nome-do-arquivo> LINES TERMINATED BY '\r\n' 
FROM <nome-da-tabela-temporária>

[Samir]

Oi Denis, na verdade eu li os posts acima, algumas coisas eu não compreendi muito bem, inclusive sobre o comando que você respondeu para mim;

Eu vi a sua referência ao código no 8º post, mas não compreendi esta linha de comando,

UPDATE <nome-da-tabela-temporária>
SET <nome-do-campo> = CONCAT(substring(<nome-do-campo>, x1, y1), ';', substring(<nome-do-campo>, x2, y2), ';',...,substring(<nome-do-campo>, xn, yn));

Você poderia me explicar melhor sobre esta linha acima? Vejo que nesta linha você faz referência ao nome-do-campo, mas não estou entendendo onde eu vou conseguir que seja inserido uma quebra de campo entre na linha abaixo:

0000000000074100000000741000000007200000000072500000000730

da seguinte forma

000000000007410;00000007410;00000007200;00000007250;00000007300

Desculpa a minha falta de experiência com banco de dados, e muito obrigado pela sua resposta!

Samir Hanna

Editado Julho 17, 2009 por Samir

[Denis Courcy]

Oi, 'Samir'

Não há o que desculpar. Estamos aqui para aprender.

Vou responder ponto a ponto, usando um exemplo prático para que você entenda.

Imaginando que seu arquivo texto se chame trabalho.txt e que cada linha deste arquivo não ultrapasse 255 caracteres.

Vamos importar o arquivo para o banco de dados.

Como não sei o tamanho de seu arquivo vou criar uma tabela residente em disco.

Passo 1: Criando a tabela temporária

CREATE tmp_trabalho(
linha varchar(255)
)ENGINE = MYISAM;

Com a tabela temporária criada vamos importá-la para a tabela tmp_trabalho criada acima. Passo 2: Importando arquivo txt

LOAD DATA LOCAL INFILE "trabalho.txt" INTO TABLE tmp_trabalho LINES TERMINATED BY '\n'

Se o arquivo trabalho.txt foi criado no windows troque o '\n' acima por '\r\n' Agora vamos tratar o arquivo importado colocando os caracteres de ";" nos locais informados por você no post anterior Passo 3:Tratando arquivo importado

UPDATE tmp_trabalho 
SET linha = CONCAT(SUBSTRING(linha, 1, 15) ';', SUBSTRING(linha, 16, 11), ';', SUBSTRING(linha, 26, 11), ';', SUBSTRING(linha, 37, 11), ';', SUBSTRING(linha, 48, 11));

Por último vamos exportar os dados modificados da tabela, para um outro arquivo txt que chamaremos de exportacao.csv Passo 4: Exportando o arquivo

SELECT linha
INTO OUTFILE exportacao.csv LINES TERMINATED BY '\r\n' 
FROM tmp_trabalho;

Para saber sobre o que as funções CONCAT e SUBSTRING fazem, leia o manual do MySQL seção 6.3.2. Funções String

[Samir]

Oi Denis,

Bom e velho Google hehehe,

Tudo certo consegui aqui, muito obrigado,

Samir Hanna

Denis, Muito Obrigado

A sua resposta está muito melhor do que eu encontrei nos DOCs do MySQL,

Valeu mesmo,

Abraço!