CODEWITHSUNDEEP
snowflake-cloud-data-platform
Py1996
Py1996

Reputation: 239

How to dump data (Plain text) from S3 to snowflake table

I want to put plain text data to snowflake using this query:

copy into "BETA"."PUBLIC"."TNS"
  from s3://inbound/DnB/TNS
  credentials=(aws_key_id='my_id' aws_secret_key='my_key')
  pattern='.*TNS.*';

but the code causes this error:

Field delimiter ',' found while expecting record delimiter '\n' File 'DnB/TNS/EBF', line 4, character 1247 Row 4, column "TNS"["C1":1] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

Upvotes: 0

Views: 318

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

You may create a file format with a field delimiter that can not be found the files, and use it with your COPY command:

create file format PLAINTEXT TYPE=CSV FIELD_DELIMITER = '*XyZ*';

copy into "BETA"."PUBLIC"."TNS"
from s3://inbound/DnB/TNS
credentials=(aws_key_id='my_id' aws_secret_key='my_key')
pattern='.*TNS.*'
file_format = PLAINTEXT;

This should help you to read the whole line from your files as one column.

CREATE FILE FORMAT https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html

Upvotes: 2

Related Questions