Displaying Long Text Fields in Tableau from Excel

Data

Displaying Long Text Fields in Tableau from Excel

The Problem

Many times, it is necessary to display content in Tableau in a verbose manner. For example: displaying comments from a survey or showing lengthy project descriptions or calls to action in an executive dashboard.

Connecting Tableau to a database with long string fields or textarea fields results in no issues.

However, when working from an Excel spreadsheet or Microsoft Access, any string entered into a field that is longer than 255 characters gets automatically trimmed in Tableau. This is caused by the Microsoft JET Engine Driver for Excel and Access, which has the limitation to 255 characters.

If I enter a long string into an Excel field and then connect to Tableau, I get the following results:

Ex. 1 & 2 – Excel spreadsheet with three rows, each with long string values.  Tableau only renders the first 255 characters of each string dimension due to the limitations from the JET Engine Driver.

I want to be able to show the entire string field in Tableau. Fortunately, there is a neat workaround to reach the desired results in Tableau. 

The result requires two tricks. One in Tableau, and one in the Excel spreadsheet itself.

The Fix

Create two or more new columns in the Excel spreadsheet.  For my example, I have added three columns, named “First Part”, “Second Part”, and “Third Part.”  In each of these columns enter a formula that grabs different chunks from the Long Description field and separates them.

My Long Description is in column  ‘C’.  The formulas are as follows: 

Column and Formula

First Part:  =LEFT(C2,255)

Second Part:  =MID(C2, 256, 255)

Third Part:  =MID(C2, 512, 255)

Ex. 3 – The resulting columns parse the original Long Description field and only keep the parts limited by the formulas.

After saving the spreadsheet, refresh the view in Tableau.

In order to get all of the parts of the Long Description into one field, common sense would say to simple concatenate the three resulting strings.  Unfortunately, the JET Engine Driver will still limit the resulting calculation to 255 characters.

The workaround requires a simple table calculation:

Ex. 4 – Tableau Table Calculation.

WINDOW_MIN( 
  ATTR([First Part]) + 
  IFNULL(ATTR([Second Part]), “”) + 
  IFNULL(ATTR([Third Part]), “”)
)
The table calculation retrieves the ATTR from each part and concatenates them together. The IFNULL functions take care of any nulls received if the Second Part or Third Part end up empty due to the Long Description being shorter than 255 characters.
The resulting field will be a discrete measure in the data window*. Drag this to the text card and the complete Long Description will show.

Ex 5. – The resulting visualization in Tableau with the Table Calculation.

*Since this is a table calculation, make sure the direction and scope of the calculation is set to ‘CELL’.

More About the Author

Tom McCullough

Central Region Lead
New Default Setting in Tableau Server 9.1 Will Break Embedded Dashboards If you embed your Tableau analytics dashboards through either a straight embedded i-frame or with the JavaScript API, upgrading your ...
Using import.io, Python and Tableau LOD Calculations to Visualize Top Baby Names over 101 Years I haven’t had a ton of time to really dig into the new features of Tableau 9, but I did want to start playing with the new Level ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072