将逃生的char弦摄入雪花

发布于 2025-02-10 04:32:49 字数 961 浏览 0 评论 0原文

我有一个问题，我正在使用阶段从Azure Blob存储中拾取.CSV文件。该文件包含由双引号（“）包含的字符串和字符串本身中的双引号（被逃脱）（”）。

一切正常，除了有一个仅包含逃生字符的字符串（“以前的值”，“”，“下一个值” ...）。报价被逃脱了，但随后引发了以下错误：找到字符“ n”而不是字段定界符'，“

我正在使用ADF，该ADF不会逃脱逃生字符（\）。我还尝试将副本转换为（例如：替换（s。$ 3，'\'，'\\'）），但没有成功。

我的文件格式看起来像这样：

CREATE OR REPLACE FILE FORMAT CSV_FORMAT
    TYPE = CSV
    FIELD_DELIMITER = ','
    COMPRESSION = 'AUTO'
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 1
    TIME_FORMAT = 'HH12:MI:SS AM'
    TRIM_SPACE = TRUE
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ESCAPE = '\\'
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
    ON_ERROR = CONTINUE
;

引起问题的CSV示例：

234567,-234567,"B.V. Example, \"Company\"","nl","B.V. Example","00001293",1202,"",2007-09-01 15:20:46.0000000,"Admin",2018-07-23 15:17:02.0000000,"Admin"
123456,-123456,"\","nl","\","00001293",1202,"",2010-11-11 14:20:47.0000000,"Admin",2018-07-23 15:17:02.0000000,"Admin"

如何解决此问题？

原文

I have a problem where I'm using a stage to pick up a .csv file from Azure Blob Storage. This file contains strings enclosed by double quotes (") and double quotes in the string itself which are escaped (").

That all works fine, except there are strings containing just an escape character ("previous value","","next value"...). The quote is escaped but then it throws the following error:
Found character 'n' instead of field delimiter ','

I'm using ADF which doesn't escape the escape character (\). I've also tried transforming the copy into (for example: replace(s.$3, '\','\\')) but with no success.

My file format looks like this:

CREATE OR REPLACE FILE FORMAT CSV_FORMAT
    TYPE = CSV
    FIELD_DELIMITER = ','
    COMPRESSION = 'AUTO'
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 1
    TIME_FORMAT = 'HH12:MI:SS AM'
    TRIM_SPACE = TRUE
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ESCAPE = '\\'
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
    ON_ERROR = CONTINUE
;

A sample of the csv that causes an issue:

234567,-234567,"B.V. Example, \"Company\"","nl","B.V. Example","00001293",1202,"",2007-09-01 15:20:46.0000000,"Admin",2018-07-23 15:17:02.0000000,"Admin"
123456,-123456,"\","nl","\","00001293",1202,"",2010-11-11 14:20:47.0000000,"Admin",2018-07-23 15:17:02.0000000,"Admin"

How to solve this issue?

分享到QQ

分享到微博