topic Re: How do I load an Excel 2010 named table ? in QlikView

How do I load an Excel 2010 named table ?

Thu, 17 Jul 2014 15:58:47 GMT

Hi All,

I want to load an Excel 2010 table (rather than a sheet) into my QV application. What is the syntax for this please ?

Thanks

Re: How do I load an Excel 2010 named table ?

cspencer3 — Thu, 17 Jul 2014 16:08:57 GMT

I believe you can load data from a table just like a normal sheet. You would want to go into your script editor and use the Table Files Wizard. I just tried it with a quickly inserted table using dummy data and the wizard saw it right away. Syntax appears to be identical, so QlikView still finds it by column/row info.

Re: How do I load an Excel 2010 named table ?

Thu, 17 Jul 2014 16:46:56 GMT

Hi Charles,

Using the Tables File Wizard doesn't allow you to select the table name. It only lists the sheet names. It appears to ignore the table name completely.

To prove the point I created two separate Excel tables on the same sheet and it just took in all the data as a single range. And if you refer to an Excel table by name in the LOAD statement, it doesn't recognise it. I was hoping that maybe the syntax was different for a table rather than a sheet.

Thanks for your response

Re: How do I load an Excel 2010 named table ?

cspencer3 — Thu, 17 Jul 2014 19:56:34 GMT

So you need to be able to address specific tables no matter where they are in the document? I assume that is so that if they move you can still address them?

Re: How do I load an Excel 2010 named table ?

Thu, 17 Jul 2014 20:29:49 GMT

You should be able to. For example, the following load statement will load data from a table called "SalesTable" in an Excel spreadsheet:

LOAD *

FROM

Sample.xlsx

(ooxml, embedded labels, table is SalesTable);

Also, if you use the wizard to load the table, you can use the "Tables" dropdown which will show sheet names and table names:

Re: How do I load an Excel 2010 named table ?

Wed, 23 Jul 2014 11:26:56 GMT

Hi Lior,

The 'Tables' dropdown only shows sheet names, not table names. Even though I can list my table names in Excel Name Manager, they do not show in QlikView. I'm using Excel 2010 and QlikView 11.00.

Any ideas

Re: Re: How do I load an Excel 2010 named table ?

Wed, 23 Jul 2014 13:15:22 GMT

I have attached a sample QVW & Excel spreadsheet, I am curious if this would work for you. I am using QlikView version 11.20 SR1.

Re: How do I load an Excel 2010 named table ?

Wed, 08 Oct 2014 15:01:58 GMT

Thanks Lior,

Sorry for the delay !

Yes, QV recognises the column headers of the table. Interesting result.