Help loading in excel file with sections

[hopkinsc]

Hi all,

I need to load in the attached excel file but i don't know how to.

within the sheet the data is split into sections.

i.e.

Item

Manufacturing

Freight

Duty

Labour

Distribution

Manufacturing

Freight

Duty

Labour

Destuffing

Manufacturing

Freight

Duty

Labour

etc

what i want is to use the bold text as the section name and the values beneath as another field.

so id want it loaded something like..

Section, Value

Item, Manufacturing

Item, Freight

Item, Duty

Item, Labour

Distribution, Manufacturing

Distribution, Freight

Distribution, Duty

Distribution, Labour

Destuffing, Manufacturing

Destuffing, Freight

Destuffing, Duty

Destuffing, Labour

Can anyone suggest a way of doing this please without changing the source file?

[chrismarlow]

You can change your Excel load to ignore the headings then use a match & peek to populate your section & value fields from column A, you would then need to tidy up the loaded data (this will be very sensitive to breaking if the format changes at all & you also need to think about how you get names for columns B onwards ...

LOAD

If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,A,peek(Section,-1)) AS Section,

If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,Null(),A) AS Value;

LOAD

A

FROM [Sample.xlsx] (ooxml, no labels, table is [P&L Feeds]);

Regards,

Chris.

[gmoraleswit · Accepted solution]

Hello,

Not sure if this is what you need but I hope it helps you:

I first identified the Sections and the Values:

Temp:

LOAD if(RowNo()=1 or len(B)=0,A) as Section,

if(RowNo()>1 and len(B)>0,A) as Value,

// A,

     B,

     C,

     D,

     ...

     W,

     X,

     Y,

     Z,

     AA

FROM

Sample.xlsx

(ooxml, no labels);

Then I use the Fill option in the Transformation Step when you load the qvd:

See QVW Attached.

Regards!

[hopkinsc]

Fantastic, thanks very much Gabriela

[hopkinsc]

Thanks Chris

[hopkinsc]

Hi Gabriela..

Just a quick question..

What you did worked but i need the column headings from column 3 onwards. as highlighted below..

Do you know how i can achieve this ?

[gmoraleswit]

Hi Chris,

The only way I could do this is by storing the data as a txt and then using the table load wizard to indicate the number header lines and the embedded labels

Then in the transformation step I deleted the lines in blank

See attach qvw.

Regards!

Gabriela

[chrismarlow]

I prefer Gabriela's initial approach to mine, I had not seen the transform step in any detail, but think for the second piece maybe the peek can work better, so extending my code (or you could extend from the transformed steps);

temp:
LOAD
If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,A,peek(Section,-1)) AS Section,
If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,Null(),A) AS Value,
B;
LOAD
A ,
B
FROM [Sample.xlsx] (ooxml, no labels, table is [P&L Feeds]);

Let TitleB=peek('B',0);

final:
NoConcatenate
Load
Section,
Value,
B AS $(TitleB)
Resident temp;

Drop table temp;

Cheers,

Chris.