Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
mattphillip
Contributor

Load script for multiple tabs in single excel document

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

1 Solution

Accepted Solutions

Re: Load script for multiple tabs in single excel document

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.

View solution in original post

28 Replies
MVP
MVP

Re: Load script for multiple tabs in single excel document

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Load script for multiple tabs in single excel document

mattphillip
Contributor

Re: Load script for multiple tabs in single excel document

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.

mattphillip
Contributor

Re: Load script for multiple tabs in single excel document

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!

Error.jpg

Re: Load script for multiple tabs in single excel document

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

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


or

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

stabben23
Honored Contributor

Re: Load script for multiple tabs in single excel document

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

mattphillip
Contributor

Re: Load script for multiple tabs in single excel document

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?

mattphillip
Contributor

Re: Load script for multiple tabs in single excel document

Hi Steffan.

Office is installed on my machine.

M

Re: Load script for multiple tabs in single excel document

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