topic Re: Load XLSX table based on it's position in QlikView

Load XLSX table based on it's position

undergrinder — Wed, 25 Nov 2015 09:04:31 GMT

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!

Re: Load XLSX table based on it's position

Mark_Little — Wed, 25 Nov 2015 09:16:40 GMT

Hi,

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

Mark

Re: Load XLSX table based on it's position

jonathandienst — Wed, 25 Nov 2015 09:18:08 GMT

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)]);

Re: Load XLSX table based on it's position

undergrinder — Wed, 25 Nov 2015 09:40:02 GMT

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.

Re: Load XLSX table based on it's position

undergrinder — Wed, 25 Nov 2015 09:43:12 GMT

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.