MySQL Forums
Forum List  »  CSV Storage Engine

Import Data from CSV - Dont insert records that shows warning(s) or error(s)
Posted by: connect fc
Date: February 13, 2013 04:49AM

Hello All,

I'm importing data from .csv file to Mysql db using "LOAD DATA LOCAL INFILE" query.
.csv contains foll:

ID | Name | Date | Price

01 | abc | 13-02-2013 | 1500
02 | nbd | blahblahbl | 1000
03 | kgj | 11-02-2012 | jghj

My Mysql Table contains following columns:
Id INTEGER
NAME Varchat(100)
InsertionTimeStamp Date
Price INTEGER

MySQL query to load .csv data to the table above :

LOAD DATA LOCAL INFILE 'UserDetails.csv' INTO TABLE userdetails
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(Id,Name,InsertionTimeStamp,Price)
set InsertionTimeStamp = str_to_date(@d,'%d-%m-%Y');

When I executed the query, 3 records got inserted into the table with 2 warnings

a) Incorrect datetime value : 'blahblahbl' for function str_to_date
b) Data truncate at row 3 (because of invalid INTEGER for Price column)

Question :

1. Is there any way for avoiding data to be inserted in table which shows warnings/errors or the row which has invalid data

I dont want Row 2 & Row 3 to be inserted as it contains invalid data

2. For WARNING of Incorrect datetime value above, can I get the row no also?
Basically I want to get exact warning/error with the row number.

Please help!

Best Regards!

Options: ReplyQuote


Subject
Views
Written By
Posted
Import Data from CSV - Dont insert records that shows warning(s) or error(s)
5100
February 13, 2013 04:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.