Issue
- Error Unexpected character ('X' (code 72)): Expected separator ('"' (code 34)) or end-of-line at line: ?, column: ?.
- Error: "Fix File Format"
Environment
File connectors:
- Amazon S3
- Azure Blob Storage
- SFTP
- Email CSV connector
All connectors that can ingest CSV files.
Resolution
1. Check that double quotes are correctly escaped as specified in RFC 4180 standard.
2. If needed, specify an Escape Character in the connector settings - see image below.
3. Specify the delimiter as a comma in the connector settings.
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 RFC 4180 standard.
A common error in this cases is having double quotes inside a field that also contains other characters such as a JSON string.
Example:
"{'automation': {'type': 1, 'rule': "HS - MRR - Billing Mail delivery failure on xneelos auto replies "}, 'added_tags': ['delivery failure'], 'ticket_type': 'System', 'status': 'Closed', 'Product': 'None'}"
The RFC states that
6. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes. For example: "aaa","b CRLF bb","ccc" CRLF zzz,yyy,xxx
7. If double-quotes are used to enclose fields, then a double-quote
appearing inside a field must be escaped by preceding it with
another double quote. For example: "aaa","b""bb","ccc"
This makes the above string invalid