How to import data from text file to mysql databas

Question

I have a 350MB file named text_file.txt containing this tab delimited data:

345868230   1646198120  1531283146  Keyword_1531283146  1.55    252910000
745345566   1646198120  1539847239  another_1531276364  2.75    987831000
...

MySQL Database name: Xml_Date

Database table: PerformanceReport

I have already created the table with all the destination fields.

I want to import this text file data into a MySQL. I googled and found some commands like LOAD DATA INFILE and quite confused on how to use it.

How can I import this text file data?

Answer 1

1. if it's tab delimited txt file:

LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE event

LINES TERMINATED BY '\r\n';

2. otherwise:

LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE event

FIELDS TERMINATED BY 'x' (here x could be comma ',', tab '\t', semicolon ';', space ' ')

LINES TERMINATED BY '\r\n';

Answer 2

If your table is separated by others than tabs, you should specify it like...

LOAD DATA LOCAL 
    INFILE '/tmp/mydata.txt' INTO TABLE PerformanceReport 
    COLUMNS TERMINATED BY '\t'  ## This should be your delimiter
    OPTIONALLY ENCLOSED BY '"'; ## ...and if text is enclosed, specify here