Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
alextomlins
Contributor III
Contributor III

QlikSense LIB Connect error with SQLTables Function for Excel sheets

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

4 Replies
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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner
Partner

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
alextomlins
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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