How to Resolve File Format or Unexpected Character Errors

Issue

Syncs fail with one of the following error messages:

• Error Unexpected character ('X' (code 72)): Expected separator ('""' (code 34)) or end-of-line at line: ?, column: ?.
• Fix File Format
• Too many entries: expected at most 1 (value \#1 (? chars)

Environment

File connectors

Resolution

To resolve this issue, do the following:

1. Verify in the source document that double quotes are correctly escaped as specified in the RFC-4180 standard.
2. In Fivetran, go to the relevant connection page.
3. Select the Setup tab.
4. Click Edit connection.
5. Under Format, explicitly specify the Delimiter character(s) (optional).
6. If needed, set the Non-Standard escape character toggle to ON, then enter the applicable character.
7. Click Save & Test.

Cause

Although a file appears correctly formatted and works in other tools, each parser interprets CSV rules differently. Fivetran's parser follows the RFC-4180 standard and inspects the first 100 rows to identify the likely delimiter. In certain edge cases, it may select the wrong character.

For example, we may determine the wrong delimiter if double quotes within a field are not correctly escaped or if fields regularly include candidate delimiter characters, such as pipes.