KNOWLEDGE BASE

Automatically Remove Double Quotations in CSV File


Published: 22 Jun 2021
Last Modified Date: 23 Jun 2021

Question

How to automatically remove the first and last double quotations in a CSV file when there are double quotations inside the contents.

By default, when there is a double quotation for each item in CSV, Tableau will automatically remove the double quotation when importing data sources.
However, when there is a double quotation inside the contents (e.g. "S 01" monitor"), Tableau could not remove the double quotation automatically.

For example, The CSV that could be read without double quotations:
========
Shop,Category,Product,Number
"AA","XXX","S 01 monitor","5"
"AB","XXX","S 02 monitor","3"
"AC","XXX","S 03 monitor","2"
========

The CSV that could be read with all the double quotations:
========
Shop,Category,Product,Number
"AA","XXX","S 01" monitor","5"
"AB","XXX","S 02 monitor","3"
"AC","XXX","S 03 monitor","2"
========

Environment

  • Tableau Desktop
  • CSV/Text Files

Answer

As a workaround make a calculation filed to remove the double quotation.

For example, the below calculation could remove the first and last quotation of a field.
RIGHT(LEFT([Category],LEN([Category])-1),LEN(LEFT([Category],LEN([Category])-1))-1)

Please check the attached workbook for details.

Additional Information

The function to automatically remove the first and last double quotations in CSV files is not currently built in the product.
Did this article resolve the issue?