How to handle many sheetnames in Qlikview?

[#1]

Hi all,

I'm trying to load an Excel file with 430 sheets but I want to do this in one tab in QV.

I have something like this:

for each vSheet in 'Company1', 'Company2', 'Company3',

LOAD

'$(vSheet)' as Company

FROM ...

next;

But because there are too many Excel sheets, it does not fit on one tab in QV. Because the script is quite big, I don't want to use two identical tabs only to finish the for-loop. Just finish the for-loop on the next line is not possible unfortunately..

Does anyone know how to fix this problem?

Thanks in advance

[Gysbert_Wassenaar]

Maybe like this:

LOAD * FROM [ExcelFile.xlsx] (ooxml, embedded labels);

[Anonymous]

Hi

Below is the code which will loop through all the sheets in all the files in the folder

for each file in FileList('C:\Harsha\Test\*.xlsx');

  ODBC CONNECT TO [Excel Files;DBQ=$(file)];

  tables:

  SQLtables;

  DISCONNECT;

  FOR i = 0 to NoOfRows('tables')-1

  LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

  Table:

  Load *

  From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

  NEXT i

  Drop table tables;

  Next file

Regards

Harsha

[#4]

I think this could work, but I want to get the sheetnames into a field, like:

'$(vSheet)' as Company

[#5]

Thank you, I think this is exactly what I need.

Maybe a stupid question, but do you have any idea why I get a syntax error on the following line:

tables:

I do the same as you did, but I get this error..

[Anonymous · Accepted solution]

Hi

if you had checked force32 bit then you need to use the ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

if not then ODBC CONNECT TO [Excel Files;DBQ=$(file)];

You can try the below code

for each file in FileList('C:\Harsha\Test\*.xlsx');

  ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

  tables:

  SQLtables;

  DISCONNECT;

  FOR i = 0 to NoOfRows('tables')-1

  LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

  Table:

  Load *,'$(sheetName)' as SheetName

  From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

  NEXT i

  Drop table tables;

  //set tables=Null;

  Next file

if still this gives an error then try connect to the excel file manually by using ODBC Connection

Regards

Harsha

[#7]

I haven't checked force32 bit, but when I use ODBC CONNECT32 it works fine.. I seems weird that it does work when it is not checked. But it works, so many thanks!

[Anonymous]

You are welcome Mark happy to help you