Insert data from a TXT into a DB

Asked

Viewed 220 times

1

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

Follows 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 databank, he shows me this:

inserir a descrição da imagem aqui

And it goes through 255 more records, like cascading, showing each line that the while traveled, as if it were only travelling to my condition and returning the while.

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

Updating

           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

Follow the file text as requested.

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

mysql delphi

  • Put a piece of txt, 5 lines is enough.

1 answer

1


Two doubts arose:

  1. Why the inc(i)?
  2. How your code compiled with with DataModule1.ZQuery1 do?

But back to your question:

Why doesn’t 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.

With each Readln a group of variables is filled in, getting to the point where they all are and finally you have a complete record.

An easy way to circumvent the logic error would be before filling in Zquery1 to check that all variables are filled in and right after deleting them all.

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.

  • The i, eh to count the entered records show me in a select and the with It was just a test I took...

  • The problem is that there will be information on a line , I made the code to srmazerar the whole line in a blob and select it to show me, I managed to zenar , the only problem is that img... to which I posted...

  • The biggest error was not even programming, but logic. Take a look at Edit.

  • Tntei fzer what he said, but it does not catch anything , it is empty the bank, compiled the code straight, but like, the logic would not be eerrada , it is worth the way to make each line of txtarmazene in a field in db...

  • Each ReadLN will pick up a part of the information you need. The block is only complete at last ReadLN block, trade my if for if copy(lTemp, 1, 3) = 'FAB' then

  • And those ifs I made are obsolete? if (copy(lTemp, 23, 3) = 'GNF') and (copy(lTemp, 39, 3) = 'COO') for example...

  • No, only replaces the only if of my block by what I posted in the comment.

  • My friend worked !!! Thank you!!!

  • Whenever you compile correctly and some error of this kind appears, think about logic. What I usually do is take pen and paper to see if the solution really works.

  • Beauty man, I’m going to adopt this idea ESMA for me, thank you very much

Show 5 more comments

Browser other questions tagged mysql delphi

You are not signed in. Login or sign up in order to post.