Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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
JonnyPoole
Employee
Employee

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.

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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?

datanibbler
Champion
Champion
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author


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.

Not applicable

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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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.