Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
MarcoWedel

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

A little cleaner version...

maxgro
MVP
MVP

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

MarcoWedel

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

MarcoWedel

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