How can we import only specific columns from the text file, into MySQL table?

Suppose if we have the values for some specific columns in the text file and MySQL table, in which we want to import the data, is having an extra column(s) then by mentioning the names of the columns in the query we can upload the values of those specific columns only. It can be understood with the help of the following example −

Example

Suppose we are having the values of columns ‘id’, ‘Name’ and ‘Salary’ only in the text file as follows −

105,Chum,11000
106,Danny,12000

Now while importing this text file into MySQL table then we need to mention the names of the columns for which we are having the values in a text file in the query as follows −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee9_tbl FIELDS TERMINATED BY ','(id, Name, Salary);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> Select * from employee9_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 105  | Chum           | NULL     | 11000  |
| 106  | Danny          | NULL     | 12000  |
+------+----------------+----------+--------+
2 rows in set (0.00 sec)

From the above result set, it is clear that MySQL only uploaded the value for three columns namely Id, Name and Salary. It stores NULL in the field of ‘Country’.

Sravani S

e

Updated on: 04-Feb-2020

992 Views

Kickstart Your Career

Get certified by completing the course

Get Started