Snowflake COPY命令报错：Found character instead of field delimiter '|' 求助

CSV文件包含行数据：

"TEXT"|"123584543"||||"Sherly"||"E'Sheryl"|||"DOCT"||"DC"|||||"AC"|||||||||||

尝试创建文件格式：

Create or Replace file format test_stg
type = CSV
RECORD_DELIMITER = '\n'
FIELD_DELIMITER = '|'
FIELD_OPTIONALLY_ENCLOSED_BY = '\042'
SKIP_HEADER=1
empty_field_as_null = true
ESCAPE = '"';

执行报错：
SQL compilation error: value ["] for parameter 'FIELD_OPTIONALLY_ENCLOSED_BY' conflict with parameter 'ESCAPE'

改用以下语句创建文件格式成功：

create or replace file format test_stg1
  type = csv
  record_delimiter = '\n'
  field_delimiter = '|'
  skip_header = 1
  null_if = ('NULL', 'null')
  empty_field_as_null = true
  FIELD_OPTIONALLY_ENCLOSED_BY = '0x22';

但执行COPY命令时出现异常：Found character instead of field delimiter '|'

1. 创建正确的文件格式 #

移除冲突的ESCAPE参数，添加ESCAPE_UNENCLOSED_FIELD = NONE避免未包围字段的转义干扰，调整后的SQL如下：

CREATE OR REPLACE FILE FORMAT test_stg_correct
TYPE = CSV
RECORD_DELIMITER = '\n'
FIELD_DELIMITER = '|'
FIELD_OPTIONALLY_ENCLOSED_BY = '"' -- 等价于'0x22'，写法更直观
SKIP_HEADER = 1
EMPTY_FIELD_AS_NULL = TRUE
NULL_IF = ('NULL', 'null')
ESCAPE_UNENCLOSED_FIELD = NONE;

2. 验证并执行COPY #

先通过验证模式排查错误行，再执行复制：

-- 先验证错误
COPY INTO your_target_table
FROM @your_stage/path/to/your_file.csv
FILE_FORMAT = test_stg_correct
VALIDATION_MODE = RETURN_ERRORS;

-- 验证通过后执行复制
COPY INTO your_target_table
FROM @your_stage/path/to/your_file.csv
FILE_FORMAT = test_stg_correct;

错误原因 #

• 第一个创建语句报错：FIELD_OPTIONALLY_ENCLOSED_BY与ESCAPE参数冲突。当指定字段包围符为双引号时，Snowflake默认用双引号转义字段内的双引号，无需额外设置ESCAPE，否则触发参数冲突。
• COPY命令报错：未配置ESCAPE_UNENCLOSED_FIELD导致Snowflake尝试解析未被双引号包围字段中的特殊字符（如单引号），误将其识别为分隔符干扰项，添加ESCAPE_UNENCLOSED_FIELD = NONE可解决此问题。

内容的提问来源于stack exchange，提问作者Sherin Shaziya