COPY INTO snowflake table not loading data - No errors

Question

As part of the Snowflake WebUI Essentials course, I'm trying to load data from 'WEIGHT.TXT' on AWS S3 bucket into a Snowflake DB table.

select * from weight_ingest

> Result: 0 rows

list @S3TESTBKT/W

> Result:1 
> s3://my-s3-tstbkt/WEIGHT.txt  509814  6e66e0c954a0dfe2c5d9638004a98912 Tue, 17 Dec 2019 14:52:52 GMT

COPY INTO WEIGHT_INGEST
FROM @S3TESTBKT/W
FILES = 'WEIGHT.TXT'
FILE_FORMAT = (FORMAT_NAME=USDA_FILE_FORMAT)

> Result: Copy executed with 0 files processed.

Can someone please help me resolve this? Thanks in advance.

Further Information:

S3 Object URL: https://my-s3-tstbkt.s3.amazonaws.com/WEIGHT.txt (I'm able to open the file contents in a browser)

Path to file: s3://my-s3-tstbkt/WEIGHT.txt

File Format Definition:

ALTER FILE FORMAT "USDA_NUTRIENT_STDREF"."PUBLIC".USDA_FILE_FORMAT 
SET COMPRESSION = 'AUTO' 
FIELD_DELIMITER = '^' 
RECORD_DELIMITER = '\n' 
SKIP_HEADER = 0 
FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE' 
TRIM_SPACE = FALSE 
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE 
ESCAPE = 'NONE' 
ESCAPE_UNENCLOSED_FIELD = '\134' 
DATE_FORMAT = 'AUTO' 
TIMESTAMP_FORMAT = 'AUTO' 
NULL_IF = ('\\N');

Stage Definition:

ALTER STAGE "USDA_NUTRIENT_STDREF"."PUBLIC"."S3TESTBKT" 
SET URL = 's3://my-s3-tstbkt';
```

Sriga Sriga · Accepted Answer · 2019-12-18T04:39:59

I believe issue is with your copy command. Try following steps:

Execute list command to get list of files:

List @S3TESTBKT

if your source file appear here just make sure folder name in your copy command.

COPY INTO WEIGHT_INGEST
FROM @S3TESTBKT/
FILES = ('WEIGHT.txt')
FILE_FORMAT = (FORMAT_NAME = USDA_FILE_FORMAT);

COPY INTO snowflake table not loading data - No errors

1 Answers