topic Extract Data from TXT file in QlikView

Extract Data from TXT file

Thu, 16 Jul 2015 14:52:06 GMT

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

maxgro — Thu, 16 Jul 2015 16:14:01 GMT

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

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

Thu, 16 Jul 2015 17:10:07 GMT

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

maxgro — Fri, 17 Jul 2015 04:37:50 GMT

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

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;

Re: Extract Data from TXT file

Fri, 17 Jul 2015 12:08:54 GMT

You are the Best!!!

You save my life!!

Thanks a lot!!!!

Thast Correct!!!!