Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

datanibbler
Esteemed Contributor

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

Tags (1)
21 Replies
ali_hijazi
Honored Contributor

Re: ODBC connection to Excel file

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
Esteemed Contributor

Re: ODBC connection to Excel file

Hi Ali,

did I mention that my Excel_file is encrypted?

It's not that easy ...

thornofcrowns
Valued Contributor II

Re: ODBC connection to Excel file

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

Re: ODBC connection to Excel file

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.

Re: ODBC connection to Excel file

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
Esteemed Contributor

Re: ODBC connection to Excel file


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).

Employee
Employee

Re: ODBC connection to Excel file

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

Employee
Employee

Re: ODBC connection to Excel file

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
Esteemed Contributor

Re: ODBC connection to Excel file

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 ...

Community Browser