Load multiple Excel files with multiple sheets

[keithlawrence]

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?

[marcus_sommer · Accepted solution]

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]

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 · Accepted solution]

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]

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