How can we import data from a text file having names of the columns in first row?

Sometimes, the input text file has the names of the columns in the first row and to import data from such kind of text file to MySQL table we need to use ‘IGNORE ROWS’ option. To illustrate it we are using the following example −

Example

Followings are the comma separated values in A.txt file −

Id,Name,Country,Salary
100,”Ram”,”INDIA”,25000
101,”Mohan”,”INDIA”,28000

We want to import this data into the following file named employee3_tbl −

mysql> Create table employee3_tbl(Id Int, Name Varchar(20), Country Varchar(20),Salary Int);
Query OK, 0 rows affected (0.1 sec)

Now, the transfer of data from a file to a database table can be done with the help of the following table −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee3_tbl FIELDS TERMINATED BY ',' ENCLOSED BY ‘“’ IGNORE 1 ROWS;
Query OK, 2 rows affected (0.16 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

In the query above, MySQL will ignore the first row. Ignoring of rows depends upon the value given at the place of ‘n’ in ‘IGNORE n ROWS’ option.

mysql> Select * from employee3_tbl;
+------+-------+---------+--------+
| Id   | Name  | Country | Salary |
+------+-------+---------+--------+
| 100  | Ram   | INDIA   | 25000  |
| 101  | Mohan | INDIA   | 28000  |
+------+-------+---------+--------+
2 rows in set (0.00 sec)

The above result set shows that the data from A.txt file has been transferred to the table.

Abhinaya

Updated on 04-Feb-2020 05:52:05

Previous Page Print Page Next Page