Load script for multiple tabs in single excel document

[mattphillip]

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

[jonathandienst]

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

[avinashelite]

check this document for the solution

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

[mattphillip]

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]

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!

[avinashelite]

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]

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

[mattphillip]

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]

Hi Steffan.

Office is installed on my machine.

M

[avinashelite]

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