Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mikue2015
New Contributor

Convert Plain Text File to Table

Hi all,

how is it possible to convert a text file of the form:

Rank

Grade

Username

Followers

Channel Views

1

A++

justin

3,784,184

21,805,586

2

A++

syndicate

1,880,518

35,578,021

3

A++

riotgames

1,622,549

703,559,320

4

A++

captainsparklez

1,135,136

18,164,175

5

A++

PhantomL0rd

944,461

79,518,626   

into a table of the form:

Rank     Grade     Username     Followers     Channel Views

1    A++    justin        3,784,184    21,805,586

2    A++    syndicate    1,880,518    35,578,021

3    A++    riotgames    1,622,549    703,559,320

4    A++    captainsparklez    1,135,136    18,164,175

5    A++    PhantomL0rd    944,461        79,518,626

Thanks for the help!

Michael

1 Solution

Accepted Solutions

Re: Convert Plain Text File to Table

Hi,

one solution could be:

QlikCommunity_Thread_165280_Pic1.JPG

tabInput:

LOAD Div(RecNo()-1,5)+1 as RecNo,

    Mod(RecNo()-1,5)+1 as ColNo,

    [@1:n] as ColVal

FROM [https://community.qlik.com/servlet/JiveServlet/download/781397-166085/Follower.txt] (fix, codepage is 1252);

mapColNames:

Mapping LOAD

  'Col'&ColNo,

  ColVal

Resident tabInput

Where RecNo=1;

tabOutput:

Generic

LOAD RecNo-1 as ID,

    'Col'&ColNo,

  ColVal

Resident tabInput

Where RecNo>1;

DROP Table tabInput;

RENAME Fields using mapColNames;

hope this helps

regards

Marco

5 Replies
mov
Esteemed Contributor III

Re: Convert Plain Text File to Table

I'm sure there are more effective ways, but here is my exercise attached.

mov
Esteemed Contributor III

Re: Convert Plain Text File to Table

A little cleaner version...

MVP
MVP

Re: Convert Plain Text File to Table

DIRECTORY;

source:

load div(rowno()-1,5)+1 as row, rowno() as id, line;

LOAD @1 as line

FROM Follower.txt

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

let s='';

for i=1 to 5

  let v$(i)= peek('line', $(i)-1);

  let s='$(s)' & chr(10) & 'SubField(line,' & chr(39) & '@' & chr(39) & ', ' & $(i) &') as [' & v$(i) & ']'& if($(i)<>5, ',');

NEXT;

final:

load $(s) ;

load

  row, concat(line, '@', id) as line

Resident source

where row <> 1

group by row;

DROP Table source;

1.png

Re: Convert Plain Text File to Table

Hi,

one solution could be:

QlikCommunity_Thread_165280_Pic1.JPG

tabInput:

LOAD Div(RecNo()-1,5)+1 as RecNo,

    Mod(RecNo()-1,5)+1 as ColNo,

    [@1:n] as ColVal

FROM [https://community.qlik.com/servlet/JiveServlet/download/781397-166085/Follower.txt] (fix, codepage is 1252);

mapColNames:

Mapping LOAD

  'Col'&ColNo,

  ColVal

Resident tabInput

Where RecNo=1;

tabOutput:

Generic

LOAD RecNo-1 as ID,

    'Col'&ColNo,

  ColVal

Resident tabInput

Where RecNo>1;

DROP Table tabInput;

RENAME Fields using mapColNames;

hope this helps

regards

Marco

Re: Convert Plain Text File to Table

Hi,

loading the file as-is, just with additional column and record numbers, the table view could also be generated using a pivot table:

QlikCommunity_Thread_165280_Pic2.JPG

LOAD Div(RecNo()-1,5)+1 as RecNo,

    Mod(RecNo()-1,5)+1 as ColNo,

    [@1:n] as ColVal

FROM [https://community.qlik.com/servlet/JiveServlet/download/781397-166085/Follower.txt] (fix, codepage is 1252);

hope this helps

regards

Marco

Community Browser