Insert data from a TXT into a DB

1

While doing all these conditions, my database is responding in a strange way because literally what I'm selecting is in every line of my .txt file, returns everything, including spaces empty, which was not to occur.

Follow the code:

 
//while para ler o txt e definir as variáveis.
  AssignFile(txt, edtCaminho.Text);
  Reset(txt);
  while not eof(txt) do
  begin
    Readln(txt, lTemp);
    inc(i);
    with DataModule1.ZQuery1 do
    begin
      if (copy(lTemp, 23, 3) = 'GNF') and (copy(lTemp, 39, 3) = 'COO') then
      begin
        gnf := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 3) = 'CDC' then
      begin
        cdc := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 14, 22) = 'NÃO É DOCUMENTO FISCAL' then
      begin
        ndocf := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 10, 29) = 'COMPROVANTE CRÉDITO OU DÉBITO' then
      begin
        comp := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 22, 6) = 'CARTÃO' then
      begin
        cartao := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 24, 3) = 'VIA' then
      begin
        via := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 3) = 'COO' then
      begin
        coo := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 15) = 'Valor da compra' then
      begin
        vlcompra := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 18) = 'Valor do pagamento' then
      begin
        vlpag := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 18, 4) = 'REDE' then
      begin
        rede := copy(lTemp, 1, 48);
      end;
      if (copy(lTemp, 14, 13) = 'VISA ELECTRON') or (copy(lTemp, 17, 7) = 'MAESTRO') then
      begin
        band := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 7) = 'COMPROV' then
      begin
        comprov := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 5) = 'ESTAB' then
      begin
        estab := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 19, 4) = 'TERM' then
      begin
        term := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 15) = 'NUMERO PARCELAS' then
      begin
        numparc := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 6) = 'CARTAO' then
      begin
        cartao2 := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 11) = 'AUTORIZACAO' then
      begin
        auto := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 4) = 'ARQC' then
      begin
        arqc := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 5, 29) = 'TRANSACAO AUTORIZADA MEDIANTE' then
      begin
        trans := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 5, 20) = 'USO DE SENHA PESSOAL' then
      begin
        usodesp := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 3) = '---' then
      begin
        traco := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 8) = 'BEMATECH' then
      begin
        imp := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 6) = 'VERSÃO' then
      begin
        versao := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 3) = 'QQQ' then
      begin
        qqq := copy(lTemp, 1, 48);
      end;
      if copy(lTemp, 1, 3) = 'FAB' then
      begin
        fab := copy(lTemp, 1, 48);
      end;

      DataModule1.Zquery1.Close;
      DataModule1.ZQuery1.SQL.Clear;
      DataModule1.ZQuery1.SQL.Add('INSERT INTO lercartao(lgnf, lcdc, lndocf, lcomp, lcartao, lvia, lcoo, lvlcompra, lvlpag, lrede, lband, lcomprov, lestab, lterm, lnumparc, lcartao2, lauto, larqc, lusodesp, ltraco, limp, lversao, lqqq, lfab)');
      DataModule1.ZQuery1.SQL.Add('VALUES (:pgnf, :pcdc, :pndocf, :pcomp, :pcartao, :pvia, :pcoo, :pvlcompra, :pvlpag, :prede, :pband, :pcomprov, :pestab, :pterm, :pnumparc, :pcartao2, :pauto, :parqc, :pusodesp, :ptraco, :pimp, :pversao, :pqqq, :pfab)');
      DataModule1.ZQuery1.ParamByName('pgnf').AsString := gnf;
      DataModule1.ZQuery1.ParamByName('pcdc').AsString := cdc;
      DataModule1.ZQuery1.ParamByName('pndocf').AsString := ndocf;
      DataModule1.ZQuery1.ParamByName('pcomp').AsString := comp;
      DataModule1.ZQuery1.ParamByName('pcartao').AsString := cartao;
      DataModule1.ZQuery1.ParamByName('pvia').AsString := via;
      DataModule1.ZQuery1.ParamByName('pcoo').AsString := coo;
      DataModule1.ZQuery1.ParamByName('pvlcompra').AsString := vlcompra;
      DataModule1.ZQuery1.ParamByName('pvlpag').AsString := vlpag;
      DataModule1.ZQuery1.ParamByName('prede').AsString := rede;
      DataModule1.ZQuery1.ParamByName('pband').AsString := band;
      DataModule1.ZQuery1.ParamByName('pcomprov').AsString := comprov;
      DataModule1.ZQuery1.ParamByName('pestab').AsString := estab;
      DataModule1.ZQuery1.ParamByName('pterm').AsString := term;
      DataModule1.ZQuery1.ParamByName('pnumparc').AsString := numparc;
      DataModule1.ZQuery1.ParamByName('pcartao2').AsString := cartao2;
      DataModule1.ZQuery1.ParamByName('pauto').AsString := auto;
      DataModule1.ZQuery1.ParamByName('parqc').AsString := arqc;
      DataModule1.ZQuery1.ParamByName('pusodesp').AsString := usodesp;
      DataModule1.ZQuery1.ParamByName('ptraco').AsString := traco;
      DataModule1.ZQuery1.ParamByName('pimp').AsString := imp;
      DataModule1.ZQuery1.ParamByName('pversao').AsString := versao;
      DataModule1.ZQuery1.ParamByName('pqqq').AsString := qqq;
      DataModule1.ZQuery1.ParamByName('pfab').AsString := fab;
      DataModule1.ZQuery1.ExecSQL;
    end;
  end;
  CloseQuery;
  Closefile(txt);
  //relatório query
  DataModule1.ZQuery2.Close;
  DataModule1.ZQuery2.SQL.Clear;
  DataModule1.ZQuery2.SQL.Add('SELECT * FROM lercartao LIMIT 0, '+ IntToStr(i));
  DataModule1.ZQuery2.Open;

