(Resolvido) Problemas com importação de tabela e

[chrystoffer]

Olá amigos, bom dia. Venho procurar ajuda novamente.

Dias atrás importei uma tabela em csv de 9GB, tive algumas dúvidas que foram esclarecidas em outro tópico (www.scriptbrasil.com.br/forum/topic/180070-resolvido-update-e-inner-join-em-um-banco-muito-grande/) e funcionou perfeitamente.

Conferimos a quantidade de linhas que ficou no bd e com o csv, revisamos algumas linhas.. tudo certo.

Agora tenho que fazer a mesma coisa, mas com outra tabela e outro arquivo csv também de 9GB, quase 10GB.

Fiz os mesmos comandos apenas alterando o nome do arquivo e o nome da tabela... terminado fomos conferir, o bd ficou com mais linhas do que o próprio arquivo!! O que pode estar gerando essas linhas a mais? Não tenho como conferir linha por linha, pois são mais de 18 milhões de linhas com 39 'campos' dentro da tabela...
Abaixo os comandos que executei em ambas, tanto 2013 como 2014 apenas alterando o nome da tabela:

Edit: Bom, importei novamente e descobri um dos problemas. Ele importou normal agora.. mas perde linha quando eu removo colunas que não vou usar na tabela. Por exemplo...

O arquivo tem 19.444.896 linhas, quando importo o bd fica exatamente com a mesma quantia de linhas... quando removo as tabelas que não são necessárias o bd diminui para 19.443.743 linhas. No arquivo todas as linhas estão preenchidas pois são todas de campos obrigatórios. Resumindo, quando removo colunas que não vou usar, perco linhas

Editado Julho 27, 2015 por chrystoffer

[Denis Courcy]

Quando você remove após a importação de csv para mysql?

Ou seja, após estar em sua base de dados mysql?

[chrystoffer]

Isso... depois que já está feito o bd eu removo o que não vai ser usado, e diga-se de passagem muita coisa. De 10gbs fica 3gbs +-
Estou fazendo um teste agora de importar somente as colunas que vou usar para ver como fica.
Usei o comando:
INTO TABLE e14cv (@remover1,adicionar2,@remover3,@remover4...)

E para abrir o csv uso o ultra-edit... unico programa que consegui abrir esses arquivos.
Novamente, obrigado pela resposta Denis

Editado Julho 22, 2015 por chrystoffer

[Denis Courcy]

Vale a pena criar uma tabela temporária, com engine MyISAM, com um único campo, para receber cada linha de seu csv e tratar, em um segundo momento, estes registros para a tabela definitiva.

[chrystoffer]

Olá Denis.. fiz um script em php para tentar identificar as linhas e deu seguinte problema.
No csv tem 39 campos e para identificar cada campo separaram por , (virgula) em cada linha... acontece que várias linhas escreveram errado algumas opções adicionando vírgula no meio do texto ocasionando que o sistema acha que tem mais campos na linha. Por exemplo na linha 8 aonde mostra:

"PAGAMENTO SERVICOS DE FRETE E LOCACAO, P/XVII OLIMPIADA ESTUDANTIL, CFE. DECRETO 092/2008." (virgula em LOCACAO, ESTUDANTIL,)

aonde deveria ser apenas

"PAGAMENTO SERVICOS DE FRETE E LOCACAO P/XVII OLIMPIADA ESTUDANTIL CFE. DECRETO 092/2008." (sem vírgula)

E isso aconteceu em várias outras linhas... pode ser por isso que tá "bugando" todo o bd?

[Denis Courcy]

Sim. por isso aconselhei que você importasse para uma tabela com um único campo varchar(1000) por registro.

Voce esta usando o load data infile para importação?

Se sim faça assim:

create table tempimport(
linha varchar(1000)
)engine MyISAM;
LOAD DATA  INFILE 'file_name.txt'
INTO TABLE tempimport
LINES
TERMINATED BY '\r\n';

Use \r\n se estiver usando windows como seu sistema operacional ou

\n se fou outro sistema operacional.

Após importar paraa tabela temporária trate a importação para a tabela definitiva usando uma storage procedure ou sua liguagem de programação preferida.

[chrystoffer]

Obrigado pela ajuda novamente Denis. Vou fazer esse teste e assim que tiver alguma resposta volto a postar aqui.
Por enquanto, muito obrigado mesmo :)

[chrystoffer]

Olá... então usando \r\n não grava nada no bd. Não fui eu que escrevi esse csv, peguei pronto.

