4 Replies Latest reply: Jul 17, 2015 8:08 AM by German Torres RSS

    Extract Data from TXT file

    German Torres

      Hi to all!!!

      I need to extract 4 spefic datas from a non parsed txt file.

       

      The example file its attached.

       

      The datas tha i need are:

      - Pagador nro.

      - Nro. Doc. Pago

      - Fecha pago

      - Importe

       

      In the example, the result should be:

      - Pagador nro. = 9663322

      - Nro. Doc. Pago = 116062

      - Fecha pago  =  16/07/15

      - Importe  = 19.787,24

       

      Please, can some help me????

       

      Regards!!!

        • Re: Extract Data from TXT file
          Massimo Grossi

          1.png

           

          Directory;

           

          tmp:

          LOAD

            *,

            if(wildmatch(@1, '*Pagador nro.*'), subfield(trim(replace(@1, 'Pagador nro.', '')), ' ', 1)) as Pagador,

            if(wildmatch(@1, '*Nro. Doc. Pago*'), Right(@1, len(@1) - index(@1, 'Nro. Doc. Pago') - len('Nro. Doc. Pago'))) as NroDocPago,

            if(wildmatch(@1, '*Fecha pago*'), trim(Right(@1, len(@1) - index(@1, 'Fecha pago') - len('Fecha pago')))) as FechaPago,

            if(wildmatch(@1, '*  Importe  *'), trim(Right(@1, len(@1) - index(@1, 'Importe') - len('Importe')))) as Importe

            ;

          LOAD

            @1

          FROM

          OPAGO_47480_2.TXT

          (txt, codepage is 1252, no labels, delimiter is '\t', msq)

          Where WildMatch(@1, '*pagador*', '*fecha*', '*importe*')

          ;

           

          final:

          load Pagador Resident tmp where len(trim(Pagador))>0;

          join (final) load NroDocPago Resident tmp where len(trim(NroDocPago))>0;

          join (final) load FechaPago Resident tmp where len(trim(FechaPago))>0;

          join (final) load Importe Resident tmp where len(trim(Importe))>0;

           

          DROP Table tmp;

            • Re: Extract Data from TXT file
              German Torres

              Massimo!!!

              Thanks a Lot!!!

               

              It works for one file. But i have many files.

              How can i join all the files results???

               

              Regards!!!

                • Re: Extract Data from TXT file
                  Massimo Grossi

                  Directory;

                   

                  for Each file in FileList('OPAGO_47480_*.TXT')

                   

                    tmp:

                    LOAD

                    *,

                    if(wildmatch(@1, '*Pagador nro.*'), subfield(trim(replace(@1, 'Pagador nro.', '')), ' ', 1)) as Pagador,

                    if(wildmatch(@1, '*Nro. Doc. Pago*'), Right(@1, len(@1) - index(@1, 'Nro. Doc. Pago') - len('Nro. Doc. Pago'))) as NroDocPago,

                    if(wildmatch(@1, '*Fecha pago*'), trim(Right(@1, len(@1) - index(@1, 'Fecha pago') - len('Fecha pago')))) as FechaPago,

                    if(wildmatch(@1, '*  Importe  *'), trim(Right(@1, len(@1) - index(@1, 'Importe') - len('Importe')))) as Importe

                    ;

                    LOAD

                    @1

                    FROM

                    $(file) //OPAGO_47480_2.TXT

                    (txt, codepage is 1252, no labels, delimiter is '\t', msq)

                    Where WildMatch(@1, '*pagador*', '*fecha*', '*importe*')

                    ;

                   

                    final:

                    load Pagador Resident tmp where len(trim(Pagador))>0;

                    join (final) load NroDocPago Resident tmp where len(trim(NroDocPago))>0;

                    join (final) load FechaPago Resident tmp where len(trim(FechaPago))>0;

                    join (final) load Importe Resident tmp where len(trim(Importe))>0;

                    DROP Table tmp;

                   

                    final2:

                    load '$(file)' as File, * Resident final;

                    DROP Table final; 

                   

                  NEXT;

                   

                  1.png