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: 
Not applicable

Import/Transform Text File

Hi Everyone,

I'm attempting upload a text file and I can't figure out way to transform the data so it comes in correctly. I've attached a sample. Anybody have any ideas form me?

Thanks,

Alex

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Alex,

I think you can use something along these lines (only some ideas, I made a lot of implicite assumptions):

TMP:

LOAD trim(@1) as tmpString, if(not wildmatch(@1,'*$*') and not previous(wildmatch(@1,'*$*')), previous(@1), peek(Header1)) as Header1,

if(not wildmatch(@1,'*$*'), @1, peek(Header2)) as Header2, if(not wildmatch(@1,'*$*'),1) as isHeader

FROM

C:\Users\Stefan\Downloads\SampleForQlikview.txt

(txt, codepage is 1252, no labels, delimiter is '\t', header is 17 lines);

RESULT:

LOAD LineNo, Header1, Header2, Product, [Product Description], left(tmpString3,index(tmpString3,'  ')) as [Service Category], money#(trim(mid(tmpString3,index(tmpString3,'  '))),'$#','.',',') as [Price in USD];

LOAD LineNo, Header1, Header2, Product, left(tmpString2,index(tmpString2,'  ')) as [Product Description], trim(mid(tmpString2,index(tmpString2,'  '))) as tmpString3;

LOAD recno() as LineNo, Header1, Header2, left(tmpString,index(tmpString,'  ')) as Product, trim(mid(tmpString,index(tmpString,'  '))) as tmpString2

resident TMP where not isHeader=1;

drop table TMP;

TMP table just handles the two headers I identified (If you have more then two levels, well, this will already break it). I skipped the first lines, if you need to load in effective date, it should not be too difficult to add this as an additional date field.

RESULT table loads are handling the separation of the different fields, mostly string operations.

See also attached,

Stefan

View solution in original post

4 Replies
swuehl
MVP
MVP

Alex,

I think you can use something along these lines (only some ideas, I made a lot of implicite assumptions):

TMP:

LOAD trim(@1) as tmpString, if(not wildmatch(@1,'*$*') and not previous(wildmatch(@1,'*$*')), previous(@1), peek(Header1)) as Header1,

if(not wildmatch(@1,'*$*'), @1, peek(Header2)) as Header2, if(not wildmatch(@1,'*$*'),1) as isHeader

FROM

C:\Users\Stefan\Downloads\SampleForQlikview.txt

(txt, codepage is 1252, no labels, delimiter is '\t', header is 17 lines);

RESULT:

LOAD LineNo, Header1, Header2, Product, [Product Description], left(tmpString3,index(tmpString3,'  ')) as [Service Category], money#(trim(mid(tmpString3,index(tmpString3,'  '))),'$#','.',',') as [Price in USD];

LOAD LineNo, Header1, Header2, Product, left(tmpString2,index(tmpString2,'  ')) as [Product Description], trim(mid(tmpString2,index(tmpString2,'  '))) as tmpString3;

LOAD recno() as LineNo, Header1, Header2, left(tmpString,index(tmpString,'  ')) as Product, trim(mid(tmpString,index(tmpString,'  '))) as tmpString2

resident TMP where not isHeader=1;

drop table TMP;

TMP table just handles the two headers I identified (If you have more then two levels, well, this will already break it). I skipped the first lines, if you need to load in effective date, it should not be too difficult to add this as an additional date field.

RESULT table loads are handling the separation of the different fields, mostly string operations.

See also attached,

Stefan

Not applicable
Author

Hi Swuehl,

  This works great. I finally looked at and seems to do everything that I need. Did you use the transformation wizard to complete this? I just didn't know where to start....

Thanks again.

swuehl
MVP
MVP

Sorry, no wizard involved.

If you want to parse in a file like that, you need to define the structure of the data, so I started with Header1, Header2 and detail lines. How to distinguish between them etc.

Then try to structure the detail lines (your actual fields) and try to parse them (I used a trim & crop technique).

There are several loads involved, if you stop after each load and look at the created table, it should be quite clear how it works.

Regards,

Stefan

Not applicable
Author

Hi Stefan

Congratulation !  I'm just be back on the QV community aftert 3 month  and find out you have overtaken John W in the Top Ranking Member.

Jean-Jacques