Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
undergrinder
Specialist II
Specialist II

Load XLSX table based on it's position

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.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
Mark_Little
Luminary
Luminary

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
undergrinder
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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.