How can we import data from .CSV file into MySQL table?

Actually.CSV is also a text file in which the values are separated by commas or in other words we can say that text file with CSV(comma separated values). We need to use FIELDS SEPARATED OPTION with LOAD DATA INFILE statement while importing the data from .CSV file to MySQL table. We are considering the following example to make it understand −

Example

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

105,Chum,USA,11000
106,Danny,AUS,12000

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

mysql> Create table employee1_tbl(Id Int, Name Varchar(20), Country Varchar(20),Salary Int);

Query OK, 0 rows affected (0.91 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.csv' INTO table employee1_tbl FIELDS TERMINATED BY ',';
Query OK, 2 rows affected (0.16 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

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

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

Vanithasree

Published on 20-Feb-2018 07:34:15

Previous Page Print Page