Tentei executar o comando

LOAD DATA LOCAL INFILE '2014.csv'
INTO TABLE `teste2014`
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

E agora ele conseguiu identificar que o que tem dentro de "aspas mesmo, com virgula" é um campo apenas.. ficou tudo certo. Mas o problema das linhas continua.
Usando esse comando gravou 19.443.743 linhas(mesma quantia de linhas quando eu removia antes as tabelas que não vou usar) enquanto o arquivo tem 19.444.897 linhas (contando com a primeira row)

Agora outro problema que encontrei, não sei se pode ser isso... algumas linhas terminam assim:

0,

e outras simplesmente

85

45

12

Todas as linhas não deveriam terminar com ou em virgula?? Eu estou executando no Windows tudo isso, mas fazendo igual você disse com TERMINATED BY '\r\n' ele não grava nada, só com \n

Por exemplo essas quarto últimas linhas:

C > 2014,6,88330,PREV-XANGRI-LÁ,129209,11,PREV XANGRI-LÁ,1,PREV XANGRI-LÁ,ATR,E,2014,2014,2014-12-30,2014-12-30,2014000059,4,Administração,122,Administração Geral,25,Gestão do RPPS Municipal,2086,MANUTENÇÂO PREV XANGRILA,3.1.90.11,31901101010000,Vencimentos e Vantagens Fixas - Servidores,400,RPPS - Livre,9,PREVFOLHA,20181811000143,,"PELA DESPESA EMPENHADA-REF A DIFERENCA EXPORTACAO/IMPORTACAO FOLHA/SAPO SALARIOS DOS SERVIDORES- EVENTO 56,",3988.86,0,,0,

C >

2014,6,88330,PREV-XANGRI-LÁ,129209,11,PREV XANGRI-LÁ,1,PREV XANGRI-LÁ,ATR,L,2014,2014,2014-12-30,2014-12-30,2014000059,4,Administração,122,Administração Geral,25,Gestão do RPPS Municipal,2086,MANUTENÇÂO PREV XANGRILA,3.1.90.11,31901101010000,Vencimentos e Vantagens Fixas - Servidores,400,RPPS - Livre,9,PREVFOLHA,20181811000143,,PELA DESPESA EMPENHADA-REF A DIFERENCA EXPORTACAO/IMPORTACAO FOLHA/SAPO SALARIOS DOS SERVIDORES- EVENTO 56,,60,3988.86,0,

C >

2014,6,88330,PREV-XANGRI-LÁ,129209,11,PREV XANGRI-LÁ,1,PREV XANGRI-LÁ,ATR,P,2014,2014,2014-12-30,2014-12-30,2014000059,4,Administração,122,Administração Geral,25,Gestão do RPPS Municipal,2086,MANUTENÇÂO PREV XANGRILA,3.1.90.11,31901101010000,Vencimentos e Vantagens Fixas - Servidores,400,RPPS - Livre,9,PREVFOLHA,20181811000143,,PELA DESPESA EMPENHADA-REF A DIFERENCA EXPORTACAO/IMPORTACAO FOLHA/SAPO SALARIOS DOS SERVIDORES- EVENTO 56,,0,,60,0.00

C >

2014,6,88330,PREV-XANGRI-LÁ,129209,11,PREV XANGRI-LÁ,1,PREV XANGRI-LÁ,ATR,P,2014,2014,2014-12-30,2014-12-30,2014000059,4,Administração,122,Administração Geral,25,Gestão do RPPS Municipal,2086,MANUTENÇÂO PREV XANGRILA,3.1.90.11,31901101010000,Vencimentos e Vantagens Fixas - Servidores,400,RPPS - Livre,9,PREVFOLHA,20181811000143,,PELA DESPESA EMPENHADA-REF A DIFERENCA EXPORTACAO/IMPORTACAO FOLHA/SAPO SALARIOS DOS SERVIDORES- EVENTO 56,,0,,60,3988.86

Coloquei um "C>" na frente de cada começo de linha pra identificar melhor

executei agora com um IGNORE pra tentar ver se poderia resolver algum problema, mas nada... continua faltando linhas.
Agora executei pelo prompt,pra tentar ver se descubro qual erro retorna... estamos na luta

Executei e tive o retorno, mas ainda faltando linha

Query OK, 19443743 rows affected, 65535 warnings <50 min >

Records: 19443743 Deleted: 0 Skipped: 0 waring 578805

