topic Re: Connecting to multiple excel sheet names in QlikView

Connecting to multiple excel sheet names

rido1421 — Thu, 14 Apr 2016 11:28:29 GMT

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,

Re: Connecting to multiple excel sheet names

sunny_talwar — Thu, 14 Apr 2016 11:30:38 GMT

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

Re: Connecting to multiple excel sheet names

Kushal_Chawda — Thu, 14 Apr 2016 11:31:45 GMT

see this

Dynamically Loading Multiple Excel Files

Re: Connecting to multiple excel sheet names

HirisH_V7 — Thu, 14 Apr 2016 12:20:02 GMT

Hi.,

look this

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

-Hirish

Re: Connecting to multiple excel sheet names

jonathandienst — Thu, 14 Apr 2016 12:36:48 GMT

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)

Re: Connecting to multiple excel sheet names

Anonymous — Thu, 14 Apr 2016 14:14:59 GMT

Hi,

Check this link:

Load all Excel files and all sheets in a folder

Hope it helps!!

Re: Connecting to multiple excel sheet names

rido1421 — Sat, 16 Apr 2016 08:08:33 GMT

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

Re: Connecting to multiple excel sheet names

rido1421 — Sat, 16 Apr 2016 08:10:56 GMT

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.