21 Replies Latest reply: Feb 25, 2015 7:46 AM by Friedrich Hofmann RSS

    ODBC connection to Excel file

    Friedrich Hofmann

      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

          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

            • Re: ODBC connection to Excel file
              Friedrich Hofmann

              Hi Ali,

               

              did I mention that my Excel_file is encrypted?

              It's not that easy ...

                • Re: ODBC connection to Excel file
                  James Summerson

                  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

                    • Re: ODBC connection to Excel file
                      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 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
                        Friedrich Hofmann

                        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
                            Friedrich Hofmann

                            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
                                Friedrich Hofmann

                                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
                                  Jonathan Dienst

                                  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

                                    • Re: ODBC connection to Excel file
                                      Friedrich Hofmann

                                      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
                                          Friedrich Hofmann


                                          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

                              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
                                  Friedrich Hofmann


                                  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
                                      Jonathan Poole

                                      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

                                        • Re: ODBC connection to Excel file
                                          Jonathan Poole

                                          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;

                                            • Re: ODBC connection to Excel file
                                              Friedrich Hofmann

                                              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
                                                  Jonathan Poole

                                                  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
                                                    Joseph Simmons

                                                    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
                                                        Friedrich Hofmann

                                                        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
                                                            Friedrich Hofmann

                                                            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.

                                                              • Re: ODBC connection to Excel file
                                                                Friedrich Hofmann

                                                                Okay,

                                                                 

                                                                I'll try specifying the Password in the ODBC_connection, this will be used exclusively for that one file. There apparently is a "Password" function - at least, that word is recognized. I cannot find anything rgd. that in the help, though.

                                                                I have another app where there is a password - but that is a database and uses a 64-BIT driver.

                                                            • Re: ODBC connection to Excel file
                                                              Friedrich Hofmann

                                                              Hi Joe,

                                                               

                                                              that thread is about an xlsb file. It doesn't really help me.

                                                              By now I have a way of connecting via ODBC to a workbook with the ending .xlsx and SELECT some data from that - but unless I can figure out how to pass the password to QlikView to open a protected workbook, that doesn't really help me do anything I couldn't do in another way ...

                                                              Do you have any ideas?

                                                              Thanks a lot!

                                                              Best regards,

                                                               

                                                              DataNibbler

                                                               

                                                              P.S.: I have found a thread in some Excel_expert_forum saying it cannot be done via ODBC, only with VBA using these lines

                                                                         Dim xlApp As New Excel.Application
                                                              Dim xlWB As Excel.Workbook = xlApp.Workbooks.Open(Filename:="C:\Path\Filename.xls", Password:="password")

                                                               

                                                              That was back in 2009, but if a macro would be a possibility, let's see. Only I don't know much VBA, not to mention VB_Script.