How to ignore error rows during import

I use import into statement to load some sample data into table, if the csv file has some erros rows, can I ignore the these rows? Is there any options?

ERROR: nodelocal://1/KCFP_CFTYDM.csv: error parsing row 753: expected 15 fields, got 16 (row: “9999,1001003800,BANCA POPOLARE DI MILANO SCARL , ITALY,10,NULL,NULL,0,NULL,01,0,0,0,0,0,0”)

Hi @bennyx,

You can’t ignore the rows, but you can seewhich ones are rejected in a separate file using WITH experimental_save_rejected=uri://path/to/save

Once you have that you can create a copy of your original csv without the erroneous rows.

Matt

Got it and thanks a lot.
Do we have a plan with this ignore error rows options? It will be very useful for data migration.

Not a problem!

It would be great if you could create an enhancement request on our GitHub.

You can tag me in the issue @mattcrdb

Thank you!
Matt

Hi, I’ve been running into similar issues with large imports where some rows fail. In my case it’s because of primary key constraint violations (don’t ask why the CSVs I’m trying to import have duplicates like that, they shouldn’t and it’s painful).

However experimental_save_rejected didn’t seem to work for me. I get an error like:

IMPORT INTO mytable (stuff) CSV DATA ('nodelocal://1/stuff-0.csv') WITH experimental_save_rejected;
ERROR:  duplicate key in primary index: duplicate key: blah

It would be wonderful if there were a way to have it complete the import and just show me the rejected rows…

I have a feeling this isn’t exactly what we had in mind with experimental_save_rejected. My sense was that that was more about parsing errors. Please do file an issue about saving rows that are rejected due to constraints.

Created https://github.com/cockroachdb/cockroach/issues/53363