0

I have data load from text file, looks like ID column is not coming from text file , it is coming as 0.

enter image description here

drop table table1;
create table table1 (id int, name varchar(50),Ref_by varchar(50),Dept varchar(50), city varchar (100));
select * from table1;

LOAD DATA INFILE 'G:/MYSQL_Load/Data_txt_files/db/tbl_Student.txt' INTO TABLE table1 FIELDS TERMINATED BY '|' LINES TERMINATED BY '|||';
select * from table1;

After load.

enter image description here

Here is the sample, between Id 9 and 10 there are three lines are added as zero. I guess this is because of characters length in the text file I have 4000+ characters for one of the filed. I tired txt and csv both are getting broken.

enter image description here

8
  • Your lines seems to be terminated with '|||\r\n'. For current statement the 2nd line looks not like 2|B|... but like \r\n2|B|..., and the former value is set to 0 because this cannot be treated as numeric value.
    – Akina
    Dec 20, 2022 at 10:49
  • From the other side you may specify .. LINES TERMINATED BY '\r\n' and use LOAD DATA IGNORE. In this case trailing '|||' will be treated as extra data in the line, and according to IGNORE this will generate warnings - ignore them.
    – Akina
    Dec 20, 2022 at 10:53
  • The reason, I have added ||| is because my source having 5000+characters and I thought of adding something at end and move to next line. If I use '\r\n' without ||| the data is breaking and cannot go for 5000+characters. Dec 20, 2022 at 11:18
  • If you share other solution that would helpful Dec 20, 2022 at 11:19
  • Before proposing a workaround, please answer these questions: Can you easily get rid of the trailing "|||"? Does the input have "\r\n" or just "\n"? What OS are you using? Does every row have exactly 5 columns?
    – Rick James
    Dec 20, 2022 at 16:51

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.