QlikSense LIB Connect error with SQLTables Function for Excel sheets

[alextomlins]

Hi All,

I recently asked a question about concatenating multiple sheets in the same Excel spreadsheet. There is no sequential/logical filename and i have been provided with a few ways to do it using SQLTables to get a list of sheet names and then a for next loop to put it together.

BUT, my problem is not the solution to my excel issues,  it is the ODBC Connection. I'm getting the error;

The following error occurred:

CONNECTs other than LIB CONNECT are not available in this script mode.

We are using QlikSense Enterprise and the speadsheet is on our enterprise server. I don't know much about connections and don't know much about SQLTables as a function.

The question is, what do i need to do to resolve this error and create an ODBC connection to the excel sheet. Maybe i'm not making sense as this is new to me but any help would be greatful. Thanks Qliktopians

[Anil_Babu_Samineni]

FYI, You must create one connection using folder. Let's assume your folder structure like

\\ServerName\Folder\Folder1\Sample.xls

You need to create connection first till here(\\ServerName\Folder\Folder1\) using the name like (Connection Name). Then, Again you must try pull data using the same connection

And moreover, You said that you need to pull data from SQL as well. For that you need to connect using SQL formalities like How to create SQL Connection Creation for Qliksense‌

[jonathandienst]

Create a named ODBC connection in ODBCAD32 (you may need the 32 bit version in the SysWow64 folder, not the 64 bit version in System32 -- these aren't typos).

The add the connection in Sense in the usual way. Finally use SQLTables to get the table details. Something like:

// Connection to ResourcePlan.xls

LIB CONNECT TO 'TestExcel32';

TABLES:

SQLTables;

DATA:

LOAD 0 as Dummy AutoGenerate 0;

For i = 0 To NoOfRows('TABLES')

    Let vTable = Peek('TABLE_NAME', i, 'TABLES');

    Concatenate(DATA)

    LOAD *

    FROM [lib://KPIFolder/ResourcePlan.xls]

    (biff, embedded labels, table is '$(vTable)');

Next

[alextomlins]

Thank you for this. I already pull from a SQL database, and i've pulled from excel files. I have just never pulled from an excel file from multiple sheets.

Some of the solutions people gave me to pull all the sheets in the excel file was to use a script that uses the function SQLTables and an ODBC Connection to the excel file. I think SQLTables is a function that lists the sheetnames via the ODBC connection - so i need to start by fixing/creating this connection before using the excel sheets script from other threads

Thanks,

I will try and create the connection, wish me luck

Alex

[alextomlins]

Hi

We are running Windows 2016.  This server does not have a ODBC driver for xlsx files. Searched Microsoft and can not seem to find the correct driver.  Can you advise how we get this driver, or have I gone down the wrong track?

Thanks