Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rido1421
Contributor II

Connecting to multiple excel sheet names

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,

1 Solution

Accepted Solutions
hirishv7
Honored Contributor

Re: Connecting to multiple excel sheet names

7 Replies

Re: Connecting to multiple excel sheet names

Re: Connecting to multiple excel sheet names

hirishv7
Honored Contributor

Re: Connecting to multiple excel sheet names

MVP
MVP

Re: Connecting to multiple excel sheet names

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qlikviewnovice
Valued Contributor II

Re: Connecting to multiple excel sheet names

Hi,

Check this link:

Load all Excel files and all sheets in a folder

Hope it helps!!

rido1421
Contributor II

Re: Connecting to multiple excel sheet names

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

rido1421
Contributor II

Re: Connecting to multiple excel sheet names

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.

Community Browser