Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I have an issue loading data from excel.
The excel document will be generated monthly , and the file name of the document will be named after the month.
Sales20160131.xlsx
I know I can deal with each file by loading * but how would I deal with the sheet names because that will change on each document as well?
Your assistance is appreciated.
LOAD
Sales_Value,
Product
FROM
$(vPath)\Sales_*.xlsx
(ooxml, embedded labels, table is Sales_20160131);
Regards,
Hi.,
look this
My First Post in Resources:) : Load Multiple Excel Files With Different Sheet names
-Hirish
Hi.,
look this
My First Post in Resources:) : Load Multiple Excel Files With Different Sheet names
-Hirish
Load like this:
For Each vFile in FileList('<path>\Sales*.xlsx')
Let vFilename = SubField(SubField(vFile, '\', -1), '.', 1);
Let vSheet = 'Sales_' & TextBetween(vFilename, 'Sales', '.xlsx')
Data:
LOAD
Sales_Value,
Product,
BaseFileName() as FileSource,
Date(Date#(TextBetween(BaseFileName(), 'Sales', '.xlsx'), 'yyyyMMdd')) as FileDate
FROM
$(vPath)\Sales_*.xlsx
(ooxml, embedded labels, table is $(vSheet));
Next
(Replace <path> in the FileList statement with the correct path to the source files)
Thank you this is very simple , and seems to be effective.
Hi Jonathan
is the correct syntax "FileBaseName() "
For some reason when I do it this way it reloads but doesn't return anything in the table.