Convert Plain Text File to Table

[Anonymous]

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

[Anonymous]

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

[Anonymous]

A little cleaner version...

[maxgro]

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;

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

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:

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