Editado Julho 23, 2015 por chrystoffer

[Denis Courcy]

Se está faltando linhas, é porque o arquivo csv está sem o \n na terminação de alguma linha de registro.

Verifique o programa que está gerando o csv ou questione quem está gerando o csv

Se estiver sobrando linhas, então é porque o arquivo csv está com \n onde não deveria e está gerando registro de forma incompleta.

Peça ou faça um teste gerando o csv com algum outro caracter no fim do registro. Tipo @ ou # e faça a carga com load data infile usando este caracter como sinalizador de fim de linha(registro)

[chrystoffer]

Olá Denis... também estou achando que é problema nesse arquivo csv.

Ontem executei o comando pelo prompt pra retornar algum erro e recebi vários iguais esse:
Data truncated for column 'HISTORICO' at row XXXXX (xxxx = linha)

Alterei de varchar(255) para text o campo historico e executei novamente o comando load, que retornou os erros:

Row XXXX was truncated; it contained more data than there were input columns (xxxx = linhas diferentes do erro anterior)

Edit: meu chefe disse que esse csv é do governo e que acredita que não tem problema... perguntei se ele ou alguém editou o arquivo, ele disse que editou mas não no excel, em outro programa... isso pode estar ocasionando esses erros?

Edit2: Qual programa posso usar para acrescentar outro carácter no final de cada linha? Visto que o arquivo tem 10gb e quase 20milhões de linhas?

Editado Julho 24, 2015 por chrystoffer

[chrystoffer]

Apenas para deixar registrado pra quem estiver com o mesmo problema, criei esse script em php para tentar solucionar mas no meio também da erro e trava o php:

<?php
@set_time_limit(1200000000000);
$servidor = "localhost";
$user = "root";
$senha = "";
$db = "bds";
$conexao = mysql_connect($servidor,$user) or die (mysql_error());
$banco = mysql_select_db($db, $conexao) or die(mysql_error());

mysql_query("START TRANSACTION");
$row = 1;
$commit = 1;
$handle = fopen ('2014_repartido\2014_1(6).csv',"r");
while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE) {
    $CD_ORGAO                   = $data[2];
    $NOME_ORGAO                 = $data[3];
    $TIPO_OPERACAO              = $data[10];
    $ANO_EMPENHO                = $data[11];
    $ANO_OPERACAO               = $data[12];
    $DT_EMPENHO                 = $data[13];
    $DT_OPERACAO                = $data[14];
    $NR_EMPENHO                 = $data[15];
    $CD_RUBRICA                 = $data[25];
    $DS_RUBRICA                 = $data[26];
    $CNPJ_CPF                   = $data[31];
    $VL_EMPENHO                 = $data[34];
    $VL_LIQUIDACAO              = $data[36];
    $VL_PAGAMENTO               = $data[38];
    $query = "INSERT INTO 2014phpronta (CD_ORGAO,NOME_ORGAO,TIPO_OPERACAO,ANO_EMPENHO,ANO_OPERACAO,DT_EMPENHO,DT_OPERACAO,NR_EMPENHO,CD_RUBRICA,DS_RUBRICA,CNPJ_CPF,VL_EMPENHO,VL_LIQUIDACAO,VL_PAGAMENTO) VALUES ('$CD_ORGAO','$NOME_ORGAO','$TIPO_OPERACAO','$ANO_EMPENHO','$ANO_OPERACAO','$DT_EMPENHO','$DT_OPERACAO','$NR_EMPENHO','$CD_RUBRICA','$DS_RUBRICA','$CNPJ_CPF','$VL_EMPENHO','$VL_LIQUIDACAO','$VL_PAGAMENTO')";
    $insert = mysql_query($query,$conexao);
    $row++;
    $commit++;
    if ($commit == 1000) {
        mysql_query("COMMIT");
        mysql_query("START TRANSACTION");
        $commit = 0;
    }
}  
 
fclose ($handle);
 
?>

Editado Julho 24, 2015 por chrystoffer

[Denis Courcy]

chrystoffer,

Você precisa corrigir a entrada. O programa que gera o csv. É lá que está o problema.

Não adianta corrigir o lado da importação de dados.

[chrystoffer]

Olá Denis...pois é. Eu fiz vários testes e nada de conseguir arrumar.. nem importado linha por linha com php. Vou tentar ver quem fez esse csv pois erro de comando não é. Pode fechar o tópico, e obrigado por me ajudar novamente.