Error: Unexpected Character / Unexpected Separator

Issue

A sync is failing with one of the following error messages:

Error Unexpected character ('X' (code 72)): Expected separator ('""' (code 34)) or end-of-line at line: ?, column: ?.

{  
 ...  
 ""data"":{  
 ""status"":""FAILURE\_WITH\_TASK"",  
 ""reason"":""com.fasterxml.jackson.dataformat.csv.CsvMappingException: Too many entries: expected at most 1 (value \#1 (? chars) \\""???\\"")\\n at \[Source: (BufferedReader); line: ?, column: ???\]"",  
 ""taskType"":""poorly\_formatted\_file""  
 },  
 ...  
 }

Environment

File connectors

Resolution

To fix the error, follow the steps below:

1. Verify in the source document that double quotes are correctly escaped as specified in the RFC 4180 standard.
2. In the connector's Setup tab, click Edit connection details.
3. Set the Enable Advanced Options toggle to ON.
4. Specify a Delimiter in the connector settings.
5. (If needed) Specify an Escape Character.

Cause

The file appears correctly formatted and is ingested or parsed correctly by other tools or software. Different tools use different rules and libraries to parse CSV. The tool used at Fivetran ingests CSV files that follow the RFC 4180 standard.

A common error, in this case, is having double quotes inside a field that also contains other characters, such as a JSON string.