Automatically Remove Double Quotations in CSV File

發佈時間 : 22 Jun 2021
上次修改日期 : 24 Aug 2022

問題

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"
========

環境

Tableau Desktop
CSV/Text Files

答案

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.