snowflake-cloud-data-platformsnowflake-schema

SQL Compilation error while loading CSV file from S3 to Snowflake


we are facing below issue while loading csv file from S3 to Snowflake.

SQL Compilation error: Insert column value list does not match column list expecting 7 but got 6

we have tried removing the column from table and tried again but this time it is showing expecting 6 but got 5

below are the the commands that we have used for stage creation and copy command.

create or replace stage mystage
url='s3://test/test'
STORAGE_INTEGRATION = test_int
file_format = (type = csv FIELD_OPTIONALLY_ENCLOSED_BY='"'  COMPRESSION=GZIP);


copy into mytable
from  @mystage
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
FILE_FORMAT = (TYPE = CSV  FIELD_OPTIONALLY_ENCLOSED_BY='"' COMPRESSION=GZIP error_on_column_count_mismatch=false TRIM_SPACE=TRUE NULL_IF=(''))
FORCE = TRUE
ON_ERROR = Continue
PURGE=TRUE;

Solution

  • You can not use MATCH_BY_COLUMN_NAME for the CSV files, this is why you get this error.

    This copy option is supported for the following data formats:

    https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html