Snowflake does not load the data for Cadilla - Ç character as delimiter

Question

I am trying to load the data into sample table with below ddl

create or replace table sample1(id varchar(1000), name varchar(200), city varchar(20));

File content

'1'Ç'aaa'Ç'a'
'2'Ç'bbb'Ç'b'

Doing manual load from UI

I have used the Ç and \u00c7 as delimiter, but I am getting the following error:

Error on line 2, character 1, column 1 ("ID") Number of columns in file (1) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error

Another delimiter, like @, works fine.

Answer 1

Since you are using a multibyte character you need to use a hex value for it as in our docs:

Multibyte characters

    Hex values (prefixed by \x). For example, for records delimited by the cent (¢) character, specify the hex (\xC2\xA2) value.

Setting the FIELD_DELIMITER = '\xC3\x87' along with ENCODING='UTF-8' would allow the COPY INTO to load the data successfully.