topic ODBC connection to Excel file in QlikView

ODBC connection to Excel file

datanibbler — Fri, 21 Nov 2014 12:47:24 GMT

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

Re: ODBC connection to Excel file

ali_hijazi — Fri, 21 Nov 2014 12:50:07 GMT

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

Re: ODBC connection to Excel file

datanibbler — Fri, 21 Nov 2014 12:52:45 GMT

Hi Ali,

did I mention that my Excel_file is encrypted?

It's not that easy ...

Re: ODBC connection to Excel file

ThornOfCrowns — Fri, 21 Nov 2014 12:59:23 GMT

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?

Re: ODBC connection to Excel file

Fri, 21 Nov 2014 13:02:16 GMT

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

Peter_Cammaert — Fri, 21 Nov 2014 13:12:21 GMT

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 Center

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

Re: ODBC connection to Excel file

datanibbler — Fri, 21 Nov 2014 13:19:33 GMT

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

Re: ODBC connection to Excel file

JonnyPoole — Fri, 21 Nov 2014 14:02:24 GMT

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

Re: ODBC connection to Excel file

JonnyPoole — Fri, 21 Nov 2014 14:16:47 GMT

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

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

LOAD

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;

Re: ODBC connection to Excel file

datanibbler — Fri, 21 Nov 2014 14:20:01 GMT

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

Re: ODBC connection to Excel file

JonnyPoole — Fri, 21 Nov 2014 19:17:28 GMT

I BELIEVE you can variablize the "DBQ=$(vPathAndFileName)" part.

So if you have a driver and have at least a 'dummy' odbc connection called (in this case) 'Test' , you can alter the spreadsheet it goes for... just ensure your LOAD reflects the spreadsheet you are after.

Re: ODBC connection to Excel file

datanibbler — Wed, 18 Feb 2015 11:03:25 GMT

Hi James,

sory, it has been a while. I'd like to take up on this again - there is not much pressure on the QlikView_development right now, so I'd like to prepare some things that I'm going to need lateron.

When I select that menu_point in Excel, it looks quite normal - as it should, I'd say. I get the dialogs that should follow and I can pick an Excel file to connect to via ODBC.

Still, it doesn't work in QlikView - I try to go via the wizard. So I choose ODBC from the dropdown, I click the button "Select" and I get the dialog where I can choose a data_source - "Excel Files" is a point to select there - and I can enter a username and password - password is probably the one used to encrypt the file, but what username to enter there?

Whatever combination I try there, I always get an error_message about an "architecture mismatch between the Driver and Application"

=> Does this mean I do not have the right driver for xlsx files and I should contact IT? The issue is, when I contact them, I should know exactly what's wrong and what I need, otherwise I'm afraid they won't be able to do much ...

Thanks a lot!

Best regards,

DataNibbler

Re: ODBC connection to Excel file

datanibbler — Wed, 18 Feb 2015 11:30:56 GMT

Hi,

don't ask me exactly how and why - but somehow it seems to have worked now - up to a point.

I still click on "Connect", I select "Excel Files" and double-click without entering either username or password and I then get the dialog where I can select a file.

It seems it only works with xls files, not with xlsx. Do I need a newer driver for that?

Only now I get the error message "Could not decrypt file" - that is rather logical ...

=> What to do now?

Re: ODBC connection to Excel file

datanibbler — Wed, 18 Feb 2015 11:43:38 GMT

OK,

I have now been able to connect via ODBC_32Bit to an UNENCRYPTED Excel_file in XLS format;

<=> in reality, I will have to a file in XLSX format and it will be ENCRYPTED, otherwise I wouldn't need all this ...

I'm still at a loss at how to do this.

I'll try experimenting and in "emergency", I can ask our external QlikView_consultant who will be on site in the first days of March, I guess.

My Connect-String right now looks like this:

ODBC

CONNECT32 TO [Excel Files;DBQ=S:\COMMON\31 MAINTENANCE\STAPLER\Staplerliste NTB 2_copy_FH.xls];
SQL SELECT *
FROM `S:\COMMON\31 MAINTENANCE\STAPLER\Staplerliste NTB 2_copy_FH.xls`.`'NTB II$'`;

Can anybody help me with the last step of adding the password?

Thanks a lot!

Best regards,

DataNibbler

Re: ODBC connection to Excel file

jonathandienst — Wed, 18 Feb 2015 11:54:33 GMT

I think you need to create a DSN for this to work. On a 64 bit machine, you will need to explicitly run the 32 bit ODBC administrator, a the Excel drivers are 32 bit (at least on my machine)

The 64 bit adminisrator is:

C:\Windows\System32\odbcad32.exe

