8

I am loading data from text file to mysql table using following query:

LOAD DATA INFILE "myFile.csv" INTO TABLE some_table COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (admin,consumer);

But when my file contains some error lines it is not able skip that line and the whole process stops at that place. I mean if my file format like :

 ---------- sankr : kumar ---------- ramesh:rao ---------- new users add here ---------- sri : vennla ---------- anu : bhavya ----------

I have to load by skipping the line "new users add here". How can do this?

Improve this question

3 Answers 3

Reset to default
10

The keyword you're looking for is IGNORE.

As in:

LOAD DATA INFILE "myFile.csv" IGNORE INTO TABLE some_table COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (admin,consumer);
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Are you sure? From the mysql documentation dev.mysql.com/doc/refman/5.5/en/load-data.html IGNORE means ignore lines that have duplicate primary key with existed row.
Hm. I do see that, but it worked for me. I can't remember where I got that from. I can test later to see if it ignores all errors or just duplicate key errors.
@StefanoGiacone Did you try it? It works for me and a second look at the documentation describes IGNORE as causing warnings instead of errors and continuing to import the file.
My confirmation, at least it worked, when I have data with wrong length.
1

I found that LOAD DATA LOCAL INFILE 'filename' REPLACE would quit after a data format error (illegal date), but LOAD DATA LOCAL INFILE 'filename' and LOAD DATA LOCAL INFILE 'filename' IGNORE continued.

This is contrary to the MySql documentation (ver 5.0) as far as I can tell.

Improve this answer

Comments

-3

It's not possible to skip lines inside the data using LOAD DATA INFILE.

Just delete the record later using a separate query.

DELETE FROM table WHERE column = "new users add here";
Improve this answer

5 Comments

My file contains error line so my query will generate an error while loading and it won't load anything to database. Its stops execution. Any alternative way to achieve this?
"ERROR 1261(01000): Row 73 doesnot contain data for all columns." It is not loading even single line to database.
@sankar ah, I see. Didn't know LOAD DATA did that. In that case, unless you find an option to ignore incomplete data in the manual, you may have to change your incoming data.
we doing bulk import, and on top you suggesting to execute delete queries. double work. downvoted
@Parag so suggest a better way?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.