Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

ODBC connection to Excel file

Hi,

can anyone please tell me what I have to do on my laptop to be able to establish an ODBC connection to an Excel file?

The file is encrypted, I know the password, but I cannot pass it to QlikView in a regular LOAD - but I have heard from @flipside that it should be possible using an ODBC connection.

That is not possible as yet - so i guess there is a driver missing or something. Unfortunately the guy from IT who was supposed to do this seems to know just as much about it as I do 😉 The difference is only that he would have the admin_rights required to do whatever he would have to do if he knew ...

Best regards,

DataNibbler

21 Replies
ali_hijazi
Partner
Partner

Excel Files are table files for qlikview

you do not need an ODBC connection you need to click on the Table Files button in the script editor

and locate your excel file and that's it

test.png

I can walk on water when it freezes
datanibbler
Champion
Champion
Author

Hi Ali,

did I mention that my Excel_file is encrypted?

It's not that easy ...

ThornOfCrowns
Specialist II
Specialist II

As I understood it, the Excel ODBC drivers should be installed as default. What happens when you select MS Query or the Data Connection Wizard as a data connection within Excel?

XL_ODBC.png

Not applicable

Hi,

For any Kind of files(.Xls, Xml, Txt, eyc) we can use table files property from edot script.

we won't  use ODBC/OLEDB for extract the data from file.

we can use ODBC/OLEDB for only Databases.

Peter_Cammaert
Partner
Partner

If you don't have Office installed on your laptop, install the required OLEDB/ODBC drivers from here:

Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Cent...

(Don't worry about the weird name, it's an all-in-one MS Office connectivity package)

When you define the ODBC connection to thius encrypted Excel, one of the configuration tabs allows you to enter a password.

datanibbler
Champion
Champion
Author


Hi,

I'm not sure how I should go about this:

- When I select "ODBC" in the scrript_editor right away, that list is blank, I cannot do anything.

- When I select "OLE DB" and then "OLE DB for ODBC drivers", I can click a bit further, but when I choose to build a connection_string and then select the tab "machine data source" and "Excel file", I get an error message.

I guess I cannot do it because I don't have admin_rights on this laptop.

Anyway - could anyone describe what would have to be done once I have someone from IT here with me to do it?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Needless to say, I need a way that someone from IT can set this up (with admin_privileges) and I can then use it (without admin_privileges).

JonnyPoole
Employee
Employee

The way i usually do this is to 'define name' for the cells in the excel file that you want to load . See below.

Untitled.png

Capture.PNG.pngCapture1.PNG.png

JonnyPoole
Employee
Employee

I was also able to load fields by referencing a Sheet with following syntax:

ODBC CONNECT TO [Test;DBQ=C:\Temp\sample.xlsx];

LOAD

  a,

  b,

  c;

SQL SELECT *

FROM `C:\Temp\sample.xlsx`.[Sheet1$];

------------------

and if its helpful you can use this syntax to load in your sheet names (and other metadata) so that it can be a more automated / dynamic solution

ODBC CONNECT TO [Test; DBQ=C:\Temp\sample.xlsx];

WorksheetNames:

SQLtables;

DISCONNECT;

datanibbler
Champion
Champion
Author

Hi Jonathan,

that looks good - were you able to do this without setting up the data source or anything beforehand?

I'll try.

Then, however, I will still need to find a way to decrypt the file in the process ...