ODBC connection to Excel file

[datanibbler]

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

[ali_hijazi]

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

[datanibbler]

Hi Ali,

did I mention that my Excel_file is encrypted?

It's not that easy ...

[ThornOfCrowns]

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?

[#5]

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]

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]

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]

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

[JonnyPoole]

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]

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