Transform text file in script

[Anonymous]

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.

Code	ID	Comment
ABCD	01	This is a good room
ABCD	02	N/A
ABCD	26	Too many chairs
DFTG	01	Not enough dogs
DFTG	07	Too 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;
		ID	01;
		ID	02;
		ID	26;
		ID	07;

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

[Aurelien_Martinez · Accepted solution]

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

[Anonymous]

Perfect! Thank you very much.

[Anonymous]

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

grrrrr