Loading data to Snowflake target: End of record reached while expected to parse column

1
votes

Observing Error:

End of record reached while expected to parse column '"DIM_EQUIPMENT_UNIT"["EQUNIT_TARE_WEIGHT_TNE":20]' File 'DIM_EQUIPMENT_UNIT_issue_nulll_end.csv', line 17, character 133 Row 16, column "DIM_EQUIPMENT_UNIT"["EQUNIT_TARE_WEIGHT_TNE":20] 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.

Sample Data:

3499933,00010101,99991231,"Y","TSXZ 622095",0,3,-2,-1,5,-2,"1","2017-03-24-17.25.42.000000","COMPASS ",5391,13,-2,"n/a ","n/a",+0.00000000000000E+000 3499948,00010101,99991231,"Y","EOLU 8888370",0,1,28,-1,3,-2,"1","2018-04-26-17.35.47.000000","COMPASS ",5799,-2,-2,"n/a ","n/a",+3.69000000000000E+000 3499968,00010101,99991231,"Y","NONZ 7086849",0,3,-2,-1,5,-2,"1","2017-03-24-17.25.42.000000","COMPASS ",5391,13,-2,"n/a ","n/a",+0.00000000000000E+000 3499992,00010101,99991231,"Y","SGPU 1240279",0,1,31,-1,3,-2,"1","2019-05-22-17.29.11.000000","COMPASS ",6203,-2,-2,"n/a ","n/a",+3.05000000000000E+000 109267,00010101,99991231,"Y","CTSU 425ß85 ",0,1,46,-1,3,-2,"1","2011-05-16-08.52.08.000000","COMPASS ",98,-2,-2,"n/a ","n/a",

DDL:

CREATE OR REPLACE TABLE DIM_EQUIPMENT_UNIT( EQUNIT_ID NUMBER, EQUNIT_VLD_FROM DATE, EQUNIT_VLD_TO DATE, EQUNIT_VLD_FLAG VARCHAR(1), EQUNIT_UNIT_NUMBER VARCHAR(13), EQUNIT_CONSTRUCTION_YEAR NUMBER, FK_TW2130EQCAT NUMBER, FK_TW0020EQT NUMBER, FK_TW2160EQSERIES NUMBER, FK_TW0050OWS NUMBER, FK_TW059VEQLESSOR NUMBER, EQUNIT_CLIENT VARCHAR(1), EQUNIT_LC TIMESTAMP_NTZ, EQUNIT_CB VARCHAR(8), EQUNIT_LOAD_CYCLE_ID NUMBER, FK_TW0820CHT NUMBER, FK_TW0850GST NUMBER, EQUNIT_SAP_ASSET_NUMBER VARCHAR(11), EQUNIT_PRE_INTEGRATION_OWNER VARCHAR(3), EQUNIT_TARE_WEIGHT_TNE FLOAT );

COPY Command Used:

COPY INTO "DIM_EQUIPMENT_UNIT" FROM '@SNOWFLAKE_STAGE/' FILES=('DIM_EQUIPMENT_UNIT_issue_nulll_end.csv') on_error='abort_statement' file_format=(type=csv SKIP_HEADER=1 FIELD_OPTIONALLY_ENCLOSED_BY='"' ESCAPE_UNENCLOSED_FIELD = None ENCODING ='WINDOWS1252' EMPTY_FIELD_AS_NULL=true NULL_IF = ('NULL', 'null', '\N') TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF')

1
snowflake-cloud-data-platform

1 Answers

1
votes

So if you see the last record in your sample data. 18th and 19th column values are "n/a". There is nothing on column number 20. Even it is supposed to be null, it should spit out in the data, like "" or "\N" or NULL.

Since it doesn't have anything, it's giving you end of file error on that column.

Now, you can do either of the below 2 things, a. make sure your sample file has an exact 20 columns, b. If you cannot do that, and if you're ok to ignore the row, change the on-error in copy statement to on_error='continue' This will ignore this row and move forward.