Load XLSX table based on it's position

[undergrinder]

Hi All!

Is there a way to load excel sheet based on it's position instead of it's name?

In QlikViev it is a valid method: table is @2, but in QlikSense I get a Error: File extdata.cpp error.

According to the document below, it is a valid syntax in Qlik Sense, but only appears at webfiles section.

https://help.qlik.com/sense/1.1/en-us/pdf/Loading%20and%20Modeling%20Data.pdf

Thank You!

G.

[Mark_Little]

Hi,

What is it exactly you are doing. Can you no just use the drag and drop functionality of loading the excel document?

Mark

[jonathandienst · Accepted solution]

The table is @2 syntax works for xls (biff) files, but not for xlsx (ooxml) files.

To achieve this with an xlsx files, you can use the following logic (replace the filename.xlsx with the correct file name):

ODBC CONNECT32 TO [Excel Files;DBQ=filename.xlsx];

SpreadsheetData:

SQLTABLES;

DISCONNECT;

Let zSheet = Peek('TABLE_NAME', 1);  // to get 2nd table

DROP Table SpreadsheetData;

LOAD ...

    ...

FROM filename.xlsx

(ooxml, embedded labels, table is [$(zSheet)]);

[undergrinder]

Hi Mark!

Thank you for your response! The drag and drop functionality isn't fit for my situation, because I have to create a load script with extract with high variability.

G.

[undergrinder]

Hi Jonathan!

Thank You!

I didn't know, that the @N method works only with biff files.

I've not tried the ODBC connection for xlsx, because the xls format is more proper to my case.

However I'll try your odbc sample soon.

G.