MySQL Forums
Forum List  »  Newbie

LOAD DATA CONSOLIDATED ERRORS
Posted by: Surya P
Date: July 08, 2021 12:47AM

We are trying to use MYSQL LOAD DATA IN FILE command in Mysql Workbench (version 8.0.25). Whenever there is a fault in the data file, the load is terminated and the error is displayed on that particular line. Once we close this error and re-run the Load, it is displaying the next error. We have to process more than 5 lakh records per day. Is there any way to continue the load till end and display all the errors with consolidated error messages and line numbers? Handling errors line by line and restarting the load each time will work out to be inefficient for our product.

My sample table structure for table_name bank3 is as below
TRAN_ACCOUNT varchar(30)
TRAN_SEQ_NUM int
CARD_NUM varchar(50)
TRAN_AMOUNT int
TRAN_DATE date

The text file (sample3.txt) that I’m trying to load is as below (it has wrong data in 4 places)
123419763444|6785|5678XXXXXXXX7760|6700|20-05-30
567876987090|6876|3400XXXXXXXX3233||19-05-14
786878565656||5622XXXXXXXX5434|9000|21-04-15
654635436547|8798|6354XXXXXXXX6544|6000|20-03-04
987987870890|9877|4565XXXXXXXX9886|7600|19-01-31
987987870890|9877|4565XXXXXXXX9886|7600|198-01-31
987987870890|9877|4565XXXXXXXX9886|7600|19-01-31
987987870890|9877|4565XXXXXXXX9886|7600|19-01-31
987987870890|9877|4565XXXXXXXX9886|7600|19-01-31
987987870890|9877|4565XXXXXXXX9886|7600|
987987870890|==*|4565XXXXXXXX9886|7600|19-01-31
987987870890|9877|4565XXXXXXXX9886|7600|19-01-31

Command Used:
LOAD DATA INFILE 'D:/RECON/mysql/sample3.txt'
INTO TABLE BANK3 FIELDS TERMINATED BY '|'
(TRAN_ACCOUNT,TRAN_SEQ_NUM, CARD_NUM,TRAN_AMOUNT,TRAN_DATE);
Error that I’m getting:

Expected Output:
Load Complete. Errors: 4 Success: 8 (On click display line numbers of 4 Errors)
Please let me know the solution to get the desired output. Thanks in advance!

Options: ReplyQuote


Subject
Written By
Posted
LOAD DATA CONSOLIDATED ERRORS
July 08, 2021 12:47AM


Sorry, only registered users may post in this forum.

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.