topic Re: Load script for multiple tabs in single excel document in QlikView

Load script for multiple tabs in single excel document

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

Hope someone can help with this.

I have very large data file to process shortly with coded field values. Accompanying this file is the field value definitions document. This document consists of a tab per field in a single excel document. My problem is that there are 200+ fields, hence 200+ tabs. I've seen some examples where there is a load script solution but these seem to be based on the same field names (each table has distinct field names). When I've tried replacing the field names with '*' it doesn't work either.

Can someone help? I really don't want to have to add 200+ pieces of load script...

Appreciated.

Matt

Re: Load script for multiple tabs in single excel document

jonathandienst — Tue, 06 Feb 2018 09:46:53 GMT

What sort of information is on the 200+ tabs? Are they all the same structure? Do you want to concatenate all of these into a single table.

If you open the spreadsheet using an ODBC/OLEDB connection, you can get a list of the tab names, and then loop over the tabs to load the content.\

Use OLEDB to get Excel sheet names

Or, if you are feeling adventurous...

How to extract Sheet Names from an Excel XLSX-file without ODBC

Re: Load script for multiple tabs in single excel document

avinashelite — Tue, 06 Feb 2018 09:50:38 GMT

check this document for the solution

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 09:52:15 GMT

Each tab has a table of varying length, each containing two fields; <fieldname>Data and <fieldname>Label. As the values in the main data table are coded, I need to load in the definition file so Qlikview can link the <fieldname>Data field in the main data table with the <fieldname>Data field in the appropriate definition table, allow me to used the <fieldname>Label field in the dashboard itself.

Unfortunately the main data file is so massive that we can't open it to edit it directly to build in the <fieldname>Label field.

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 10:02:11 GMT

Hi Avinash,

I took a look at the link and tried the script but I'm getting this error message (see below). Any ideas how I can fix this? Thanks!

Re: Load script for multiple tabs in single excel document

avinashelite — Tue, 06 Feb 2018 10:29:54 GMT

I feel like its the driver miss match try to change the connection string

ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];

Re: Load script for multiple tabs in single excel document

stabben23 — Tue, 06 Feb 2018 10:36:09 GMT

You need to have Excel installed on the machine which is running the script.

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 10:39:40 GMT

I tried the CONNECT64 line and it gets as far as Table 112 then I get an error message 'Internal inconsistency, Type F' and Qlikview crashes. Am I just trying to load too many tables? Maybe its worth a shot to just split the files into two groups?

What do you think?

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 10:40:33 GMT

Hi Steffan.

Office is installed on my machine.

Re: Load script for multiple tabs in single excel document

avinashelite — Tue, 06 Feb 2018 10:42:51 GMT

need to see the files to suggest ..as per your words it loads correctly till table 112 then try splitting and see how it goes

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 11:46:42 GMT

Right I've tried splitting them and it got through the first file which had 60 tables. Then it connected to the second table and I got this error.

Sorry for all the hassle this is causing.

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 11:47:09 GMT

And here's the code:

FOR EACH file in FileList('V:\Qlikview\Purchased HESA dataset\New folder\Labels\Beta Files\*.xlsx');

ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];

Temp:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('Temp')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'Temp'), 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;

Re: Load script for multiple tabs in single excel document

tamilarasu — Tue, 06 Feb 2018 12:02:02 GMT

Hi Matt,

Could you attach a sample file. It will be really helpful for us to provide you the solution quickly and also we can avoid back and forth replies.

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 12:06:28 GMT

Attached as requested.

Re: Load script for multiple tabs in single excel document

tamilarasu — Tue, 06 Feb 2018 12:44:41 GMT

Hi Matt,

What will be the output / expected table format. Do you want to concatenate all the tabs one below one?

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 12:48:37 GMT

I'd like to load in all the tables. The data isn't linked so I don't want them combined into a single table. Hope this makes sense.

Re: Load script for multiple tabs in single excel document

tamilarasu — Tue, 06 Feb 2018 14:03:21 GMT

If we load all the tabs as tables then we will end up creating 200+ tables. I think this is not a good idea. I have analysed your excel file and created a singe table but with all the required columns. I think this should link with your data. Note that I have faced some issues while loading your excel as it loads empty columns (AA, AB etc..) as well. So I have modified the script to load only the required columns. Kindly check and let me know your thoughts on this.

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 14:14:03 GMT

That's awesome thank you! This is the kind of area I need to work on in the future. I can build reasonably complex dashboards but I still have a weakness when it comes to optimal table import format. Any suggestions on some good reads or articles which might help my accomplish this?

Thanks!

P.S. Did you amend the excel file itself or does the load script handle all the transformation steps?

Re: Load script for multiple tabs in single excel document

tamilarasu — Tue, 06 Feb 2018 14:28:40 GMT

No Problem Matt .

Matt Phillip wrote:

This is the kind of area I need to work on in the future. I can build reasonably complex dashboards but I still have a weakness when it comes to optimal table import format. Any suggestions on some good reads or articles which might help my accomplish this?

You can learn everything by practicing. I would suggest you to check some answered questions in the community everyday and try to do it yourself. This will give you a good start and some idea. Next step is try to answer some scripting questions in the community like I did . Also there are lot of good people who can post some good stuffs. Please try to check their replies as well. That's more than enough.

Matt Phillip wrote:

P.S. Did you amend the excel file itself or does the load script handle all the transformation steps?

I did all the transformations in the load script itself. Kindly confirm whether you are loading data from single excel file or multiple excel files. Because the code that I have provided will load only data from a single file (I just commented the for loop). If you want to load from multiple files, kindly let me know. I will provide the code for that as well.

Re: Load script for multiple tabs in single excel document

mattphillip — Tue, 06 Feb 2018 14:31:00 GMT

On this occasion its a single file, but I'm anticipating having more than one file going forward. Could you send through example script which could cater for multiple files too?

Thanks for the tips! I'll definitely do that.