Tableau Desktop and Web Authoring Help

If you have workbooks that were created before Tableau Desktop 8.2 that use Microsoft Excel or text file data sources, or you are using the Excel or text file legacy connection option, you have the option to upgrade the data sources in your workbook. By upgrading your Excel and text file data sources you can take advantage of 1.) better data interpretation, and 2.) compatibility on the Mac. If you are considering upgrading an existing workbook to use the new connection, review the tables below to compare how your data is processed and displayed between the legacy and default connection types.

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, navigate to your Excel or text file data, click the drop-down arrow on Open, and then select Open with Legacy Connection.

NOTE: Beginning with Tableau 2020.2, legacy Excel and Text connections are no longer supported. See the Legacy Connection Alternatives document in Tableau Community for alternatives to using the 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
.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
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
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
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
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
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, right, inner, and full outer 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.