Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Transform text file in script

I need to some information from a text file and am getting confused about where to go next.

The file is similar to the attached, although this is obviously sample data and the real file has a lot more fields.

The only information I need is the Code, the ID, and the Comments out of this.

   

CodeIDComment
ABCD01This is a good room
ABCD02N/A
ABCD26Too many chairs
DFTG01Not enough dogs
DFTG07Too dark

I've managed to load the file using:

TEMP2:
LOAD If(@1='Code',@1) as Code,
If(@1='Comments',@1) as Comment,
If(@1='ID',@1) as ID,
@2 as Result
FROM
[SiteTest.txt]
(
txt, codepage is 1252, no labels, delimiter is '=', header is 1 lines)
WHERE @1='Code' or @1 = 'Comments' or @1 = 'ID'

but this clearly gives me the information unattached to itself.

   

Code Comment ID Result
Code ABCD;
Code DFTG;
Comments This is a good room;
Comments N/A;
Comments Too many chairs;
Comments Not enough dogs;
Comments Too dark;
ID01;
ID02;
ID26;
ID07;

   

Any steers in the right direction would be most gratefully received.

1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

test:

LOAD Distinct

  If(IsNull(Code), Peek(Code), Code) as Code,

  Comments,

  If(IsNull(ID), Peek(ID), ID) as ID

;

LOAD

  If(@1='Code', @2) as Code,

  If(@1='Comments', @2) as Comments,

  If(@1='ID', @2) as ID

FROM

C:\Users\AMartinez.STORENEXT\Downloads\SiteTest.txt

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

Where Match(@1, 'Code', 'Comments', 'ID');

NoConcatenate

LOAD

  *

Resident test

Where not IsNull(Comments)

;

DROP table test;

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

3 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

test:

LOAD Distinct

  If(IsNull(Code), Peek(Code), Code) as Code,

  Comments,

  If(IsNull(ID), Peek(ID), ID) as ID

;

LOAD

  If(@1='Code', @2) as Code,

  If(@1='Comments', @2) as Comments,

  If(@1='ID', @2) as ID

FROM

C:\Users\AMartinez.STORENEXT\Downloads\SiteTest.txt

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

Where Match(@1, 'Code', 'Comments', 'ID');

NoConcatenate

LOAD

  *

Resident test

Where not IsNull(Comments)

;

DROP table test;

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!
Anonymous
Not applicable
Author

Perfect! Thank you very much.

Anonymous
Not applicable
Author

Then...the customer tells me that actually, that text file was a database extract...from a database I was already connecting to!

grrrrr