9 Replies Latest reply: Sep 6, 2016 9:09 AM by Michael Tarallo RSS

    .xlsb ODBC connection QlikSense

    Marco van Zand

      Dear Qliksensers,

       

      I created an ODBC connection to a .xlsb (Excel Binary) but it didn't show any tables/workbooks. What did i do wrong?

        • Re: .qlsb ODBC connection QlikSense
          Michael Tarallo

          Hi Tom.

           

          Can you provide more information. What is a QLSB file for? I searched google and could not find that extension.

           

          Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

           

          Regards,

          Mike Tarallo

          Qlik

          • Re: .qlsb ODBC connection QlikSense
            Marco van Zand

            Sorry XLSB (Excel Binary File)

              • Re: .qlsb ODBC connection QlikSense
                Michael Tarallo

                Hello Tom - I put a few feelers out. I tried this with a VERY simple .xlsb file attached in this thread and it worked - can you give it a try? Note I provided all the screen shots of the ODBC driver I am using. it may be something causing it to fail like the VB script that might be embedded in it. A good test would be to ALSO try in with the ODBC driver in Excel as well and see what happens - just to see if any other errors may be generated.

                 

                8-29-2016 2-21-52 PM.png

                 

                8-29-2016 2-24-30 PM.png

                 

                8-29-2016 2-21-59 PM.png

                8-29-2016 2-20-09 PM.png

                 

                8-29-2016 2-20-27 PM.png

                 

                8-29-2016 2-29-35 PM.png

                 

                8-29-2016 2-30-31 PM.png

                 

                 

                Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                 

                Regards,

                Mike Tarallo

                Qlik

              • Re: .xlsb ODBC connection QlikSense
                Marco van Zand

                Hi Mike,

                 

                The only difference is the version of the driver (14.00.7010.1000). I can select the ODBC in QlikSense but it doesn't show any columns. I can select the workbook.

                 

                In Excel I am not able to select the ODBC

                  • Re: .xlsb ODBC connection QlikSense
                    Michael Tarallo

                    Hi Tom,

                     

                    I am not sure what happened, I apologize - but in my previous reply - I thought I made mention of trying the attached file that I created as a sample but I don't see it - it did not post. Please try the attached file and let me know what happens. At this point it seems to be related to the driver or something in the file that is preventing this from displaying in Qlik Sense. In Excel - you need to use the Query option as in the screen shot to select the specific ODBC driver.

                     

                    Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                     

                    Regards,

                    Mike Tarallo

                    Qlik

                  • Re: .xlsb ODBC connection QlikSense
                    Marco van Zand

                    Hi Michael

                     

                    These are my settings of your enclosed xlsb file. Sorry my laptop is in Dutch

                    QLSB1.png

                    This is the driver I use

                    QLSB2.png

                    In Qlik Sense

                    QLSB3.png

                    Data load and Preview, note that my preview is always blank with ODBC

                    QLSB4.png

                     

                    In Excel I created the ODBC connection. But I dont have any tables or whatever

                    QLSB5.png

                    • Re: .xlsb ODBC connection QlikSense
                      Rohit Kumar

                      Hi Tom,

                      I don't understand why do you need to create an ODBC connection for XLBS file, Please drag and drop it on qlik sense it will automatically create an connection with the same directory or you may tell me what is the need of that connection hat you are trying to create ?

                       

                      Lets say you have created an connection then go to 64-BIT Administrator Tools and create a new connection and there is a option by which you can test the connection , try it as same Michael stated above .

                      Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                        • Re: .xlsb ODBC connection QlikSense
                          Marco van Zand

                          HI Rohit,

                           

                          Yes I can drag and drop but only XLSX instead of XLSB

                          • Re: .xlsb ODBC connection QlikSense
                            Michael Tarallo

                            Thanks for your contribution Rohit - just for clarity - the reason Tom is using ODBC is because it is an Excel Binary file - .xlsb - as you can see in the screen shot below (dragging to data load area)- this is not a natively supported FILE format (like .xls and xlsx) )for Qlik Sense, so ODBC is necessary:

                             

                             

                            Tom - possibly update the ODBC driver to 15.X? Also make sure you are using Qlik Sense 3.0.1 - once we get all the versions matching perhaps we can troubleshoot more.

                             

                            In regards to Excel - I believe you need to define a table space in the spreadsheet in order for that to work:

                             

                             

                            9-6-2016 9-08-25 AM.png

                             

                            Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                             

                            Regards,

                            Mike Tarallo

                            Qlik