Load an Excel Table (ListObject)

[Anonymous]

Dear community,

Loading Excel is so much fun. Though since 2003 we began to see a new type of object called ListObject. This object is quite similar to a named range but is in fact publicly named a Table. Since then many users are using this object. I can't find another parameter in the statement for file definition.

While loading Named range is totally fine, loading a Table (or ListObject in VBA) seems impossible. Have you encounter this type of issue. How did you deal with that?

Much thanks in advance.

[marcus_sommer]

If you could elaborate it in more detail and provide a small example it will be helpful.

- Marcus

[Anonymous]

Hi Marcus,

Here is an Excel File.

You will find:

• a Named Table (Table1)
• a Named Range (Range1)

The Named Table (Table1) is not available as an option to be loaded in QlikView whereas the Named Range (Range1) is.

Thanks,

[marcus_sommer]

I think that these Table (ListObject) is no own data-area it's a feature (on a higher level) which will be applied to a data-area and therefore you couldn't access these virtual object directly.

But if you give these Table an additional name you could access it and a normal odbc-load on the sheet or a normale table-load will be work also:

ODBC CONNECT32 TO [DriverForExcel;DBQ=I:\MyPath\TestLoadListObjects.xlsx];

SQL SELECT Country,

    Sales

FROM `I:\MyPath\TestLoadListObjects.xlsx`.`Sheet1$`;

SQL SELECT *

FROM `I:\MyPath\TestLoadListObjects2.xlsx`.NameForTable; // with a name for this ListObject

Directory;

LOAD A,

     B,

     C,

     D,

     E,

     F

FROM

(ooxml, no labels, table is Sheet1);

- Marcus

[Anonymous]

Hi Markus,

Thank you for your reply.

Unfortunately, here is a very quick example and the users will have to manage multiple Named Range to apply on top of the Named Table which is not helpful as the table can extend automatically without the range be extended.

I guess you confirmed I have to find another option to deal with that.

Thank you!

[marcus_sommer]

The excel name-ranges could be dynamically with an expression like this:

offset(Sheet1!$A$1;;;CountA(Sheet1!$A:$A);CountA(Sheet1!$1:$1))

See also: Offset Function in Excel - Easy Excel Tutorial

- Marcus

[Anonymous]

Hi Markus,

I didn't know this function thanks. After exploring it, I can say it won't work. Thank you thou on that matter.

Best,

[marcus_sommer]

For me these methods worked very well since years - I use it for all my dynamic data-areas, mostly for pivot-tables. Therefore have a further look to the idea - maybe I have made an error by choosing/translating the right function/syntax - in german in function will be called as Bereich.Verschieben().

- Marcus