Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to handle many sheetnames in Qlikview?

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

7 Replies
Gysbert_Wassenaar

Maybe like this:

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

Not applicable
Author

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

'$(vSheet)' as Company

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

You are welcome Mark happy to help you