Load Multi Columns from excel file

[gidon500]

hi

I have a request to load multi columns from excel file

I do not know how many rows and columns I have

it is very simple table  .

I get  multi columns and need to create 2 columns table

enclosed please find excel file  with one sheet

on the left  is the input format

and in the right is the output  format

thanks

gidon

[Kushal_Chawda]

Data:

CrossTable(Col,Balcode)

LOAD Description,

    BalCode,

    F3,

    F4,

    F5

FROM

[loadmulticolmns.xlsx]

(ooxml, embedded labels, header is 1 lines, table is DATA);

DROP Field Col;

[gidon500]

Hi Kush

It works great  ,

but my problem is that I do not know how many columns I am going to have

it can be 3 or 7

with new rows that will be added by the CFO

can we fix it

Gidon

[Kushal_Chawda · Accepted solution]

try below this will load all the columns dynamically using * prefix

Data:

CrossTable(Col,Balcode)

LOAD Description,

    *

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

NoConcatenate

LOAD *

Resident Data

where not Exists(Description,Balcode);

DROP Table Data;

DROP Field Col;

[gidon500]

THANKS

HAPPY NEW YEAR

GIDON

[Kushal_Chawda]

Welcome & HAPPY NEW YEAR TO YOU AS WELL