The 32 bit administrator is:

C:\Windows\SysWOW64\odbcad32.exe

(No that is not a typo = the 64 bit is in the folder named 32 and the 32 bit is in folder named 64 - go figure:)

Anyway use odbcad32 to create a system DSN using the driver:

Microsoft Excel Driver (*.xls, *.xlsx, *.xslm, *.xlsb)

You should be able to add login credentials to the DSN. Now select ODBC (and check Force 32 bit if you are using a32 bit driver on a 64 bit machine) and choose the system DSN you created. Then click Select to bring a list of tables (sheets and named ranges in the Excel file).

This worked perfectly when trying to read from a compiled binary XLSB file.

HTH

Jonathan

Re: ODBC connection to Excel file

datanibbler — Wed, 18 Feb 2015 12:31:40 GMT

Hi Jonathan,

so I need to run this .exe to create a DSN that will, due to the credentials I add there, be valid for only that one Excel_file (others with a different password will not work with this), is that what you're telling me?

When I use the wizard to "Connect" in the script_editor, I can add credentials to the DSN I select ("Excel File") as well - but I do not know which username to use? The file has a password all right, but no username ...

Anyway, I still get the message "Could not decrypt file".

What do do now? Can you help - first tell me whether I can just use the wizard or I really need to run that .exe on my C_drive (in SysWOW64) - and if I need to use that .exe, what exactly do I have to do there? Maybe you could add a screenshot if possible?

Thanks a lot!

Best regards,

DataNibbler

Re: ODBC connection to Excel file

datanibbler — Wed, 18 Feb 2015 13:10:31 GMT

Hi Jonathan,

I tried while waiting, to use that odbcas32.exe (in the SysWOW directory) to create a SYSTEM DSN - explicitly, for that's what you said, not a USER_DSN, right?

=> Well, I cannot do that as I have no admin_privileges, but I could go through the process, it just didn't finish - but even so I did not see where I could add login credentials to the DSN - and I have no username for that file, just a password.

Re: ODBC connection to Excel file

Wed, 18 Feb 2015 13:15:39 GMT

Hi Data,

maybe have a quick look at this thread

http://community.qlik.com/message/626457#626457

When creating the connection, you can specify the user and password to use as normal, so that should work fine for your protected excel

hope that helps

Joe

Re: ODBC connection to Excel file

datanibbler — Wed, 25 Feb 2015 08:47:30 GMT

Hi,

here I go again: IT has just installed the newest version of MS_Office on my laptop and we tested it successfully yesterday (connecting to an unencrypted file with the ending .xlsx, that is).

Strangely, I tried it quite a lot today and once in a while the connect_string worked, then it didn't ... but even with the connect_string being built by the wizard (I guess that means that the connection was established, no?) I still have no idea how to SELECT any data from that file. I just don't know the syntax.

The connect_string I built successfully yesterday reads

>>> ODBC CONNECT32 TO [Excel Files; DBQ = [server_file_path]\[filename].xlsx; <<<

The thing is, I have three drivers listed in the dialogbox (after clicking "Connect")

- Excel

- Excel Files

- Excel_bk

When I click on "Select" right away, I don't know which one is used? I get the dialogbox to select my file right away.

=> I navigate to the file, then I select from that dropdown_box on the left side either "Excel files (.xls)" or "All files". After that, I click on the specific file I want to open and "OK".

Then I keep getting the error_message "External table is not in the expected format".

Can anyone help me there?

Can that have something to do with blanks in the file_name? Though it worked yesterday and the file also has blanks ...

Thanks a lot!

Best regards,

DataNibbler

F... me - it seems that when I use the original file instead of the copy I just created, it works - I get the correct selection screen to choose what I want to retrieve. That's strange because other than copying the file, I didn't change anything about it ...

P.P.S.: Nope - even than - the SELECT string is generated correctly, but when I execute it, I get an error_message about "Error in ODBC connection". Annoying ...

Re: ODBC connection to Excel file

datanibbler — Wed, 25 Feb 2015 10:38:21 GMT

Hah!

I've made it.

Seems I need both - the connect_string that was generated successfully at first AND the SELECT_statement, fully specifying the file and workbook.

What I'm not quite sure about - for some sheets, there are several entries in that dialog. One just states the sheet_name, then there is some "...Print_Area" and some "...Filter_Area". The "...FilterArea" features the real names of the fields (rather than F1, F2 ...) and that worked now. But why that and not one of the others?

Let's see...

P.S.: OK, the others now work, too. The last remaining hurdle is thus the encryption.

I guess I cannot specify the password in the connect_string for it is not (like with a database) part of my credentials, but only
valid for that one file.