2 of 2 people found this helpful
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:
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.
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?
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!
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.
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!
tableau_access_priveledges.PNG 39.8 KB
1 of 1 people found this helpful
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 ???
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...
1 of 1 people found this helpful
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.
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.