topic Re: How to Import unformatted Txt Files in QlikView

How to Import unformatted Txt Files

Thu, 29 Nov 2012 15:08:09 GMT

Hi everybody,

I have a text file in not a good format.

Can you help me to import this file correctly in Qlikview?

My file is like this (see exemple.txt join):

Start Time : 20/01/2008 10:00:00

End Time : 20/01/2008 12:00:00

Application1 : 5

Dimension1 : 2

Dimension2 : 4

Dimension3 : 1

Dimension4 : 5

Application2 : 12 Percentage of success : 0.00%

Dimension1 : 6

Dimension2 : 0

Dimension3 : 12

Dimension4 : 5

I have 2 problems:

- I use separator ":" but the time is cut not correctly, Have you any idea?

- How to integrate the row : Number of B : 0 Percentage of success : 0.00% since I want a row with Numer of B = 0 and a another row Percentage of success : 0.00%

I want in qlikview (see my excel files)

Col 1	Col 2	Col 3
Start Time	20/01/2008 10:00
End Time	21/01/2008 12:00
Application1	5	Application1
Dimension1	2	Application1
Dimension2	4	Application1
Dimension3	1	Application1
Dimension4	5	Application1
Application2	12	Application2
Percentage of success	0.00%	Application2
Dimension1	6	Application2
Dimension2	0	Application2
Dimension3	12	Application2
Dimension4	5	Application2

thank you for your help

Re: How to Import unformatted Txt Files

Gysbert_Wassenaar — Thu, 29 Nov 2012 15:51:03 GMT

Try this:

LOAD

@1 as Col1,@2 as Col2

FROM exemple.txt

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

Where len(trim(@1))>0;

load @3 as Col1,@4 as Col2

FROM exemple.txt

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

Where len(trim(@3))>0;

Re: How to Import unformatted Txt Files

Thu, 29 Nov 2012 17:30:35 GMT

Thank Gysbert,

It's not totally that i want but it's help me.

When i finished correctly this script, i post it!

Re: How to Import unformatted Txt Files

Thu, 29 Nov 2012 19:23:43 GMT

I modified my files since my need.

Can you help me?

Thank

Re: How to Import unformatted Txt Files

Fri, 30 Nov 2012 08:22:34 GMT

Hello!

Anyone else can help me to inport my txt file correctly?

I do use Previous? peek function? a loop?

I need your help...thank

Re: How to Import unformatted Txt Files

Gysbert_Wassenaar — Sat, 01 Dec 2012 18:39:05 GMT

load *,if(left(Col1,11)='Application', Col1,peek('Col3')) as Col3;

load SubField(@1, ' : ',1) as Col1,

SubField(@1, ' : ',2) as Col2;

load SubField(@1, chr(9)&chr(9)) as @1

from exemple2.txt

(txt, codepage is 1252, no labels, msq)

Re: How to Import unformatted Txt Files

Sat, 15 Dec 2012 13:23:33 GMT

Thank you very much.

I don't have time to test your script but thanks