Snowflake file format - ' ' is not recognized with Copy Into command

Viewed 177

I have a txt file, see screenshot below for some of the data in the file.

I'm doing a COPY INTO a new table with no data in it but I'm seeing this error:

Timestamp ' ' is not recognized File 'my_file' , line 2, character 381 Row 1, column "CLICKS"["TS":7]

In the file format below I've added skip_header = 1 because headers are in the file, and field_delimiter = '"' because it looks like each value is separated by " "

This is my file format:

CREATE FILE FORMAT IF NOT EXISTS my_file_format
field_delimiter = '"'
date_format = 'YYYY-MM-DD'
error_on_column_count_mismatch = True
skip_header = 1
escape = NONE
NULL_IF=('NULL','',' ','NULL','NULL','//N', '\\N', '    ') ;

enter image description here

I've also created the table:

create table my_table(
PAGE_URL VARCHAR,
NORMALIZED_PAGE_URL VARCHAR,
TARGET_URL VARCHAR,
NORMALIZED_TARGET_URL VARCHAR,
CLICK_ID VARCHAR,
IMPRESSION_ID VARCHAR,
TS TIMESTAMP,
PUBLISHER_DOMAIN_ID INT,
etc...
);
1 Answers

This is a problem that originates in whatever system the text file was created. It's not a problem that can be solved easily in Snowflake.

Related