Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
keithlawrence
Contributor III
Contributor III

Load multiple Excel files with multiple sheets

Hi Community,

 

We're struggling with something that should, in theory, be quite simple. We have multiple Excel files with multiple sheets. All the sheets have the same data structure but they have different sheet names. The sheet names follow a similar patter of '### to ###' where # is a numerical digit. The digits can be one digit or up to four digits long.

 

We've tried using the ODBC and loop as detailed here:

https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...

But it doesn't work as it seems to be looking for the same sheet names across all the files which we don't have. When we test the above link on the examples given, all works but not for us.

 

We also have some hidden tabs in a few of the files which seem to cause more errors.

 

What are we doing wrong? Or do we need to rename all the sheets 'sheet1', 'sheet2', etc?

Labels (1)
2 Solutions

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

I assume that you are struggling with the missing drop-statement - just add it and try it again:

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
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 * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

drop table tables;

Next

 

- Marcus

 

 

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

There could be two different kinds of unwanted tables - one are internal tables (from an excel point of view) like the print-area and some more other types of tables which aren't a sheet. I have seen somewhere a method how to differ between them but actually I couldn't remember the details and don't find the related posting. I think in your case you don't need this because you wrote that your sheet-names following a certain pattern which could be queried, for example with something like:

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
if isnum(subfield('$(sheetName, ' ', 1)) and subfield('$(sheetName, ' ', 2) = 'to' and isnum(subfield('$(sheetName, ' ', 3)) then
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
end if
NEXT i

If only sheets with data in it have such sheet-names it will also be useful to avoid the load of empty tables.

If not you could implement a "pre-load" which counts the records (in total or for certain fields) or checked the exists of certain fields (you will probably need to use ERRORMODE for it) or something similar. Personally I would rather tend to load these unwanted tables and drop them afterwards. This could happens within a loop too like:

for i = nooftables() to 1 step -1
...

and then you could there query noofrows() or fieldvalue() or something similar to detect the unwanted tables and to remove them.

- Marcus

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

I assume that you are struggling with the missing drop-statement - just add it and try it again:

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
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 * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

drop table tables;

Next

 

- Marcus

 

 

keithlawrence
Contributor III
Contributor III
Author

Thanks Marcus. That's worked.

 

Any idea how we can get the loop to ignore sheets that have blank rows in them? It appears some the files have hidden or system sheets that cause numerous tables to be loaded in to the model. We're fixing it by dropping them after the loop but I'm sure there's an easier way to this.

 

Thanks

marcus_sommer
MVP & Luminary
MVP & Luminary

There could be two different kinds of unwanted tables - one are internal tables (from an excel point of view) like the print-area and some more other types of tables which aren't a sheet. I have seen somewhere a method how to differ between them but actually I couldn't remember the details and don't find the related posting. I think in your case you don't need this because you wrote that your sheet-names following a certain pattern which could be queried, for example with something like:

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
if isnum(subfield('$(sheetName, ' ', 1)) and subfield('$(sheetName, ' ', 2) = 'to' and isnum(subfield('$(sheetName, ' ', 3)) then
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
end if
NEXT i

If only sheets with data in it have such sheet-names it will also be useful to avoid the load of empty tables.

If not you could implement a "pre-load" which counts the records (in total or for certain fields) or checked the exists of certain fields (you will probably need to use ERRORMODE for it) or something similar. Personally I would rather tend to load these unwanted tables and drop them afterwards. This could happens within a loop too like:

for i = nooftables() to 1 step -1
...

and then you could there query noofrows() or fieldvalue() or something similar to detect the unwanted tables and to remove them.

- Marcus

keithlawrence
Contributor III
Contributor III
Author

That's got it! Thanks Marcus.

 

Had to make some minor adjustments to the code as missing some brackets and apostrophes

 

if isnum(subfield('$(sheetName)', ' ', 1)) and subfield('$(sheetName)', ' ', 2) = 'to' and isnum(subfield('$(sheetName)', ' ', 3)) then