How can we import data from a text file whose first row contains column names?

Sometimes, the first row of the input text file contains the names of the columns, to import data from such text files into the MySQL table, we need to use the "IGNORE ROWS" option . To illustrate this, we use the following example -

Example

The following are the comma separated values ​​from the A.txt file-

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

We want to import this data into In the 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, you can transfer the data from the file to the database table 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 above query, MySQL will ignore the first row. Ignored rows depend on the value given at position "n" in the "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 in the A.txt file has been transferred to the table.

The above is the detailed content of How can we import data from a text file whose first row contains column names?. For more information, please follow other related articles on the PHP Chinese website!