Last Modified Date: 16 Aug 2016
When you use certain file-based data sources in Tableau Desktop, such as Microsoft Access, Microsoft Excel, and text, you might experience unexpected issues that are related to limitations with the Microsoft Jet database engine (Jet).
These issues can be difficult to troubleshoot, and most times the cause is not evident.
This article focuses on the following:
- Issues that are related to data types, including errors in the data itself.
- Error messages that suggest that Tableau may have misinterpreted a field’s data type.
Note: Jet limitations can occur in workbooks created in Tableau Desktop 8.1 and earlier that use Excel and text file data sources, workbooks that use the Jet-based legacy connection, and workbooks that use an Access data source. To avoid Jet data type limitations, consider upgrading your Excel or text file data sources. For more information, see in Tableau Desktop Help.
Symptoms and causes of misinterpreted data types
Jet determines the data types for each column in your data source. It does this by scanning only the first few rows in each column. In many cases the first few rows do not provide enough information to interpret the data type accurately.
In addition, when you connect to Excel data, Jet interprets numeric fields as floating point values. This can cause unexpected behavior in any action that is performed on numeric fields.
After you establish the connection to your data from Tableau, you cannot force the connection to interpret a column differently. The data type definition must occur before the connection is established, in the underlying data.
Some specific symptoms that suggest misinterpreted data types include:
A field that should display as a dimension appears as a measure instead.
A field that should be a string, such as "ID," is interpreted as a numeric field (or vice versa).
Numbers are interpreted as dates, numbers are rounded incorrectly, etc.
Unexpected null values.
When creating a table join, you see the following error: Database error 0x80004005: Type mismatch in expression.
- Currency and other numeric values that are formatted for European locales can be misinterpreted. This can cause the following error: Microsoft JET database error 0x80004005: The text file specification field separator matches decimal separator or text delimiter.
Resolving misinterpreted data types
To resolve issues related to misinterpreted data types, follow one of the suggestions listed below:
Upgrade the Excel and text file data sources in your workbook to stop using Jet. To upgrade the Excel and text file data sources, open the Tableau workbook, and select Data > Upgrade Data Sources. For more information, see Upgrade Data Sources in the Tableau Desktop Help.
Note: Upgrading the data sources in Tableau Desktop does not change the underlying data.
Create a Schema.ini file to explicitly declare each column in your CSV or tab-delimited file. The Schema.ini file must be called Schema.ini and saved in the same folder as the CSV or tab-delimited file.
The Schema.ini file overrides any results from the initial Jet scan with the characteristics that you specify for a particular column, such as the data type or the number of decimal places in a column.
For more information, see Schema.ini File (Text File Driver) page on the MSDN site.
If you can’t create a Schema.ini file immediately, consider modifying the underling Excel or CSV file in one of the following ways:
- Excel or CSV file: Insert a row at the top to establish the data format. You can add one row as the first line of data with the correct format. This can be either valid data or a test row. If you add a test row, remember to exclude the row in Tableau after you establish connection. If your data is a CSV file, consider adding quotation marks around each value in the text file to force Jet to treat each number as a string.
- Excel file: In a column of numeric values that should be interpreted as text, force the Jet engine to interpret the data correctly. One method is to prepend a single quotation mark to the values in the first few rows of the column. For example, change a value of 123 to '123.
You can also edit the Windows registry to force Jet to scan more rows of the Excel or text file. Tableau does not recommend this option because editing the Windows registry incorrectly can have harmful effects on your computer. If you are not familiar with editing the registry, consider one of the above options instead.
Alternate Search Terms:jet, data types, legacy connection, access, windows, database error, 0x80004005, mismatch