Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Danijel
Partner - Creator III
Partner - Creator III

Load Excel Files with different sheets?

Hello all,

I would like to load many Excel files, but they have different tab names. There has been a change in the source system, hence the difference in names.

The problem is that I cannot identify which sheet name to use by looking at the excel file.

My current code is like this:

for each vfile in dirlist(...)
for each vsheet in 'sheet1', 'sheet_1'

Excel:
load
...
...
from...
...

next vsheet;
next vfile;

Works, but of course I get errors that data is not found if the wrong register name is used.

As an interim solution, I temporarily switch off the error messages via errormode=0 or 1.

I have searched the community, but only found helpful information as soon as you can use odbc.In my case this is unfortunately not possible because we use LibreOffice, so the excel odbc driver is not installed.


Does anyone else have an idea, without errormode etc.?

Thanks and greetings

Labels (2)
6 Replies
Lisa_P
Employee
Employee

Maybe use a wildcard rather than loop?

Danijel
Partner - Creator III
Partner - Creator III
Author

Possible at excel file level, not possible at tab sheet level.

martinpohl
Partner - Master
Partner - Master

Hi Danijel,

with ODBC connect to your excel-file you can read sheets (I think it was sqltables as it is at sql tables) to identify your tables.

Excel ODBC driver you can download at microsoft homepage.

This also works for example if in excel file password protection is used.

Regards

marcus_sommer

I'm not absolutely sure but I think the odbc-driver is independent to an Excel installation - therefore this should be the easiest way.

Loading with the error-mode is not really sensible because you just prevents script-errors but you couldn't ensure that all data will be loaded.

Alternatives to the odbc-driver could be to list all sheet-names within another sheet maybe created with an OnClose macro or with an Excel4-function (it's very old and not documented since ages but they may further exists). Also thinkable is to use an outside-macro which runs through all files and lists their sheets maybe just with vbs - but I don't know if it's possible on your target-platform without Excel (but the files might be moved back and forth).

Another way would be to unzip the xlsx (I assume it's not an old xls) with any batch and then to pick the needed sheet-informations from the xml-files there. I think if you searched for it you will find various hints how to get it to work.

- Marcus

Danijel
Partner - Creator III
Partner - Creator III
Author

Hi,

yes, reinstalling the driver would be an idea, but due to restrictions this could be more difficult.

Macro is also a topic in itself. Then there seems to be no other option at the moment, which is a pity.

marcus_sommer

Like mentioned you may pick the information from the xml-files. Of course it caused some overhead to copy and rename the file, unzip it, loading the workbook.xml to get the sheet-names and then looping through it - but it's possible. I think a good starting point is:

XML & ZIP: Explore Your Excel Workbooks File Structure | Professor Excel | Professor Excel (professo...

- Marcus