How to import data from text file to mysql database

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

It should be as simple as...

LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE PerformanceReport; 

By default LOAD DATA INFILE uses tab delimited, one row per line, so should take it in just fine.

Answer 2

Walkthrough on using MySQL's LOAD DATA command:

1. Create your table:

   CREATE TABLE foo(myid INT, mymessage VARCHAR(255), mydecimal DECIMAL(8,4)); 

2. Create your tab delimited file (note there are tabs between the columns):

   1   Heart disease kills     1.2 2   one out of every two    2.3 3   people in America.      4.5 

3. Use the load data command:

   LOAD DATA LOCAL INFILE '/tmp/foo.txt'  INTO TABLE foo COLUMNS TERMINATED BY '\t'; 

   If you get a warning that this command can't be run, then you have to enable the --local-infile=1 parameter described here: How can I correct MySQL Load Error

4. The rows get inserted:

   Query OK, 3 rows affected (0.00 sec) Records: 3  Deleted: 0  Skipped: 0  Warnings: 0 

5. Check if it worked:

   mysql> select * from foo; +------+----------------------+-----------+ | myid | mymessage            | mydecimal | +------+----------------------+-----------+ |    1 | Heart disease kills  |    1.2000 | |    2 | one out of every two |    2.3000 | |    3 | people in America.   |    4.5000 | +------+----------------------+-----------+ 3 rows in set (0.00 sec) 

How to specify which columns to load your text file columns into:

Like this:

LOAD DATA LOCAL INFILE '/tmp/foo.txt' INTO TABLE foo FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (@col1,@col2,@col3) set myid=@col1,mydecimal=@col3; 

The file contents get put into variables @col1, @col2, @col3. myid gets column 1, and mydecimal gets column 3. If this were run, it would omit the second row:

mysql> select * from foo; +------+-----------+-----------+ | myid | mymessage | mydecimal | +------+-----------+-----------+ |    1 | NULL      |    1.2000 | |    2 | NULL      |    2.3000 | |    3 | NULL      |    4.5000 | +------+-----------+-----------+ 3 rows in set (0.00 sec)