When you create a workbook that connects to Microsoft Excel or text file based data sources, the workbook uses the default connection that does not rely on the Microsoft Jet Data Engine driver (Jet). Although the default Excel and text file connections do not mimic all Jet driver capabilities, using the default data source connections will help improve your experience when working with Excel or text file data sources, including compatibility with Tableau Desktop on the Mac. If you are considering upgrading an existing workbook to use the new connection, review the tables to compare how your data is processed and displayed between the legacy and default connection types.
Note: If there are certain capabilities of the Jet driver that your data relies on, you might need to use the legacy connection to get the expected results. Workbooks that were created before Tableau Desktop 8.2 that use Excel and text file data sources automatically use the legacy connection. To create a new workbook that uses the legacy connection, in Tableau Desktop 8.2, navigate to your Excel or text file data, click the drop-down arrow on Open, and then select Open with Legacy Connection.
File Formats and Attributes
The following tables show some examples of the types of Excel and text files, and tables that you can connect to in Tableau when using the legacy and default connections types.
Excel
| Your Data | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| .xlsb file format | Allows connections to Excel data in .xlsb file format. | Does not allow connections to Excel data in .xlsb format. Connections to Excel data in .xls or .xlsx file formats can be used instead. |
| Excel built-in named ranges | Allows connections to built-in named ranges. | Built-in named ranges are hidden. |
| Hidden sheet | Allows connections to a table in a hidden sheet. | Hides a table in a hidden sheet. |
| Very hidden sheet | Allows connections to a table in a very hidden sheet. | Hides a table in a very hidden sheet. |
| Table that contain charts | Allows connections to tables that contain Excel charts. However, the table does not contain any values. | Hides tables that contain Excel charts. |
| Empty table | Allows connections to an empty table. | Hides an empty table. |
| Table headers | Treats the first row of a table as a header. | Automatically detects whether the first row of a table is a header. |
| Table width | Limits the table width to 255 columns. | No table width constraints. |
| Empty columns and rows | Empty columns and rows are visible. | Empty columns and rows are hidden because they do not contain any values. |
Text
| Your Data | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| File name length | Does not allow connections to a file whose file name is more than 64 characters long. | No length constraints for file names. |
| Multiple period in the file name | Does not allow connections to a file whose file name contains multiple periods. | No period constraints for file names. |
| Number of columns in the file | Uses the number of fields present in the first row to determine the number of columns in the file. | Scans the entire file and uses the most common number of fields in a row to determine the number of columns in the file. |
| Headers rows | Does not automatically detect whether the first row in the file is a header. Note: You can manually specify that the first row in the file is a header. | Automatically detects whether the first row in the file is a header. Note: You can manually override the detection. |
| Empty columns | Empty columns are visible. | Empty columns are hidden because they do not contain any values. |
Characters and Formatting
The following tables show some examples of how your Excel and text file data might be displayed in Tableau when using the legacy and default connection types.
Excel
| Your Data | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| Number of characters in the field name | Field names are truncated to 64 characters. | No constraint on the number of characters in a field name. |
| Special characters in field names | Special characters are not allowed in field names. For example, special characters like quotation marks and periods are converted to number signs. Brackets are converted to parentheses. | Special characters are allowed in field names. |
| Leading and trailing spaces in field names | Leading and trailing spaces in field names are allowed. | Leading and trailing spaces in field names are automatically removed from column names. |
| Duplicate field names | For duplicate field names, an index number is appended onto the field name. For example, Test Scores1. | For duplicate field names, a space and an index number are appended to the field name. For example, Test Scores 1. |
| Excel cell formatting | Supports cell formatting configured using the Format Cells option in Excel. | Does not support cell formatting configured using the Format Cells option in Excel. |
| Precision with currency values | Fields that contain currency values are represented with a maximum precision of four digits | Full precision of currency values is represented. |
Text
| Your Data | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| Special characters in file names | Special characters are not allowed in file names. For example, special characters like periods are converted to number signs. | Special characters are allowed. |
| Characters in header field names | Automatically removes leading spaces from header field names. | Automatically removes leading and trailing spaces from header field names. |
| Field separators | Recognizes only commas as field separators. Note: You can manually override the detection. | Automatically detects the field separator. Note: You can manually override the detection. |
| Text enclosed with quotation marks | Quotation marks used to enclose text values remain visible. | Quotation marks used to enclose text values are automatically hidden. |
| ANSI and OEM code pages | ANSI and OEM code pages are supported. | ANSI an OEM code pages are automatically converted to be cross-platform compatible. |
| BOM code pages | Inconsistently supports BOM code pages. | Supports BOM code pages. |
Data Type Detection
The following tables show some examples of how data type is detected and how certain values are displayed in Tableau when using the legacy and default connection types.
Excel
| Data Type Detection | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| Columns | Data type of a column is determined by the first 8 rows. Note: After the data type of a column in the table has been determined, it cannot be changed. | Data type of a column is determined by the 95% of the first 10000 rows. Note: You can manually override the detection. |
| Date values without time | Date values are assigned a datetime data type. | Date values without time are assigned a date data type. Date values with time are assigned a datetime data type. |
| Number values | All number values are represented as real numbers. | Number values without decimal points are represented as integers. |
| Null values | If a column contains a null cell, the data type for the column is automatically designated as a string data type. | Null cells do not affect data type detection. |
| Reference errors or empty cells | If a column contains cells with reference errors or empty cells, the entire column is interpreted as a string data type. | Reference errors or cells with no values do not affect the data type detection. |
| Overriding data type detection | After the data type of a column in the table has been determined, it cannot be changed. | Data type of a column can be changed after the automatic detection. |
| Time value precision | The smallest measurement of time values is whole seconds. | The smallest measurement of time values is fractional seconds. |
| DecimalSymbol and CurrencyDecimalSymbol schema.ini values | Both DecimalSymbol and CurrencyDecimalSymbol schema.ini values are recognized. | DecimalSymbol and CurrencyDecimalSymbol scheam.ini values are recognized. However if both values are used, DecimalSymbol takes precedence. |
| Cells formatted as text | The data type of a column is detected as a string data type when cells are formatted as text using the Format Cells option in Excel. | Does not support cell formatting configured using the Format Cells option in Excel. |
Text
| Data Type Detection | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| Columns | Data type of a column is determined by the first 25 rows. | Data type of a column is determined by the first 1024 rows. |
| Boolean (True/False) values | Boolean values are assigned the string data type. | Boolean values are assigned the boolean data type. |
| Values that become null | Spaces in a cell, whether enclosed in quotation marks or not, are treated as null values. Columns with null values are detected as the string data type. | Two field separators in a row are treated as a null value. Null values are ignored during data type detection. |
Data Source Connection Properties
The following table shows examples of the differences in data source connection properties in Tableau when using the legacy and default connection types.
| Property | Legacy Connection | Default Connection (8.2 and later) |
|---|---|---|
| Custom SQL | Custom SQL is allowed. | Does not allow the use of Custom SQL. |
| Join type | Allows left, right, and inner join types. | Allows left and inner join types. |
| Join operators | Allows equal to (=), greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), and not equal to (<>) join operators. | Allows equal to (=) join operators. |