Bulkload to snowflake

Hi everyone,

I'm trying to load data to snowflake using bulkload. If new data has not enough columns match table then I received error

Error: Output Data (23): The COPY failed with error: Number of columns in file (124) does not match that of the corresponding table (128), use file format option error_on_column_count_mismatch=false to ignore this error
  File 'sometable_bde58800b7e54b238dfb37f3af4162.gz', line 2, character 1
  Row 1 starts at line 1, column "TABLE_NAME"["feature_abc":124]
  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.

How can i continuely load data skip the error. Missing fields will be NULL like ODBC missing fields

Thanks and Happy new year!

Output

Accepted answers

BrandonB

You could use a text input at the end of your workflow with every column name in it with null values and union with your dataset before output. Then you can use a filter tool and say that a specific column is not null that you know always has data in your main data set. This text input would serve as a placeholder so that when it is unioned, it will add the extra columns and null values.

All comments

BrandonB

jrgo

Hi @huynv96

You may have field values that contain a new line return... the bulk loader does not work very well when this occurs because it COPY command that's executed assumes that new line returns are a new row/record.

I was able to solve by adding a Multi-Field formula tool and run all TEXT columns with the following expression:

REGEX_REPLACE([_CurrentField_], '\n', ' ')

This replaced new line character (\n) with a space using regex. You can change the replacement with something else if you need to be able to distinguish when a new line was suppose to happen.

Jimmy
Teknion Data Solutions

huynv96

Thanks,

But i don't think my data fields have new line character.

huynv96

Thanks, @BrandonB

It's a good solution.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335