However, when he inserts the data into my database, it appears to me this:

And it goes on for 255 more records, like cascade, showing every line that while traveled, as if it were only traversing my condition and returning while .

Why? Could it be a problem in my Insert? or could it be a problem with my code? Any ideas?

Update

           xxxxxxxxxxxx
          xxxxxxxxxxxxxxxxxxxxxxx 
 PCA. xxxxxxxxxxxxxxx, CENTRO MOSSORO-RN
CNPJ:xxxxxxxxxxxxxxxxx  
IE:xxxxxxxxxxxxxxxxxxx        
------------------------------------------------
05/12/2013 17:34:07   GNF:xxxxxx      COO:xxxxxx
CDC:0006
             NÃO É DOCUMENTO FISCAL             
         COMPROVANTE CRÉDITO OU DÉBITO          
                     CARTAO                     
                     1ªVIA                      
COO do documento vinculado:               xxxxxx
Valor da compra  R$                        63,35
Valor do pagamento  R$                     63,35
                 REDE                           
              MASTERCARD                        
COMPR:xxxxxxxx4    VALOR:        63,35          
ESTAB:xxxxxxxxx PLASTIJxxxxx                    
xxxxxxxxxxxxxxxxx TERM:PV834358/050075          
NUMERO PARCELAS : 02                            
CARTAO: xxxx.xxxx.xxxx.xxxx                     
AUTORIZACAO: xxxxxx                             
ARQC:xxxxxxxxxxxxxxxxx                          
    TRANSACAO AUTORIZADA MEDIANTE               
    USO DE SENHA PESSOAL.                       

                               (SiTef)          

------------------------------------------------
BEMATECH xxxxxxxxxxxxxxxxxxxxx
VERSÃO:xxxxxxxx ECF:xxx LJ:xxxx
QQQQQQQQQxxxxxxx 05/12/2013 17:34:13 
FAB:xxxxxxxxxxxxxxxxxxxxx 

Follows the text of the file as requested.

