topic Re: Load al the excel sheets in QlikView

Load al the excel sheets

jmvilaplanap — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need to load all the spreadsheets of an excel file (more than 50) and I want to try to do it semi-automatic. I have seen in others questions that the best way is to use a ODBC connexion to the excel file and get all the sheet names, but this doesn´t work for my, I can't configure this ODBC connexion.

My solution (for now) is create an inline table with the names of the sheets and do a loop, but I don`t like it, I think must to have another better solution.

Could anyone help me?

Thanks

Re: Load al the excel sheets

Frank_Hartmann — Sat, 08 Jul 2017 10:43:08 GMT

Maybe like attached

hope this helps

Re: Load al the excel sheets

Frank_Hartmann — Sat, 08 Jul 2017 10:56:35 GMT

this example might also be interesting

hope this helps

Re: Load al the excel sheets

jmvilaplanap — Sat, 08 Jul 2017 11:20:33 GMT

Hi,

The problem here is when the sheets have no consecutive labels. In my case, the sheets have diferent names as:

Sales
Budget
Clients
Providers
...

Thanks

Re: Load al the excel sheets

jmvilaplanap — Sat, 08 Jul 2017 11:23:07 GMT

Hi,

In this example you're using a OLEDB connection, but my question is if is possible to get the sheets name directly from the file or get the data without know the name of the sheets

Regards

Re: Load al the excel sheets

Frank_Hartmann — Sat, 08 Jul 2017 15:09:52 GMT

You could solve it using a macro.

The macro extracts the sheet names and writes it into a variable.

then you can use this variable in your loop!

see attached zip file.

You will just have to adapt the paths inside the vbs file and the QV-Script according to your environment

hope this helps

Re: Load al the excel sheets

BalaBhaskar_Qlik — Sun, 09 Jul 2017 01:48:02 GMT

Try this:

Re: Excel with multiple sheets

Re: Load al the excel sheets

jmvilaplanap — Sun, 09 Jul 2017 11:03:28 GMT

This solution is useful, but I looking for one solution inside qlik.

Thanks

Re: Load al the excel sheets

jmvilaplanap — Sun, 09 Jul 2017 11:04:24 GMT

Hi,

This solution only works if the name of the sheets have a pattern, for example sheet1, sheet2, etc....

Regards

Re: Load al the excel sheets

Frank_Hartmann — Sun, 09 Jul 2017 11:44:21 GMT

Ok, what do you mean with "inside Qlik"?

would it be also acceptable to use a macro within the module of QV instead of an external vbs file?

If yes, then try attached solution.

hope this helps