Loading Unstructured Data from Excel

[Vinothishere]

Hi,

I have the sample data in the following format:

Dim1	Dim2	Qty
	ABC
	Stage1
100	A	3
101	B	4
	Stage2
101	A	8
102	D	9

I need to load this sample data in the following way:

Dim1	Dim2	Qty	Title1	Title2
100	A	3	ABC	Stage1
101	B	4	ABC	Stage1
101	A	8	ABC	Stage2
102	D	9	ABC	Stage2

Thanks in advance.          

[Ralf-Narfeldt]

This preceding load should do it:

Load * where Len(Dim1)>0;

LOAD Dim1,

     Dim2,

     Qty

FROM

your_excel.xlsx

(ooxml, embedded labels, table is your_sheet_name);

[Vinothishere]

Its working,but how to populate the value for Title2 column?

[jonathandienst]

I would not call this unstructured, but multiline headers from Excel sheets can be awkward to use. Have a look at my post here Cross table for an example of how to do this.

[Ralf-Narfeldt]

Sorry, missed that you wanted to populate Title1 and Title2 as well.

Will Title1 repeat in the data?

[Ralf-Narfeldt]

So, this should do it with Title 1 hardcoded. If you have several similar files or Title1 changing, there would be some more code needed.

Source:

<your source table load from Excel file here>

Temp:

Load *, IF(ISNULL(Temp) OR TRIM(Temp)='', Peek(Title2), Temp) as Title2;

Load *, If(IsNull(Dim1),Dim2,Null()) As Temp resident Source;

Result:

Load *, 'ABC' As Title1 resident Temp where Len(Dim1)>0;

Drop tables Source, Temp;

[Vinothishere]

Can you please explain the logic behind how the two load statements in Temp table works?

[Ralf-Narfeldt · Accepted solution]

Temp:

//If Temp is Null or empty, that is, it is a value row

//get the previous value of Title2, which should be a Stage,

//otherwise (if it's a Stage row) put Temp there.

Load *, IF(ISNULL(Temp) OR TRIM(Temp)='', Peek(Title2), Temp) as Title2;

//Load all values from source plus a Temp field.

//If Dim1 is empty (Null) put Dim2, otherwise put Null

//That way each row with StageX gets this value,

//all value rows will have Null.

Load *, If(IsNull(Dim1),Dim2,Null()) As Temp resident Source;

//Remember that the bottom Load loads before the first one.

//Now we do the final load where we discard the non-value rows with the where //condition, adding the Title1.

Result:

Load *, 'ABC' As Title1 resident Temp where Len(Dim1)>0;

Please mark as Helpful/Answered if you are happy with this!