12 Replies Latest reply on May 25, 2012 9:56 AM by Ian Waring

    Custom SQL with Excel 2010?

    Ian Waring

      Does anyone have this working?

       

      I logged a support request with an inability to union 40 columns in 3 sheets of the same workbook, but was told I was likely to be testing the very limits of the Jet Engine; suggested I use a database instead. As part of the support process, I was asked to try the 2010 Access Connectivity driver and a matching SP1.

       

      Windows Update has now provided both an SP2 and SP3 in quick succession, and now Custom SQL on sheets that used to work every time now don't work at all. Can't even update some single sheets without using Custom SQL.

       

      Does anyone have Excel 2010 working? Life was bliss with 2007 but my IS dept upgrading me (without me asking for the update) has been hellish ever since :-}

        • 1. Re: Custom SQL with Excel 2010?
          Ian Waring

          FWIW, the error i'm getting is Error 80004005: Cannot open or write to file. It alleges it's open by another user, but it's not. Can't see any locks, temp files or other processes with files open anywhere near where the physical file is, and it doesn't work either on a network drive nor with the Excel file on a local drive.

           

          Desperate enough to load up Qlikview to at least get some work done, but that tries to fit the whole file into memory and passes out when its head hits the ceiling. Waiting for my own IS to allow me to load the files into a database, and apart from that, i'm completely stuck. Can do no work. Now praying Tableau support turn up with something real soon now :-}

          • 2. Re: Custom SQL with Excel 2010?
            Alex Kerin

            Is there a secondary error message from JET? That error message is the generic "something screwed up" and as such is meaninglessness.

             

            I use custom SQL with 2010, but only with a few unions or joins

            1 of 1 people found this helpful
            • 3. Re: Custom SQL with Excel 2010?
              Ian Waring

              The full error text is:

               

              There was a problem connecting to the data source "A-Tool Export (CET

              Reporting - Datasources Combined.xlsx":

               

              Database Error 0x80004005: The Microsoft Access database

              engine cannot open or write to the file ". It is already opened exclusively

              by another user, or you need permission to view and write its data.

              Unable to connect to Microsoft Excel file "X:\CET Reporting -

              Datasources Combined.xlsx". Check that you have access privileges for

              the requested file and that it is not open in another application.

               

              Would you like to edit the connection information?

               

              [Yes]    [No]

               

              That happens on *every* extract file I have now :-}

              • 4. Re: Custom SQL with Excel 2010?
                Alex Kerin

                If this is on every extract then I guess you'll need to go back to Tableau support.

                 

                I wonder if there are some access rights you need to set up within Access? I did find some pages on JET and Access user rights.

                1 of 1 people found this helpful
                • 5. Re: Custom SQL with Excel 2010?
                  Ian Waring

                  Tableau Support cut the first 10,000 rows into a sheet of it's own, and found it loaded. Cited a few historic limitations with the Jet driver, asking that I put my data within the limits specified. However, max 65,000 rows on the sheet has not been a restriction as long as I remember, so I repeated the exercise with 70,000 rows, which also worked.

                   

                  I then tried the two sheets by themselves (all supporting data and sheets stripped out), so a Custom SQL on one sheet with 530,000 rows, the other north of 300,000. Took 10 minutes to process the metadata, but worked. At which point support wanted to close the ticket (with me thinking - I really need to strip all my Excel sheets naked as soon as I want to do any analytics?)

                   

                  I will move the data into Access on Thursday. However, i'm really troubled by the metrics being put on the support folks. This is the second time that all fingers point at the Jet driver, and the ticket is pushed heavily to "resolved" without:

                   

                  1) learning the true root cause

                  2) blaming a piece of code outside of Tableau's own code base with incorrect limits cited

                  3) although my spreadsheet breaks on their own machines, I have no visibility that an engineer would even see what is breaking, to help improve the core product

                   

                  The metrics sound like the support folks, friendly as they are, are batting tickets rather than getting to root causes. And if it is Tableau's choice to use the Jet drivers, I'd have preferred them to take ownership of the customer problem rather than stop the second they can infer outside code as a potential cause without any proof. I can't see what the Jet driver is being asked to do by Tableau, but would hope that the support folks could, and would then be able to make a call on whether I'm exposing a real bug or not.

                   

                  Not trying to be high and mighty. Tableaus products set a really high standard, and I hope I'm not being naive in thinking the support folks are being given measures that may contradict that excellent reputation in the long term.

                  • 6. Re: Custom SQL with Excel 2010?
                    Alex Kerin

                    At the end of the day, JET is a nightmare.

                     

                    Hope you find some resolution with Access.

                    1 of 1 people found this helpful
                    • 7. Re: Custom SQL with Excel 2010?
                      Ian Waring

                      ... Which Tableau uses the Jet driver to access! However, I'm told it's easier to import tables into SQLserver from Access compared to where I am at the moment. I'd really prefer more time in Tableau and less time under the bonnet ;-)

                      • 8. Re: Custom SQL with Excel 2010?
                        Russell Christopher

                        Ian, I'm on Excel 2010 / Desktop 7.03 and I just tried the same thing: A UNION ALL against 3 worksheets of 50 columns x 140k rows.

                         

                        No problems.

                         

                        Created an extract against same, have flipped in and out of using the extract and also have refreshed the extract after updating a row or two - still works.

                         

                        You mentioned that you got things working by removing several sheets in the original workbook; Unioning the same two sheets fails when you have others? How many additional sheets did you actually remove from the original workbook - I've seen JET's metadata discovery process blow up when presented with many sheets, each with many columns while working "on behalf" of Tableau when one connects to a workbook - I wonder if you're running into some flavor of same. If that's the case what you've already done (reduce complexity of the Excel workbook) will probably be your best bet -- Or maybe export each of the three sheets as CSVs into a folder and work from those.

                        1 of 1 people found this helpful
                        • 9. Re: Custom SQL with Excel 2010?
                          Ian Waring

                          Think you're 100% correct.

                           

                          From memory (I'm on leave today), around 10 sheets. Widest is circa 60 columns, most of the others less than 10 columns by<100K rows. Less than 255 columns all in. Size of the .xlsx file appears to be a big factor.

                          • 10. Re: Custom SQL with Excel 2010?
                            Russell Christopher

                            Yeah, I think you're at an unfortunate intersection of what you want to do, and what JET can comfortably do for you. Don't get me wrong - I love JET (my first real job was at Microsoft supporting it!), but its halcyon days are long past

                             

                            MS Access manages to hide some of this by only "interrogating" one Excel worksheet at a time when you import and/or link. If you add up the amount of time interrogating each worksheet as you select each one, they'll add up to just about about the same amount of time as it takes Tableau.

                             

                            However, since Access doesn't allow you to actually JOIN/UNION or do fancy stuff between sheets during an import it isn't forced to work through every worksheet right up front. Tableau has to, so it seems longer.

                             

                            Not too long ago, I worked with a large bank with the same issues - their analysts had monstrous workbooks, many of which with sheets we really couldn't even use as a source of data due to their shape - JET dutifully tried to figure out what the datatypes of each column in each sheet were, and eventually would get sick -- forgot the error message, unfortunately. Removing the "dead wood" resolved the issue.

                             

                            It is interesting that service packs "shortened your runway" on this behavior. Have you tried rolling back the service packs and see if behavior reverts to the norm?

                            • 11. Re: Custom SQL with Excel 2010?

                              Here's a thinking-outside-the-box suggestion. Save the .xlsx file as an Excel 2003 style .xls file. In my opinion the new Open XML which is the underpinning of Office 2007 / 2010 files is insanely complicated and inefficient. The JET engine should handle the older non-XML format very efficiently. The only drawback would be if you run over the Excel 2003 limit on number of rows.

                              • 12. Re: Custom SQL with Excel 2010?
                                Ian Waring

                                I take it back with Tableau Support - they came back to say the JET engine is now sensitive to the amount of data in a single spreadsheet, let alone doing SQL joins of several, so it is officially even more of a liability after all the Service Packs 1-3 are applied by Windows Update.

                                 

                                The number of rows is well over 65,000...

                                 

                                What i've done is to produce another spreadsheet with the final raw guts of what I need to feed into the Tableau extract, and that buys me about two months to replatform the data. My IS folks have insufficient time to set me up with SQLserver anytime soon, so they've asked me to put it in Access in the short term - given that'll be easy for them to migrate when they're ready.

                                 

                                So, thank you for all the help as always. It is greatly appreciated.