How can we upload data into MySQL tables by using mysqlimport?

MySQLMySQLi Database

For uploading the data into MySQL tables by using mysqlimport we need to follow following steps −

Step-1 − Creating the table

first of all, we need to have a table in which we want to upload the data. We can use CREATE TABLE statement for creating a MySQL table. For example, we created a table named ‘student_tbl’ as follows −

mysql> DESCRIBE Student_tbl;
+--------+-------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | int(11)     | YES  |     | NULL    |       |
| Name   | varchar(20) | YES  |     | NULL    |       |
| Class  | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

Step-2 − Creating the data file

Now, in this step we need to create a data file, containing the fields within tab-delimited data. As we know that the name of the data file must be same as the name of MySQL table hence we are creating the data file as ‘student_tbl.txt’, having the data as follows −

1 Gaurav    10th
2 Rahul     10th
3 Digvijay  10th

Step-3 − Uploading the data

Now with the help of the following command by using mysqlimport we can import this file −

C:\mysql\bin>mysqlimport -u root query C:/mysql/bin/mysql-files/student_tbl.txt
query.student_tbl: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

Now with the help of the following query, we can see that the data has been uploaded to the table −

mysql> Select * from student_tbl;
+--------+----------+-------+
| RollNo | Name     | Class |
+--------+----------+-------+
|     1  | Gaurav   | 10th  |
|     2  | Rahul    | 10th  |
|     3  | Digvijay | 10th  |
+--------+----------+-------+
3 rows in set (0.00 sec)
raja
Published on 20-Feb-2018 14:30:56
Advertisements