Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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;
THANKS
HAPPY NEW YEAR
GIDON
Welcome & HAPPY NEW YEAR TO YOU AS WELL