9 Replies Latest reply on Jan 4, 2013 9:39 AM by Mike Greening

    Problem with loading Excel files into Tableau Desktop

    Mike Greening

      I've got two Excel files in the same folder, each with the same data structure, with the only difference between the two files being that one file has more rows of data than the other.  The smaller file is about 140 MB, containing about 672,000 rows and 86 columns.  The second file is about 185 MB, about 892,000 rows, and 86 columns.

       

      I can open the smaller file in Tableau and work with it.  The second file gives me this error when connecting:

       

      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.

       

      Any idea what is happening here?  Is there some kind of file size limit?  I have tried the following:

       

      - moving the files to different locations to try to resolve the permissions issue

      - closing any programs that might be using the file

      - changing the file name

      - rebooting and trying to load the file in Tableau

      - trying to load the .tab file (I'm using Excel because this had failed for me in the past and Tableau Support had me convert the .tab file to Excel first, which worked until now)

       

      Again, the files are in the same folder and contain the same data structure, with the larger file simply having more rows.  This problem also exists if I try to refresh the extract of the smaller file with the larger one.

        • 1. Re: Problem with loading Excel files into Tableau Desktop
          Joshua Milligan

          Mike,

           

          This error message can happen if the JET driver can't determine the data type of a column.  For example, if there was a column for total and it contained the values:

           

          10.40

          20.50

          30.10

          BAD DATA

          10.00

          ....

           

           

          Then the JET driver might fail to recognize the column as numeric.  That could be why the smaller file works (it has good data) and the second file doesn't (it might have a bad record).

           

          You might check to see if this is the issue.

           

          Joshua

          2 of 2 people found this helpful
          • 2. Re: Problem with loading Excel files into Tableau Desktop
            Mike Greening

            Hi Joshua, thanks for the info.  I'm happy to dig through the file, but I'm not sure what I'm looking for - what constitutes "bad data" according to Tableau?

            • 3. Re: Problem with loading Excel files into Tableau Desktop
              Joshua Milligan

              Mike,

               

              If this is indeed the cause of the error message, then it is the Microsoft driver failing to recognize the type of the data, because some value in the column doesn't match the expected data type.  So this would be something like a string value in a numeric column or an invalid date.  If you turn on filters in Excel and look at all the values in the drop down, you may find something that doesn't match.

               

              I know it's not much to go on, but I hope it helps!

               

              Joshua

              • 4. Re: Problem with loading Excel files into Tableau Desktop
                Toby Erkson

                If you know that none of your data contains the pound (a.k.a. hash) symbol, #, then in Excel press Ctrl + F (the Find function) and do a search for #.  The pound symbol prefixes any error in your spreadsheet i.e. #DIV!, #REF!, etc.

                 

                Make sure there are no leading or trailing spaces in the data.  Joshua's suggestion of Data >> Filter is a great start.

                • 5. Re: Problem with loading Excel files into Tableau Desktop
                  Mike Greening

                  Thanks for the input guys.  I opened the large data file in Excel and deleted the top 400,000 rows, moved the remaining 492,000 rows to the top of the sheet, then saved the file (about 100 MB).  Tableau connected to the file without issue.  I then opened the large data file again and this time, removed the bottom 492,000 rows and only kept the top 400,000 rows.  I saved the file (about 90 MB), and Tableau also opened that file without any problem.

                   

                  I opened the large data file yet again, changed the name of the file and the save location to be the same as the two smaller files I had created, and Tableau was not able to open the file, giving the same error as reported in the original post (screenshot attached).

                   

                  Since I was able to open both the top half and the bottom half of the data successfully, I think we can rule out any issue with bad data.  It appears that Tableau is either timing out or losing the connection in another way when the file size exceeds somewhere around 150 MB.  Since we have two Tableau Desktop licenses in the office, I tried on the other PC with much higher system specs, and the same error occurred.

                   

                  I really need to get started on generating some reports, so for now I will just have to work with the latest 500k rows of data, but this is not going to be feasible for long as eventually we will be exceeding this amount of data pretty much daily and the reports will not be useful.

                   

                  Any other suggestions will be greatly appreciated!

                  • 6. Re: Problem with loading Excel files into Tableau Desktop
                    Toby Erkson

                    Hmm, interesting.  May be a question for Support.

                     

                    Can you import the Excel data into a MS Access database and then connect Tableau to the Access database?  With that much data that's what I'd do.

                     

                    Just curious, how is the Excel file saved (what is the file extension)?  .xls or .xlsx or ???

                    1 of 1 people found this helpful
                    • 7. Re: Problem with loading Excel files into Tableau Desktop
                      Alex Kerin

                      Or try exporting to a text file (though Tableau will still use JET, which is probably the issue).

                       

                      As you are still generating new data, you are very close to Excel 2010's limit anyway - it's time to take that data into a database - even MYSQL running locally...

                      • 8. Re: Problem with loading Excel files into Tableau Desktop
                        Toby Erkson

                        Take a look at "Two data connections to the same data source, but with too many records for a query" from here.  Maybe blending the data is what you need to do until you find a better fix.

                        1 of 1 people found this helpful
                        • 9. Re: Problem with loading Excel files into Tableau Desktop
                          Mike Greening

                          Thanks for the input everyone.  I seem to have come across a solution.  If I load a large .tab file (>1GB) into Excel, and then save the file as a .txt file instead of .xlsx, it imports successfully into Tableau Desktop.  Excel is performing something on the data that the raw .tab file does not have, because trying to import the raw .tab file into Tableau fails (this was why I was directed by Tableau Support to import into Excel first).

                           

                          I recognize that I'm going to hit the wall for file size soon in both Excel and Access, so I will have to work with Tableau support to figure out what Excel is doing to the data that we can do in house to make it able to upload to Tableau seamlessly.