topic How to dynamically load Excel worksheets - Qlik Sense in QlikView

How to dynamically load Excel worksheets - Qlik Sense

Tue, 09 May 2017 23:48:04 GMT

I have an Excel workbook that has multiple sheets of data. The sheets all have different names (January, February, etc) .The format is exactly the same. I essentially want to concatenate all the sheets together into one table. How can I dynamically do this in Qlik Sense Data Load editor?

Re: How to dynamically load Excel worksheets - Qlik Sense

ahaahaaha — Wed, 10 May 2017 06:55:10 GMT

Hi Jeff,

If I understood you correctly. You can simply load sheets sequentially, observing the condition of the same field name.

Example in attached files.

Regards,

Andrey

Re: How to dynamically load Excel worksheets - Qlik Sense

RSvebeck — Wed, 10 May 2017 07:27:57 GMT

Do a for each loop so you don't need repeated coding:

for each vSheet in 'Sheet1','Sheet2','Sheet3'
LOAD a,
b,
'$(vSheet)' as Sheet
FROM
Book1.xlsx
(ooxml, embedded labels, table is $(vSheet));

Next vSheet;

Re: How to dynamically load Excel worksheets - Qlik Sense

senpradip007 — Wed, 10 May 2017 07:37:59 GMT

Hi,

If I understood you correctly. You can create User DSN with "Microsoft Excel Driver" in the ODBC Data Source Administrator section. After that connect using the same ODBC, you can retrieve the sheet names of the excel dynamically.

ODBC CONNECT32 TO [QV Excel ODBC1;DBQ=D:\QV Test.xlsx];

ExcelInfo:

SQLTABLES;

DISCONNECT;

for i = 0 to NoOfRows('ExcelInfo')-1

let vSheetName = PurgeChar(PurgeChar(peek('TABLE_NAME', $(i), 'ExcelInfo'), chr(39)), '$');

if left(vSheetName, 2) = "20" then

Data:

LOAD

$(i) as IterNum,

ID,

Data

FROM

(ooxml, embedded labels, table is [$(vSheetName)]);

endif

Re: How to dynamically load Excel worksheets - Qlik Sense

Thu, 11 May 2017 02:21:54 GMT

I can load without issue by defining loads and concatenate the sheets together. However, the number and names of the sheets can vary, thus, making this not viable.

Re: How to dynamically load Excel worksheets - Qlik Sense

Thu, 11 May 2017 02:33:57 GMT

I think this may work in QlikView but not Qlik Sense unless I move to Legacy Mode. Is there a way to do with LIB Connect? I can't get past connection info to execute loop.

Re: How to dynamically load Excel worksheets - Qlik Sense

RSvebeck — Thu, 11 May 2017 11:07:44 GMT

No sure which of all replies you refer to, but all examples should work in Sense as long as you change the path to a lib path.

In my example above, this is the Sense version:

for each vSheet in 'Sheet1','Sheet2','Sheet3'

LOAD
      a,
      b,
      '$(vSheet)' as Sheet
FROM [lib://MyDocs/Book1.xlsx]
(ooxml, embedded labels, table is $(vSheet));

Next vSheet;