Error: extraneous " in field when loading csv into TCRM

Question

I am relatively new to Salesforce so please bear with me. I am trying to push a CSV file to the Tableau CRM via the External Data API. I keep getting my data upload rejected, with the following error:

Something went wrong while executing the digest node: failed to open CSV reader: line 1, column 124: extraneous " in field (02K17000000wQUAEA2_03C17000001DqZNEA0)

Now, this seems to be happening on a very specific kind of field value. The following are some examples:

"\"COMPANYNAME, INC.\"" "\"NOTICE 2020-23/COVID-19\""

I have played around with some of the values in my CSV file that I am attempting to upload, and I have determined the following:

TCRM seems to flag any text field that is enclosed by double quotes, and also enclosed in escaped double quotes

The two fields given above are examples of this.

I am fine with this being a rule that I have to follow, but I cannot find any documentation around this. I have been working off of this guide (Analytics External Data Format Developer Guide) and there is nothing in here about enclosing a field in double quote enclosed in escaped double quotes. It mentions the following rules only:

• If a field value contains a control character or a new line the field value must be contained within double quotes (or your fieldsEscapedBy value). The default control characters (fieldsDelimitedBy, fieldsEnclosedBy, fieldsEscapedBy, or linesTerminatedBy) are comma and double quote. For example, "Director of Operations, Western Region".
• If a field value contains a double quote, escape the double quote by preceding it with another double quote (or your fieldsEscapedBy value): for example, "This is the ""gold"" standard".
• Field values aren’t trimmed. A space before or after a delimiting comma is included in the field value. A space before or after a double quote generates an error for the row. For example, John,Smith is valid. John, Smith is valid, but the second value is " Smith". "John", "Smith" isn’t valid.

For reference, here is file_format section of my metadata.json file for the dataset I am trying to upload.

"fileFormat": {
    "charsetName": "UTF-8",
    "fieldsDelimitedBy": ",",
    "fieldsEnclosedBy": "\"",
    "fieldsEscapedBy": "\\",
    "linesTerminatedBy": "\r\n",
    "numberOfLinesToIgnore": 1
},

Answer 1

SOLVED! What was happening was that the metadata file was being parsed as is, without any escaping. So, the following metadata.json file_format section:

{
  "fileFormat": {
    "charsetName": "UTF-8",
    "fieldsDelimitedBy": ",",
    "fieldsEnclosedBy": "\"",
    "fieldsEscapedBy": "\\",
    "numberOfLinesToIgnore": 0
  }

...
...
...

}

...was literally being parsed as: enclosing char = \", escaping char = \\ . So no wonder "\"NOTICE\"" did not work, because it is parsing this as: random char, then enclosing char, then some other chars... which is disallowed (cannot having anything before an enclosing char... see about trimming above).

To confirm I tried: \"some other text but \\\"NOTICE\\\" is quoted\" and this worked! (because \" is the enclosing char, \\ is the escape char.

Might I suggest to any Salesforce people out there, please think about updating the documentation! My file_format section of my metadata.json file is virtually identical to the example, and it results in these errors.