The X and substitution as there is personal information of the company, but it is style it. If there is any doubt just talk.

    
asked by anonymous 04.06.2014 / 03:53

1 answer

1

Two doubts have arisen:

  • Why does inc(i) ?
  • How did your code compile with with DataModule1.ZQuery1 do ?
  • But back to your question:

    Why does not it work?

    Your TXT is not formatted according to what you have programmed. For your code to work, all information must be on the same line.

    At each ReadLN a group of variables is populated, getting to the point where all of them are and finally you have a complete record.

    An easy way to get around the logic error would be before filling in ZQuery1 to check if all variables are filled and then delete all of them.

    Simplifying a lot, something like this:

    If (trim(gnf) <> '') and (trim(cdc) <> '') ... then  //todas as outras variáveis
    begin
      DataModule1.Zquery1.Close;
      DataModule1.ZQuery1.SQL.Clear;
      DataModule1.ZQuery1.SQL.Add('INSERT INTO lercartao(lgnf, lcdc, lndocf, lcomp, lcartao, lvia, lcoo, lvlcompra, lvlpag, lrede, lband, lcomprov, lestab, lterm, lnumparc, lcartao2, lauto, larqc, lusodesp, ltraco, limp, lversao, lqqq, lfab)');
      DataModule1.ZQuery1.SQL.Add('VALUES (:pgnf, :pcdc, :pndocf, :pcomp, :pcartao, :pvia, :pcoo, :pvlcompra, :pvlpag, :prede, :pband, :pcomprov, :pestab, :pterm, :pnumparc, :pcartao2, :pauto, :parqc, :pusodesp, :ptraco, :pimp, :pversao, :pqqq, :pfab)');
      DataModule1.ZQuery1.ParamByName('pgnf').AsString := gnf;
      DataModule1.ZQuery1.ParamByName('pcdc').AsString := cdc;
      DataModule1.ZQuery1.ParamByName('pndocf').AsString := ndocf;
      DataModule1.ZQuery1.ParamByName('pcomp').AsString := comp;
      DataModule1.ZQuery1.ParamByName('pcartao').AsString := cartao;
      DataModule1.ZQuery1.ParamByName('pvia').AsString := via;
      DataModule1.ZQuery1.ParamByName('pcoo').AsString := coo;
      DataModule1.ZQuery1.ParamByName('pvlcompra').AsString := vlcompra;
      DataModule1.ZQuery1.ParamByName('pvlpag').AsString := vlpag;
      DataModule1.ZQuery1.ParamByName('prede').AsString := rede;
      DataModule1.ZQuery1.ParamByName('pband').AsString := band;
      DataModule1.ZQuery1.ParamByName('pcomprov').AsString := comprov;
      DataModule1.ZQuery1.ParamByName('pestab').AsString := estab;
      DataModule1.ZQuery1.ParamByName('pterm').AsString := term;
      DataModule1.ZQuery1.ParamByName('pnumparc').AsString := numparc;
      DataModule1.ZQuery1.ParamByName('pcartao2').AsString := cartao2;
      DataModule1.ZQuery1.ParamByName('pauto').AsString := auto;
      DataModule1.ZQuery1.ParamByName('parqc').AsString := arqc;
      DataModule1.ZQuery1.ParamByName('pusodesp').AsString := usodesp;
      DataModule1.ZQuery1.ParamByName('ptraco').AsString := traco;
      DataModule1.ZQuery1.ParamByName('pimp').AsString := imp;
      DataModule1.ZQuery1.ParamByName('pversao').AsString := versao;
      DataModule1.ZQuery1.ParamByName('pqqq').AsString := qqq;
      DataModule1.ZQuery1.ParamByName('pfab').AsString := fab;
      DataModule1.ZQuery1.ExecSQL;
      gnf := '';
      cdc := '';
      //Todas as outras variáveis
    end;
    

    This way you get around the error and get the expected result.

        
    04.06.2014 / 14:49