topic Re: import txt file in QlikView

import txt file

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a txt file this with this line:

18140030015-201607221410378132029382-0246RECIFE RCE RCE 0110000000032666300E 000000000000000000

Must be this:

22/07/2016 14:10:37 8132029382 24,6 8140030015 3,26663

I tried this:

LOAD

mid(@1,33,4) as RAMAL,
mid(@1,13,8) as DATA,
mid(@1,13,4) as ANO,
mid(@1,17,2) as MES,
mid(@1,19,2) as DIA,
mid(@1,39,3) as TMP_LIGACAO,
mid(@1,87,6) as VLR_S_IMPOSTO,
mid(@1,21,6) as HORA_LIGACAO,
mid(@1,2,10) as FONE_DESTINO,

@1
FROM

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

Re: import txt file

fkeuroglian — Fri, 16 Sep 2016 22:50:37 GMT

And the result?

Would be easier if you pass the txt file to help you

Fernando K.

Re: import txt file

Anonymous — Sat, 17 Sep 2016 00:48:44 GMT

the result I need is:

22/07/2016 14:10:37 8132029382 24,6 8140030015 3,26663

Re: import txt file

Anonymous — Sat, 17 Sep 2016 01:00:50 GMT

It does not show the option to include the .txt file but it has the first line of interaction.

18140030015-201607221410378132029382-0246RECIFE RCE RCE 0110000000032666300E 000000000000000000

Re: import txt file

robert_mika — Sat, 17 Sep 2016 01:27:50 GMT

T1:

load * inline [

18140030015-201607221410378132029382-0246RECIFE RCE RCE 0110000000032666300E 0000000000000000000];

load

Date(Date#(mid(@1,13,8),'YYYYMMDD'),'DD-MM-YYYY') as DATA,

num(mid(@1,87,6),'0,00000') as RAMAL,

mid(@1,27,10) as TMP_LIGACAO,

num(mid(@1,39,3),'0,00') as VLR_S_IMPOSTO,

TIME(time#(mid(@1,21,6),'hhmmss'),'hh:mm:ss') as HORA_LIGACAO,

mid(@1,2,10) as FONE_DESTINO,

Resident T1;

change name of the fields(my Spanish?) is not so good...

Re: import txt file

johnca — Sat, 17 Sep 2016 05:00:06 GMT

Another way is with preceding loads...

Result is your string:

22/07/2016 14:10:37 8132029382 2,46 8140030015 3,26663

DATA:

Load *,

DATA & ' ' &

HORA_LIGACAO & ' ' &

first_number & ' ' &

TMP_LIGACAO & ' ' &

FONE_DESTINO & ' ' &

VLR_S_IMPOSTO as Result;

Load

mid(@1,33,4) as RAMAL,

Date(Date#(mid(@1,13,8),'YYYYMMDD'),'DD/MM/YYYY') as DATA,

mid(@1,13,4) as ANO,

mid(@1,17,2) as MES,

mid(@1,19,2) as DIA,

Num(mid(@1,39,3),'0,00') as TMP_LIGACAO,

Num(mid(@1,87,6),'0,00000') as VLR_S_IMPOSTO,

Time(Time#(mid(@1,21,6),'hhmmss'),'hh:mm:ss') as HORA_LIGACAO,

mid(@1,27,10) as first_number,

mid(@1,2,10) as FONE_DESTINO;

Load * Inline [@1

18140030015-201607221410378132029382-0246RECIFE RCE RCE 0110000000032666300E 000000000000000000

];

HTH,

John

Re: import txt file

Anonymous — Mon, 19 Sep 2016 14:39:40 GMT

Thank you all, it worked.

Re: import txt file

johnca — Mon, 19 Sep 2016 14:42:51 GMT

Glad it worked for you. Please close the thread by marking one answer as Correct.