Change data source in Tableau - and keep the formating

It is easy to change the datasource in Tableau by using "Replace data source" in the data menu. However, this action has some limitations as calculated fields, color formatting etc. is not moved but will revert to the default values.

  • How to keep the Formating

Jonathan Drummey showed this great hack on the community, which is a time saver if you change data source and end up with a lot of work because colors, aliases and other metadata will revert to the default values.

The trick is to go inside the XML file behind every Tableau workbook and then swop the section where the data source is defined to a new one. Simply create a workbook with the new connection, open it (with notepad or a similar text-edit tool) and copy/paste the connection string to the existing workbook.

For example a connection string could look like this:

<datasources> <datasource caption='Sheet1 (test file.xlsx)' inline='true' name='excel.41568.583014791664' version='8.1'> <connection auto-extract='yes' class='excel' driver='Ace12' filename='C:\Documents and Settings\Desktop\test file.xlsx' header='yes' imex='1'> <relation name='Sheet1$' table='[Sheet1$]' type='table' /> </connection> <column datatype='string' hidden='true' name='[F1]' role='dimension' type='nominal'> </column> <column datatype='integer' name='[Number of Records]' role='measure' type='quantitative' user:auto-column='numrec'> <calculation class='tableau' formula='1' /> </column> <layout dim-ordering='alphabetic' dim-percentage='0.5' measure-ordering='alphabetic' measure-percentage='0.4' show-structure='true' /> <semantic-values> <semantic-value key='[Country].[Name]' value='&quot;United States&quot;' /> </semantic-values> </datasource></datasources>

Back to blog

Subscribe for updates