KNOWLEDGE BASE

Resolving Incorrect Data Issues Related to Jet


Published: 07 Apr 2017
Last Modified Date: 07 Apr 2017

Issue

Certain file-based data sources in Tableau Desktop, such as Microsoft Access, Microsoft Excel, and text, may produce 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. Generally speaking there are two groups of issues:

  • 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.

Symptoms of misinterpreted data types

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.

Environment

  • Tableau Desktop
  • Excel
  • Access
  • Text Files

Resolution

Resolving misinterpreted data types

To resolve issues related to misinterpreted data types, follow one of the suggestions listed below:

Upgrade the Tableau data source

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 for your text file

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.

Modify the underlying data

If you cannot 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.

Other options

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.

Cause

  • 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.
  • 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.



 

Additional Information

  • Jet limitations can occur in workbooks created in Tableau Desktop 8.1 or earlier versions that use Excel or text file data sources.
  • Workbooks that use the Jet-based legacy connection.
  • 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 Upgrading Data Sources in Tableau Desktop Help.
Did this article resolve the issue?