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

Updated on 20-Jun-2020 09:14:57

• Related Questions & Answers
• How can we import data from .txt file into MySQL table?
• How can we export all the data from MySQL table into a CSV file?
• How can we import only specific columns from the text file, into MySQL table?
• How can we export some field(s) from MySQL table into a CSV file?
• How can we import CSV files into MySQL tables by using mysqlimport?
• How can we import the text file, having some line prefixes, into MySQL table?
• How can we MySQL LOAD DATA INFILE statement with ‘ENCLOSED BY’ option to import data from text file into MySQL table?
• How can we export all the data from MySQL table into a text file?
• How to import csv file data from Github in R?
• How can we MySQL LOAD DATA INFILE statement with ‘FIELDS TERMINATED BY’ option to import data from text file into MySQL table?
• How can we import the text file, having data on the same line with a separator, into MySQL table?
• What kind of settings can we do to a CSV file by query while exporting the values from MySQL table into a CSV file?
• How can we insert data into a MySQL table?
• How can we export some field(s) from MySQL table into a text file?
• How to import csv file in PHP?