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’.