Tableau for Legacy and Default Excel and Text File Connections

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 DataLegacy ConnectionDefault Connection (8.2 and later)
.xlsb file formatAllows 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 rangesAllows connections to built-in named ranges.Built-in named ranges are hidden.
Hidden sheetAllows connections to a table in a hidden sheet.Hides a table in a hidden sheet.
Very hidden sheetAllows connections to a table in a very hidden sheet.Hides a table in a very hidden sheet.
Table that contain chartsAllows connections to tables that contain Excel charts. However, the table does not contain any values.Hides tables that contain Excel charts.
Empty tableAllows connections to an empty table.Hides an empty table.
Table headersTreats the first row of a table as a header.Automatically detects whether the first row of a table is a header.
Table widthLimits the table width to 255 columns.No table width constraints.
Empty columns and rowsEmpty columns and rows are visible.Empty columns and rows are hidden because they do not contain any values.

Text

Your DataLegacy ConnectionDefault Connection (8.2 and later)
File name lengthDoes 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 nameDoes not allow connections to a file whose file name contains multiple periods.No period constraints for file names.
Number of columns in the fileUses 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 rowsDoes 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 columnsEmpty 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 DataLegacy ConnectionDefault Connection (8.2 and later)
Number of characters in the field nameField names are truncated to 64 characters.No constraint on the number of characters in a field name.
Special characters in field namesSpecial 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 namesLeading and trailing spaces in field names are allowed.Leading and trailing spaces in field names are automatically removed from column names.
Duplicate field namesFor 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 formattingSupports 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 valuesFields that contain currency values are represented with a maximum precision of four digitsFull precision of currency values is represented.

Text

Your DataLegacy ConnectionDefault Connection (8.2 and later)
Special characters in file namesSpecial 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 namesAutomatically removes leading spaces from header field names.Automatically removes leading and trailing spaces from header field names.
Field separatorsRecognizes 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 marksQuotation marks used to enclose text values remain visible.Quotation marks used to enclose text values are automatically hidden.
ANSI and OEM code pagesANSI and OEM code pages are supported.ANSI an OEM code pages are automatically converted to be cross-platform compatible.
BOM code pagesInconsistently 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 DetectionLegacy ConnectionDefault Connection (8.2 and later)
ColumnsData 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 timeDate 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 valuesAll number values are represented as real numbers.Number values without decimal points are represented as integers.
Null valuesIf 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 cellsIf 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 detectionAfter 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 precisionThe smallest measurement of time values is whole seconds.The smallest measurement of time values is fractional seconds.
DecimalSymbol and CurrencyDecimalSymbol schema.ini valuesBoth 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 textThe 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 DetectionLegacy ConnectionDefault Connection (8.2 and later)
ColumnsData 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) valuesBoolean values are assigned the string data type.Boolean values are assigned the boolean data type.
Values that become nullSpaces 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.
PropertyLegacy ConnectionDefault Connection (8.2 and later)
Custom SQLCustom SQL is allowed.Does not allow the use of Custom SQL.
Join typeAllows left, right, and inner join types.Allows left and inner join types.
Join operatorsAllows 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.