Issue pulling data from a multi-tab excel document

[#1]

Hi all,

I am new to Qlikview and I am working on a dashboard that pulls in 15+ tabs from an excel document. Right now the script below works to pull in all of the data from the spreadsheet but when I run the script I am getting an scrpit error. I believe the issue is that there are two named ranges (List_Activities and List_Systems) in the excel document that I am using as dropdown selections. It looks like once the load script see those named ranges it is trying to pull them in as distinct sheets. Any idea how I can get Qlikveiw to filter them out so I can leave them in the Excel doc?

Thanks,
Chris

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

For each file in FileList('C:\Users\chris.d.santoleri\Desktop\Qliview\AMI_IT_Timesheet.xlsx')

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

tables:

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

/*

One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

We will loop through this set of sheet names.

*/

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

       /*

       Assign the TABLE_NAME to the variable "sheetName".

       TABLE_NAMEs that contain spaces will be enclosed in single quotes.

       The purgeChar function will remove any quotes AND dollar signs.

       */

       LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

       // Optional filtering logic to select certain sheets

                        // IF not(wildmatch('$(sheetName)', 'Master File*')) THEN  // All sheetNames that are not Master File

             SupplementalTimsheet:   

             // Now that we have a sheet name, a standard biff (Excel) load can be used.

             LOAD Day as Date,

                       Day(Day) As Day,

                       Month(Day) As Month,

                       Year(Day) As Year,

                       WeekStart(Day) As Week,                         

                                   Name,

                                   Activity,

                                   System,

                                   [QC or Ticket Number],

                                   Hours,

                                   Comments,

                    '$(sheetName)' as Sheet  // Optionally, the sheetName value may be loaded as a field

             FROM $(file) (ooxml, embedded labels, table is [$(sheetName)])

             Where '$(sheetName)' <> '[List_Systems]';

NEXT

DROP TABLE tables;   // The table list is no longer needed

[#2]

anyone?

[flipside · Accepted solution]

Hi,

In BIFF format Excel files (.xls), the named ranges show as TABLE_TYPE = [TABLE], whereas Sheets show as [SYSTEM TABLE], perhaps .xlsx files are the same. If so, you cannot filter SQLtables directly, so you have to use a temp table ...

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

TmpTables:

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

tables:

NOCONCATENATE LOAD * resident TmpTables where TABLE_TYPE = 'SYSTEM TABLE';

DROP TABLE TmpTables;

flipside

[#4]

I still cannot seem to get that to work. Should this temp table be before the table that is loading all of the sheets?

Chris

[Gysbert_Wassenaar]

Yes, replace

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

tables:

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

with what flipside posted above.

The sheets in .xlsx files are still of type SYSTEM TABLE while Named Ranges have type TABLE so his solution will work.

See attached files. Maybe you can figure out that way what's going on. You'll have to adjust the path to the xlsx file in the qvw, but then it should work as it does here.

[#6]

Thanks for your help, That is what I was trying before and I continue to get this error.

-------------------------------------------------------------------------------------------

Connecting to Excel Files;DBQ=C:\Users\chris.d.santoleri\Desktop\Qliview\AMI IT Timsheet\AMI_IT_Timesheet.xlsx

Connected

TmpTables << Tables-ODBC;dsn=excel files;dbq=c:\users\chris.d.santoleri\deskt 36 lines fetched

tables << TmpTables 0 lines fetched

[#7]

Thank you. I will take a look at that and see if I can figure it out. What I did notice is that in your excel file your data is different in all three sheets so you would not want to concatenate but my data contains the same columns for all sheets and I do want them to concatenate. So I am guessing this is the line that is throwing me off right now.

NOCONCATENATE LOAD * resident TmpTables where TABLE_TYPE = 'SYSTEM TABLE';

[flipside]

Hi Chris,

The NOCONCATENATE statement is definitely required so don't think this is the issue for you. To explain, the temp table (TmpTables) is using the metadata returned by SQLtables to populate it.  Unfortunately we cannot filter this at this point so we are trying to move the data to a new table so we can use a where clause to remove the data we don't require.  If we just use LOAD * resident, even when specifying a new table name beforehand, Qlikview will see the structure matches the existing table (TmpTables) and will combine the table rows instead.  The NOCONCATENATE command forces a separate table instead.

From your log file, it looks like it isn't finding any rows with TABLE_TYPE = 'SYSTEM TABLE', so the first thing to check is what TABLE_TYPEs are you getting from SQLtables?

flipside

[#9]

Thanks flipside,

I finally figured out how that worked yesterday. I ended up getting the data to load by using TABLE_TYPE = 'TABLE'; instead of SYSTEM TABLE. That seemed to work fine from what I can tell.

Now my issue is that when I try to load in specific fields it errors out again but using LOAD* works.

Chris