Text File data normalisation

[thepope]

Hi,

Is there a way to format data that either comes in a single line or over multiple lines

Field01, Field02, Field03, Field04, Field05, Field06, Field07
X12049,0962019010,Y,PAP,192.123.123.18,255.255.255.0,192.123.123.1
X12049,0962019011,Y,PAP,192.123.123.18,255.255.255.0,192.123.123.1

X1T043,0962019002,Y,\
TXT,192.123.123.200,255.255.255.0,192.123.123.1

X12043,0962019004,Y,\
PDF,192.123.123.26,255.255.255.0,192.123.132.1

X12043,0962019005,Y,\
PDF,192.123.132.26,255.255.255.0,192.123.123.1

It needs to look like

Field01, Field02, Field03, Field04, Field05, Field06,Field07
X12049,0962019010,PAP,192.123.123.18,255.255.255.0,192.123.123.1
X12049,0962019011,PAP,192.123.123.18,255.255.255.0,192.123.123.1

X1T043,0962019002,TXT,192.123.123.200,255.255.255.0,192.123.123.1

X120431,0962019004,PDF,192.123.123.26,255.255.255.0,192.123.132.1

X120432,0962019005,PDF,192.123.132.26,255.255.255.0,192.123.123.1

I have tried using Peek but it still fails

[rubenmarin · Accepted solution]

Hi, for that particular example, if the error is the same as you show, you can use:

// Load data and identify partial rows, also row numebr to keep track of loadiong order
Init:
LOAD Field01, 
     Field02, 
     Field03, 
     Field04, 
     Field05, 
     Field06, 
     Field07,
     RowNo() as RowNumber,
     If(Len(Field07)<5 and Field04='\',1,0) as isFirstPart,
     If(Len(Field07)<5 and Field04<>'\',1,0) as isSecondPart
FROM ...

// Merge rows; loading from the last row, add fields from the second part to the first part. PRevious can access the data ignored bny the 'where' filter
CreateRows:
LOAD Field01, 
     Field02, 
     Field03,
     If(isFirstPart, Previous(Field01), Field04) as Field04,
     If(isFirstPart, Previous(Field02), Field05) as Field05,
     If(isFirstPart, Previous(Field03), Field06) as Field06,
     If(isFirstPart, Previous(Field04), Field07) as Field07,
     RowNumber,
     isFirstPart
Resident Init
where isSecondPart=0
Order by RowNumber desc;

DROP Table Init;

[thepope · Accepted solution]

Thank You,

I neater solution than I came up with 👍