Instrução Load Data Infile
#1
Posted 17/09/2009, 00:44
possui delimitadores, ou seja, linha de tamanho fixo. Em pesquisa descobri que a instrução LOAD DATA INFILE é capaz
de realizar uma importação em velocidade muito alta (meu arquivo tem aproxidamente 50000 linhas). Segue abaixo
uma linha do arquivo:
0010025000251618501110002543900100025027000765700850589000000000001580000
Da coluna 1 até a 3 é o numero do banco,
da 4 até 7 é o prefixo da agencia,
da 8 até 13 é a conta, e assim por diante.
Tenho um método chamado sql que executa a query que passo como parâmetro e abaixo segue um exemplo de
LOAD DATA INFILE que achei, mas não é para arquivos sem delimitadores. Alguém saberia me dizer quais as
alterações que devo fazer para conseguir importar meu arquivo????:
$rsCheques = $mySQL->sql("LOAD LOCAL DATA INFILE 'c:/CST/cxq9021509.txt' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ");
#2
Posted 17/09/2009, 01:15
Primeiramente, seja bem-vindo à nossa comunidade. É um prazer tê-lo aqui.
Bem, antes de usar essa instrução do MySQL você terá que inevitavelmente separar estes dados do arquivo. Teria como dizer exatamente todas as separações que deseja? Até o momento temos isso:
Dá para criar um script PHP bem prático e pequeno capaz de efetuar essa edição, podendo assim usar este recurso do MySQL que realmente é muito mais ágil do que executar quase 50.000 consultas.(001)(0025)(000251)618501110002543900100025027000765700850589000000000001580000
[]sAté mais
#3
Posted 17/09/2009, 20:46
Olá,
Primeiramente, seja bem-vindo à nossa comunidade. É um prazer tê-lo aqui.
Bem, antes de usar essa instrução do MySQL você terá que inevitavelmente separar estes dados do arquivo. Teria como dizer exatamente todas as separações que deseja? Até o momento temos isso:Dá para criar um script PHP bem prático e pequeno capaz de efetuar essa edição, podendo assim usar este recurso do MySQL que realmente é muito mais ágil do que executar quase 50.000 consultas.(001)(0025)(000251)618501110002543900100025027000765700850589000000000001580000
[]’s
Paulo,
É muito agradável participar de uma comunidade a qual possui pessoas tão prontas a ajudar. Segue abaixo um exemplo
de uma linha com as devidos campos separados. Todavia observe que não necessito de todas as informações da linha (
são os que estão fora dos parênteses).
01(001)(0025)(000251)(61850111)0(0025)(4390010)(0025)0(27000765700850589)0(00000000001580000)
#6
Posted 18/09/2009, 12:49
Sinta-se em casa.Paulo,
É muito agradável participar de uma comunidade a qual possui pessoas tão prontas a ajudar. Segue abaixo um exemplo
de uma linha com as devidos campos separados. Todavia observe que não necessito de todas as informações da linha (
são os que estão fora dos parênteses).
01(001)(0025)(000251)(61850111)0(0025)(4390010)(0025)0(27000765700850589)0(00000000001580000)
Bem, com campos opcionais o código que eu havia em mente muda um pouco. A idéia é a seguinte: você informa o arquivo de texto e a função cria um arquivo de dados delimitados (CSV), fazendo com que este sim seja importado pelo MySQL.
Essa conversão seria algo assim:
<?php $csv = fopen('cxq9021509.csv', 'w'); // arquivo com os dados limitados que iremos criar foreach (file('cxq9021509.txt') as $entry) { // aqui percorremos o arquivo de texto que já existe fputcsv( $csv, preg_split( '{\d{2}(\d{3})(\d{4})(\d{6})(\d{8})\d(\d{4})(\d{7})(\d{4})\d(\d{17})\d(\d{17})}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ) ); } fclose($csv); ?>Peguei essa linha sua de 75 caracteres e a multipliquei 50 mil vezes, resultando num arquivo de texto de 3,6 MB. Então rodei este código, que foi executado em 2 segundos, gerando um arquivo CSV de 3,8 MB. Isto é, a transformação necessária do arquivo levou 2 segundos. Agora ele já pode o comando
LOAD LOCAL DATA INFILE
do MySQL exatamente como você estava fazendo, apenas trocando a extensão .txt por .csv. Isso resolve seu problema?
[]sAté mais
#7
Posted 18/09/2009, 22:25
Sinta-se em casa.Paulo,
É muito agradável participar de uma comunidade a qual possui pessoas tão prontas a ajudar. Segue abaixo um exemplo
de uma linha com as devidos campos separados. Todavia observe que não necessito de todas as informações da linha (
são os que estão fora dos parênteses).
01(001)(0025)(000251)(61850111)0(0025)(4390010)(0025)0(27000765700850589)0(00000000001580000)
Bem, com campos opcionais o código que eu havia em mente muda um pouco. A idéia é a seguinte: você informa o arquivo de texto e a função cria um arquivo de dados delimitados (CSV), fazendo com que este sim seja importado pelo MySQL.
Essa conversão seria algo assim:<?php $csv = fopen('cxq9021509.csv', 'w'); // arquivo com os dados limitados que iremos criar foreach (file('cxq9021509.txt') as $entry) { // aqui percorremos o arquivo de texto que já existe fputcsv( $csv, preg_split( '{\d{2}(\d{3})(\d{4})(\d{6})(\d{8})\d(\d{4})(\d{7})(\d{4})\d(\d{17})\d(\d{17})}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ) ); } fclose($csv); ?>Peguei essa linha sua de 75 caracteres e a multipliquei 50 mil vezes, resultando num arquivo de texto de 3,6 MB. Então rodei este código, que foi executado em 2 segundos, gerando um arquivo CSV de 3,8 MB. Isto é, a transformação necessária do arquivo levou 2 segundos. Agora ele já pode o comandoLOAD LOCAL DATA INFILE
do MySQL exatamente como você estava fazendo, apenas trocando a extensão .txt por .csv.
Isso resolve seu problema?
[]’s
Prezado Paulo,
Seu código realmente é muito eficiente, pois consegui separar os campos do meu .txt de forma muito veloz. Todavia
gostaria de esclarecer algumas dúvidas:
- A primeira linha e a última linha não possuem o mesmo comprimento das demais e a informação que me interessa
na primeira linha é da posição 6 até a posição 13 e da última linha, nenhuma informação. Tem como adaptar seu
código para corrigir este problema?
- A tabela tem tblcxq902 tem que possuir a mesma quantidade de campos que o arquivo?
Sou iniciante e tenho certeza que vcs irão me ajuda muito.
Abraço e muito grato pela ateñção.
Edição feita por: osvaldo luso, 18/09/2009, 22:45.
#8
Posted 18/09/2009, 23:14
Que bom que tenha servido!Prezado Paulo,
Seu código realmente é muito eficiente, pois consegui separar os campos do meu .txt de forma muito veloz. Todavia
gostaria de esclarecer algumas dúvidas:
- A primeira linha e a última linha não possuem o mesmo comprimento das demais e a informação que me interessa
na primeira linha é da posição 6 até a posição 13 e da última linha, nenhuma informação. Tem como adaptar seu
código para corrigir este problema?
- A tabela tem tblcxq902 tem que possuir a mesma quantidade de campos que o arquivo?
Sou iniciante e tenho certeza que vcs irão me ajuda muito.
Abraço e muito grato pela ateñção.
Dá pra adaptar o código sim. Bem, a última linha podemos apenas ignorar. Como será o formato da primeira?
Quanto a tabela dever possuir a mesma quantidade dos arquivos, não necessariamente. Você pode ter mais campos na tabela. Neste caso, você especifica os campos onde irá inserir os dados (estes sim deverão ter a mesma quantidade de campos do arquivo CSV).
[]sAté mais
#9
Posted 19/09/2009, 01:19
Que bom que tenha servido!Prezado Paulo,
Seu código realmente é muito eficiente, pois consegui separar os campos do meu .txt de forma muito veloz. Todavia
gostaria de esclarecer algumas dúvidas:
- A primeira linha e a última linha não possuem o mesmo comprimento das demais e a informação que me interessa
na primeira linha é da posição 6 até a posição 13 e da última linha, nenhuma informação. Tem como adaptar seu
código para corrigir este problema?
- A tabela tem tblcxq902 tem que possuir a mesma quantidade de campos que o arquivo?
Sou iniciante e tenho certeza que vcs irão me ajuda muito.
Abraço e muito grato pela ateñção.
Dá pra adaptar o código sim. Bem, a última linha podemos apenas ignorar. Como será o formato da primeira?
Quanto a tabela dever possuir a mesma quantidade dos arquivos, não necessariamente. Você pode ter mais campos na tabela. Neste caso, você especifica os campos onde irá inserir os dados (estes sim deverão ter a mesma quantidade de campos do arquivo CSV).
[]’s
Paulo,
Aí está o formato da primeira linha:
017082009091619080000000000CXQ90200000
Só preciso da posição 6 a 13 (data) e da 28 a 33 (nome interno do arquivo). A última linha não me interessa.
Dúvidas:
-Mesmo tendo especificado o caminho para criar o .csv conforme abaixo, o mesmo foi criado em "Meus documentos" com os delimitadores. No caminho especificado foi criada uma planilha com os dados separados em celulas diferentes. Como
faço para que o arquivo com os delimitadores seja criado na pasta desejada?
$csv = fopen('c:/CST/cxq9021509.csv', 'w');
-Como faço para especificar os nomes dos campos da tabela no LOAD DATA INFILE?
Grato.
#10
Posted 19/09/2009, 16:26
Vamos lá, por partes:Paulo,
Aí está o formato da primeira linha:
017082009091619080000000000CXQ90200000
Só preciso da posição 6 a 13 (data) e da 28 a 33 (nome interno do arquivo). A última linha não me interessa.
Dúvidas:
-Mesmo tendo especificado o caminho para criar o .csv conforme abaixo, o mesmo foi criado em "Meus documentos" com os delimitadores. No caminho especificado foi criada uma planilha com os dados separados em celulas diferentes. Como
faço para que o arquivo com os delimitadores seja criado na pasta desejada?
$csv = fopen('c:/CST/cxq9021509.csv', 'w');
-Como faço para especificar os nomes dos campos da tabela no LOAD DATA INFILE?
Grato.
Como a primeira linha você só precisa de duas informações, elas não entrarão no arquivo com delimitadores, certo? Errado? Presumo que você irá usar estas informações em outro lugar, certo? Errado? Estou assumindo minha suposição:
<?php $csv = fopen('cxq9021509.csv', 'w'); $txt = file('cxq9021509.txt'); array_pop($txt); // apaga a última linha (: foreach ($txt as $line => $entry) { if ($line == 0) { // guarda a data em $date e o nome interno do arquivo em $filename se for a primeira linha list($date, $filename) = preg_split( '{\d{5}(\d{8})\d{14}(\w{6})\d{5}}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ); } else { fputcsv($csv, preg_split( '{\d{2}(\d{3})(\d{4})(\d{6})(\d{8})\d(\d{4})(\d{7})(\d{4})\d(\d{17})\d(\d{17})}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE )); } } fclose($csv); ?>
Veja que guardei as duas informações da primeira linha em variáveis.
Quanto ao caminho para criar o CSV, tente assim:
$csv = fopen('C:\\CST\\cxq9021509.csv', 'w'); // no Windows as barras são invertidas; no PHP elas precisam ser escapadas
Quanto a especificar os nomes dos campos da tabela no comando
LOAD DATA
, seria logo após do nome da tabela, entre parênteses, assim:LOAD LOCAL DATA INFILE 'C:/CST/cxq9021509.txt' INTO TABLE tblcxq902 (col1, col2, col3, col4, col5, col6, col7, col8, col9) FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
Segue a documentação oficial do
LOAD DATA
: http://dev.mysql.com.../load-data.html []sAté mais
#11
Posted 22/09/2009, 00:37
Vamos lá, por partes:Paulo,
Aí está o formato da primeira linha:
017082009091619080000000000CXQ90200000
Só preciso da posição 6 a 13 (data) e da 28 a 33 (nome interno do arquivo). A última linha não me interessa.
Dúvidas:
-Mesmo tendo especificado o caminho para criar o .csv conforme abaixo, o mesmo foi criado em "Meus documentos" com os delimitadores. No caminho especificado foi criada uma planilha com os dados separados em celulas diferentes. Como
faço para que o arquivo com os delimitadores seja criado na pasta desejada?
$csv = fopen('c:/CST/cxq9021509.csv', 'w');
-Como faço para especificar os nomes dos campos da tabela no LOAD DATA INFILE?
Grato.
Como a primeira linha você só precisa de duas informações, elas não entrarão no arquivo com delimitadores, certo? Errado? Presumo que você irá usar estas informações em outro lugar, certo? Errado? Estou assumindo minha suposição:<?php $csv = fopen('cxq9021509.csv', 'w'); $txt = file('cxq9021509.txt'); array_pop($txt); // apaga a última linha (: foreach ($txt as $line => $entry) { if ($line == 0) { // guarda a data em $date e o nome interno do arquivo em $filename se for a primeira linha list($date, $filename) = preg_split( '{\d{5}(\d{8})\d{14}(\w{6})\d{5}}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ); } else { fputcsv($csv, preg_split( '{\d{2}(\d{3})(\d{4})(\d{6})(\d{8})\d(\d{4})(\d{7})(\d{4})\d(\d{17})\d(\d{17})}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE )); } } fclose($csv); ?>
Veja que guardei as duas informações da primeira linha em variáveis.
Quanto ao caminho para criar o CSV, tente assim:$csv = fopen('C:\\CST\\cxq9021509.csv', 'w'); // no Windows as barras são invertidas; no PHP elas precisam ser escapadas
Quanto a especificar os nomes dos campos da tabela no comandoLOAD DATA
, seria logo após do nome da tabela, entre parênteses, assim:LOAD LOCAL DATA INFILE 'C:/CST/cxq9021509.txt' INTO TABLE tblcxq902 (col1, col2, col3, col4, col5, col6, col7, col8, col9) FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
Segue a documentação oficial doLOAD DATA
: http://dev.mysql.com.../load-data.html
[]’s
Paulo,
Acho que fiz conforme sua orientação, porém o programa nao grava na tabela e não retorna nenhum erro, veja:
Linha do arquivo cxq9021509.csv:
1;32;2891;12856;1;32;1300314013;850517;249300;1908;20090818;32;10007;20090917
Trecho de código que chama a função sql, para gravação no BD somente dos campos Banco, Agencia e Conta:
$rsCheques = $mySQL->sql("LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'");
Obs: O primeiro campo da tabela é o ID do tipo autoincremento.
Edição feita por: osvaldo luso, 22/09/2009, 00:41.
#12
Posted 22/09/2009, 11:54
Opa,Paulo,
Acho que fiz conforme sua orientação, porém o programa nao grava na tabela e não retorna nenhum erro, veja:
Linha do arquivo cxq9021509.csv:
1;32;2891;12856;1;32;1300314013;850517;249300;1908;20090818;32;10007;20090917
Trecho de código que chama a função sql, para gravação no BD somente dos campos Banco, Agencia e Conta:
$rsCheques = $mySQL->sql("LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'");
Obs: O primeiro campo da tabela é o ID do tipo autoincremento.
Estou certo de que tenha seguido minha orientação. Porém, perdão, eu errei!
Segue nossa consulta SQL:
LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'Eu não havia reparado antes, mas o
LOCAL
deve vir após o LOAD DATA
, sendo portanto LOAD DATA LOCAL
. Outro problema é o ponto-vírgula no lugar da vírgula em FIELDS TERMINATED BY
. Eu já tinha "arrumado" mas acho que tu não percebeu. Outra coisa: eu errei ao te dizer que o nome dos campos viriam após o nome da tabela. Eles ficam no final, após toda "configuração" do comando. Portanto, nossa consulta fica:
LOAD DATA LOCAL INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (Banco, Agencia, Conta)Isto funciona.
Outra coisa interessante que notei, que vale o comentário, é que se você quiser ignorar alguma coluna do arquivo CSV no momento da inserção, basta especificar as colunas com arroba. Exemplo: quero apenas a agência e a conta. Ficaria assim:
LOAD DATA LOCAL INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@Banco, Agencia, Conta)E por aí vai...
[]sAté mais
#13
Posted 24/09/2009, 21:49
Opa,Paulo,
Acho que fiz conforme sua orientação, porém o programa nao grava na tabela e não retorna nenhum erro, veja:
Linha do arquivo cxq9021509.csv:
1;32;2891;12856;1;32;1300314013;850517;249300;1908;20090818;32;10007;20090917
Trecho de código que chama a função sql, para gravação no BD somente dos campos Banco, Agencia e Conta:
$rsCheques = $mySQL->sql("LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'");
Obs: O primeiro campo da tabela é o ID do tipo autoincremento.
Estou certo de que tenha seguido minha orientação. Porém, perdão, eu errei!
Segue nossa consulta SQL:LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'Eu não havia reparado antes, mas oLOCAL
deve vir após oLOAD DATA
, sendo portantoLOAD DATA LOCAL
. Outro problema é o ponto-vírgula no lugar da vírgula emFIELDS TERMINATED BY
. Eu já tinha "arrumado" mas acho que tu não percebeu.
Outra coisa: eu errei ao te dizer que o nome dos campos viriam após o nome da tabela. Eles ficam no final, após toda "configuração" do comando. Portanto, nossa consulta fica:LOAD DATA LOCAL INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (Banco, Agencia, Conta)Isto funciona.
Outra coisa interessante que notei, que vale o comentário, é que se você quiser ignorar alguma coluna do arquivo CSV no momento da inserção, basta especificar as colunas com arroba. Exemplo: quero apenas a agência e a conta. Ficaria assim:LOAD DATA LOCAL INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@Banco, Agencia, Conta)E por aí vai...
[]’s
Paulo,
É perfeito! Converti o arquivo e realizei a importação em 3 segundos. Mas a minha tarefa está só começando. Espero
contar com você em novos desafios.
Muiiiito Obrigado e que Deus te ilumine.
Opa,Paulo,
Acho que fiz conforme sua orientação, porém o programa nao grava na tabela e não retorna nenhum erro, veja:
Linha do arquivo cxq9021509.csv:
1;32;2891;12856;1;32;1300314013;850517;249300;1908;20090818;32;10007;20090917
Trecho de código que chama a função sql, para gravação no BD somente dos campos Banco, Agencia e Conta:
$rsCheques = $mySQL->sql("LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'");
Obs: O primeiro campo da tabela é o ID do tipo autoincremento.
Estou certo de que tenha seguido minha orientação. Porém, perdão, eu errei!
Segue nossa consulta SQL:LOAD LOCAL DATA INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 (Banco, Agencia, Conta)FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'Eu não havia reparado antes, mas oLOCAL
deve vir após oLOAD DATA
, sendo portantoLOAD DATA LOCAL
. Outro problema é o ponto-vírgula no lugar da vírgula emFIELDS TERMINATED BY
. Eu já tinha "arrumado" mas acho que tu não percebeu.
Outra coisa: eu errei ao te dizer que o nome dos campos viriam após o nome da tabela. Eles ficam no final, após toda "configuração" do comando. Portanto, nossa consulta fica:LOAD DATA LOCAL INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (Banco, Agencia, Conta)Isto funciona.
Outra coisa interessante que notei, que vale o comentário, é que se você quiser ignorar alguma coluna do arquivo CSV no momento da inserção, basta especificar as colunas com arroba. Exemplo: quero apenas a agência e a conta. Ficaria assim:LOAD DATA LOCAL INFILE 'c:\CST\cxq9021509.csv' INTO TABLE tblcxq902 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@Banco, Agencia, Conta)E por aí vai...
[]’s
Paulo,
É perfeito! Converti o arquivo e realizei a importação em 3 segundos. Mas a minha tarefa está só começando. Espero
contar com você em novos desafios.
Muiiiito Obrigado e que Deus te ilumine.
Paulo,
Nas linhas do arquivo texto existem duas informações de datas em posições diferentes. Preciso
capturar essas datas, formatá-las para o formato brasileiro (dd/mm/aaa) para gravar no BD
juntamente com as outras informações. Gostaria que no momento de geração do arquivo csv
essa transformação para o formato brasileiro já fosse feita. É possivel?? São as posições "(\d{8})"
do código abaixo.
Outra coisa, as informações apóso último"(\d{8})" eu gostaria que não entrassem na geração do csv, pois
existe uma quebra de linha neste ponto que esta provocando a entrada de "sujeira"no arquivo csv.
grato.
fputcsv($csv,preg_split( '{(\d{1})\d(\d{4})\d(\d{4})\d(\d{9})(\d{3})\d(\d{4})\d(\d{10})\d(\d{6})(\d{17})\d(\d{4})(\d{8})\d{20}(\d{7})\d{16}(\d{8})}', trim($entry), 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE ));
#14
Posted 24/09/2009, 22:05
[...] ó terra, terra, terra; ouve a palavra do Senhor. — Jeremias 22:29
#15
Posted 25/09/2009, 01:17
Olá Osvaldo, esta operação não é aconselhável. Principalmente se você for realizar busca entre datas na tabela usando tal campo.
Vou tratar as datas dentro do BD. Mas como faço para NÃO inserir as informações localizadas após o último
"(/d{8})" no arquivo csv. Tem quebra de linha no arquivo texto, neste ponto e essas informações
são inseridas não me interessam.
1 user(s) are reading this topic
0 membro(s), 1 visitante(s) e 0 membros anônimo(s)