Connecting to multiple excel sheet names

[rido1421]

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,

[sunny_talwar]

See if this helps: Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[Kushal_Chawda]

see this

Dynamically Loading Multiple Excel Files

[HirisH_V7 · Accepted solution]

Hi.,

look this

My First Post in Resources:) : Load Multiple Excel Files With Different Sheet names

-Hirish

[jonathandienst]

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)

[Anonymous]

Hi,

Check this link:

Load all Excel files and all sheets in a folder

Hope it helps!!

[rido1421]

Thank you this is very simple , and seems to be effective.

[rido1421]

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.