13 Replies Latest reply on Mar 14, 2012 9:26 AM by Ian Waring

    Tableau issue with large Excel files?

    Ian Waring

      I have one very large spreadsheet - 135Mb or so in size - that has 10 tabs, 3 of which have data up to the 800,000+ rows mark. When I try to open it in Tableau desktop Professional 7.01, on my local hard disk (ready to do some custom joins to do an extract), I get:

       

      Database error 0x80004005: The Microsoft Office 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.

       

      I'm the administrator on my own machine, the data is on my own hard disk, and i'm the only user. Is Tableau just timing out the connection and telling fibs? :-)

       

      Now seeing if there's another way of loading this in the meantime... before someone says "ETL" to me!

        • 1. Re: Tableau issue with large Excel files?
          Alex Kerin

          Are you accessing all 10 tabs? Are you doing joins between the tabs? Maybe try text files (though the JET engine is still used)

          • 2. Re: Tableau issue with large Excel files?
            Ian Waring

            Hi Alex,

             

            Didn't get as far as giving me any choice of tabs to work from.

             

            I have saved the individual sheets as CSV files, loaded the first, added the second, added the third, then tried the fourth (one field name had an extra space in it), corrected it in Excel, then tried to append it again and Tableau crashed. Now it won't give me the option of appending data to this data source.

             

            I had a critical report to get out ready for a meeting with EMC on Monday, so parked the work and hacked the EMC sales data together and got that dashboard out. However, trying to get the same done for another 12 key vendors - so will try again on Monday.

            • 3. Re: Tableau issue with large Excel files?
              Ian Waring

              By the way, each sheet has 31 columns per tab, but i've taken that down to 3 sheets and around 2.6m records, and it still won't let me read it in. It takes a couple of minutes for Excel to load or save. I also tried the binary format Excel lets you save in for fast loading/saving, but Tableau claims not to be able to read that format. So, at face value, it appears to time out.

               

              I'm also having an experience of having an extract data source only showing a subset of vendor records available despite there being no vendor filter applied on that database in my Tableau workbook (there are global  filters on other workbooks, but they don't have common names). But that's for another day also. It's now the weekend here :-)

              • 4. Re: Tableau issue with large Excel files?
                Alex Kerin

                You may want to save each as csv files, then use a notepad replacement like notepad++ to put them all in one file. Ditch Excel completely.

                • 5. Re: Tableau issue with large Excel files?
                  Ian Waring

                  I'd rather the bug was removed so Tableau could load it with one dash of custom SQL - and without a stack of intermediate steps. At the moment, it's not allowing it in one step or for more than three incremental data loads into the same extract (the crash dump after trying the latter was sent automatically to Tableau).

                   

                  I'm currently managing to crash Tableau 3-4 times per week... though in ever more innovative ways :-)

                  • 6. Re: Tableau issue with large Excel files?
                    Alex Kerin

                    It's probably an issue with JET, which is MS. Still Tableau's problem, but JET isn't going away soon.

                    • 7. Re: Tableau issue with large Excel files?
                      Ian Waring

                      Still the same issue:

                       

                      ++

                      Database error 0x80004005: The Microsoft Office 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.

                      --

                       

                      It's not open - I have a utility that shows all open files, and it's not open by anyone else (besides, it's a single user Windows 7 Enterprise PC where i'm the administrator). If I output the Excel 2007 sheet to a CSV file and try to import, it misses all the field names. Getting really lost now, don't know where to go - short of firing up MySQL and trying to get the data into that somehow. Happy to provide the file to Tableau developers privately if they want a crack at opening it (it is over 300Mb in size)... and the beast takes 40 mins+ to do a recalc.

                       

                      Any help/guidance/pointers would be greatly appreciated.

                       

                      Ian W.

                      • 8. Re: Tableau issue with large Excel files?
                        Alex Kerin

                        What do you mean "Misses the field names?"

                        • 9. Re: Tableau issue with large Excel files?
                          Ian Waring

                          It doesn't list any fields in dimensions or measures from a CSV file of the content. Try to open it with Notepad++ and it claims the file is too big to handle.

                           

                          I'm trying to evangelise Tableau to a few key decision makers and am not looking good not being able to load data into it to do the trend graphs they are looking for. Hence, any clues or other things to try most welcome...

                          • 10. Re: Tableau issue with large Excel files?
                            Ian Waring

                            Logged with Tableau support. Excel file on the way over as I write this.

                            • 11. Re: Tableau issue with large Excel files?
                              Alex Kerin

                              I wonder if this is a character set problem with the CSV - if you were able to open it in notepad++, you could change it of course. Though, if it's from Excel, it should be ANSI I would guess.

                              • 12. Re: Tableau issue with large Excel files?
                                Ian Waring

                                Any ideas how to get an update on my support ticket? I loaded the whole Excel file over last night and would live to know if it loaded okay or not. All my work is stopped dead at the moment :-(

                                 

                                I have another spreadsheet with the same no of columns (sub 50) and brings in over two million records across three sheets, so there is no 65,000 row restriction on the Jet engine. I have already checked all the file permissions and that of the directory, and they are consistent with other files that work fine.

                                 

                                Just feeling a bit lost now. Support ticket is #86939 btw.

                                • 13. Re: Tableau issue with large Excel files?
                                  Ian Waring

                                  Problem solved.

                                   

                                  What happened is that I did a paste at some point with "visible cells only" in Excel - which then crashed. What I did today was to remove a lot of the vlookups on two sheets in the same spreadsheet (one for transactions from our old AS400 system, the other from our new SAP system), which took out some 370,000 rows of multiple =vlookups each. On opening in Tableau, it no longer hung - but successfully then imported the combined version of both sheets - but added new measures called F29 through to F255. Thinks - there is a 256 column restriction in the Jet engine, so why is it getting more data than my 30 columns in the spreadsheet

                                   

                                  Opening the spreadsheet in Excel again, hey presto, my data was in the first 28 columns only. So jumped down column 29, and there were a full row of #N/A's going from column 29 to the furthest column Excel would hold to the right in rows some row beyond row 100,000. This then repeated over several hundred other rows. So, removed all these, ensured all data was clean up after column AC onwards, did the same under the data as a precaution, and voila - data loaded successfully into Tableau.

                                   

                                  So, job done.

                                   

                                  Not sure if it would help if Tableau Desktop should admit to finding data beyond the column header labels at some point (I thought the Jet engine only scanned the top few rows - in Tableau, it seems to insist on going the whole distance before presenting the list of sheets you can pick from), but that's for another day.

                                   

                                  I do appreciate all the assistance given to date. Thank you.