CODEWITHSUNDEEP
mysqlload-data-infile
vishal
vishal

Reputation: 4083

how to ignore / skip invalid data row in LOAD DATA INFILE while loading data from csv.

I am trying to load data from CSV file, In few date field columns data in CSV is invalid. But when i run LOAD DATA INFILE statement, it inserts NULL value in those column. how can I skip entire row which has invalid data ?

Upvotes: 1

Views: 3791

Answers (3)

DanJGer
DanJGer

Reputation: 514

You can skip invalid null dates by using a NOT NULL on the date column when creating the table.

Upvotes: 0

user2323191
user2323191

Reputation: 1

when you are loading data through csv files. date should be in yyyy-mm--dd HH:MM:SS format. otherwise it inserts NULL values..in csv file select the date whole column and right click on it. click on format cell and select 'date' column in right side and also select 'custom'. then you can see format 'yyyy/mm/dd' change it to 'yyyy-mm--dd HH:MM:SS' . do the same for all date columns using format painter at the top left of csv file. this should solve you problem i guess.

Upvotes: 0

Raul
Raul

Reputation: 607

You cant skip those rows, if you are using load in file function. Do one thing, initially load all data into a temporary table and then from that table pull only valid rows to your staging table. once you will insert all rows into staging table delete that temporary table!

Upvotes: 2

Related Questions