Numbers Converted to Strings when Exporting to Crosstab

Issue

When you export a view as a crosstab from Tableau Server, numbers that use a comma as a thousand separator (such as 1,200) might be converted to strings in Microsoft Excel.

Environment

Tableau Server
Microsoft Excel
Non-US locale setting for the Tableau Server user

Resolution

The following workarounds may be used to solve the problem:

Option 1

In Microsoft Excel, select the column that contains the strings that should be numbers, and then open the Find and Replace dialog box.
In the Find and Replace dialog box, make the following selections, and then click Replace All.
- For Find what, press ALT and type 0160, using the number keypad.
- For Replace with, leave the field blank.

The incorrectly formatted strings are replaced with numbers.

Option 2

Download the workbook from Tableau Server.
Open it in Tableau Desktop and export the data as crosstab (Worksheet > Export > Crosstab to Excel)

The downloaded Excel document should contain figures rather than strings.

Option 3

Export the underlying data instead of the crosstab by using any one of the following options:

In browser, use Export > Data > Download All Rows as Text File
From a command line, use tabcmd get "<name>.csv" or tabcmd export <name> --csv

Note: When exporting the underlying data, the formatting will match the "Default Number Format".

Option 4

As an alternative workaround, work with your IT team to change your computer's local setting.

Open the Windows Region and Language settings.
Change Format to match the crosstab formatting, e.g. English (South Africa).
Select Apply.
Open .csv exported from Tableau Server.

Cause

Export > Crosstab was intentionally designed to retain the crosstab formatting as the tool Export > Data exports the underlying data based on the Default Number Format. In certain locales, thousand separators are exported from Tableau Server as non-breaking spaces. When Microsoft Excel encounters a thousand separator in a number that is denoted by a non-breaking space, it treats that number as a string.

Additional Information

Oracle International Language Environments Guide: Decimal and Thousands Separators.

To voice your support for the inclusion of this feature request in a future product release, add your vote to the following Community Idea: Export data to csv for Microsoft and Tableau server.

Discuss this article... Feedback Forum