How to dynamically load Excel worksheets - Qlik Sense

[#1]

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?

[ahaahaaha]

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

[RSvebeck]

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;

[senpradip007]

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

next

[#5]

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.

[#6]

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.

[RSvebeck]

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;