topic Help to import Text File Without Delimiters in QlikView

Help to import Text File Without Delimiters

Wed, 03 Mar 2010 23:24:40 GMT

I have a text file Without Delimiters, example:

88092099 A G NAP MARTI NETO 2,5 3 5,5
88083888 ABRAAO D S 3,5 2,75 6,25
88040182 ACI GROLLI CARVALHO 4,25 3,75 8

I need to import this file in five fields:

ID NAME V1 V2 V3
88092099 A G NAP MARTI NETO 2,5 3 5,5
88083888 ABRAAO D S 3,5 2,75 6,25
...

Are there any suggestions?

Thanks and regards

Jonathan

Help to import Text File Without Delimiters

johnw — Thu, 04 Mar 2010 01:16:11 GMT

Maybe this?

LOAD
ID
,NAME
,subfield(Remainder,' ',1) as V1
,subfield(Remainder,' ',2) as V2
,subfield(Remainder,' ',3) as V3
;
LOAD
ID
,left(Remainder,findoneof(Remainder,'1234567890,')-1) as NAME
, mid(Remainder,findoneof(Remainder,'1234567890,')) as Remainder
;
LOAD
subfield(DataLine,' ',1) as ID
subfield(DataLine,' ',2) as Remainder
;
LOAD DataLine
FROM your source
;

Help to import Text File Without Delimiters

rwunderlich — Thu, 04 Mar 2010 07:18:31 GMT

subfield() can use a negative index (don't recall where I learned this, it's not in the V8 or V9 Help). So how about:

LOAD
subfield(@1,' ',1) as ID,
mid(@1, index(@1,' ',1)+1, (index(@1,' ', -3) - index(@1,' ',1) -1)) as NAME,
subfield(@1,' ',-3) as V1,
subfield(@1,' ',-2) as V2,
subfield(@1,' ',-1) as V3
FROM yourFile.txt
(txt, codepage is 1252, no labels, delimiter is none, msq);
;

Can be simplified if the ID field length is fixed, which it appears to be from your example.

-Rob