Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Skip records in MYSQL text file import

Posted on 2012-09-09
6
Medium Priority
?
484 Views
Last Modified: 2012-10-10
I am looking for a way to skip "Bad" or "corrupt" records during the import of a data file.  These are not records at the beginning of the file and can be in the middle of a several hundred thousand record file.  I am using the following to import.

LOAD DATA INFILE 'C:\\DIRNAME\\FILENAME.TXT'
    INTO TABLE TABLENAME
      fields terminated BY '\t'

If I receive an error such as "Error Code: 1406. Data too long for column 'AreaCode' at row 155762.

So if I want to then run it again skipping row 155762 is there a way to do that?
0
Comment
Question by:pwbigdawg
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38381330
IGNORE 155762 LINES
should ignore the first 155762 lines in the file.  Is that what you want?
0
 

Author Comment

by:pwbigdawg
ID: 38381410
No, I would just like to ignore a single line at record 155762
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38381452
I do not think that is possible.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 24

Accepted Solution

by:
johanntagle earned 750 total points
ID: 38381623
lwadwell is correct.  Your options here are:

1.  Since records 1-155761 are already loaded, just rerun the load data but already ignore the first 155762 lines (the ones that are already loaded plus the trouble-making line)

2.  Pre-process your data file to remove/correct the offending lines

3.  Change the table structure to accommodate the bigger data

4. Change the session SQL_MODE before importing the data.  It looks like it is set to one of the stricter modes.  Setting it to '' will relax it, but the trouble-making row will be imported with the offending column truncated.  You will see which rows had values truncated by doing a "show warnings" after the load data command completed.
0
 

Author Comment

by:pwbigdawg
ID: 38396204
How do I change the session SQL_Mode and what options are available?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38397341
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question