Mysql Loader Control File

Mysql Loader Control File is the mean to import a data to a plain text from Mysql database.

Understand with Example

The Tutorial illustrate an example from 'Mysql Loader Control File'.To grasp this example we create a table 'employee1' with required fieldnames and datatypes respectively.

Query to Create Table named employee1:

mysql>CREATE TABLE employee1 (              
         ->    Empid int(10),         
         ->    Empname varchar(60),   
         ->    date date 
         ->  );

Query to insert data into Table named employee1:

The insert into is used to add the records or rows into a table 'employee1'.

mysql>insert into employee1 values(01,'Girish','2008-12-20');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-21');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(03,'vineet','2008-12-21');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(04,'Amit','2008-12-20');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-23');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Sandeep','2008-12-24');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'suman','2008-12-25');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(01,'AAA',' 2008-12-03');
Query OK, 1 row affected (0.02 sec)

 Query to view data of  Table named employee1:

The select query is used to view the detail of table 'employee1'.

mysql> select * from employee1;
+-------+---------+------------+
| Empid | Name    | DOB        |
+-------+---------+------------+
| 1     | Girish  | 2008-12-20 |
| 2     | Komal   | 2008-12-21 |
| 3     | vineet  | 2008-12-21 |
| 4     | Amit    | 2008-12-20 |
| 2     | Komal   | 2008-12-23 |
| 2     | Sandeep | 2008-12-24 |
| 2     | suman   | 2008-12-25 |
| 1     | AAA     | 2008-12-03 |
+-------+---------+------------+
8 rows in set (0.00 sec)

Query to create loader control file of Table named employee1:

The Query below is used to import the table 'employee1' from Mysql database to a plain text 'employee.txt'.

mysql> select * into outfile 'C:/Documents and Settings/Administrator/Desktop/employee.txt'
    -> fields terminated by '\t'
    -> lines terminated by '\n'
    -> from employee1;
Query OK, 8 rows affected (0.01 sec)

Output :-

1	Girish	2008-12-20
2	Komal	2008-12-21
3	vineet	2008-12-21
4	Amit	2008-12-20
2	Komal	2008-12-23
2	Sandeep	2008-12-24
2	suman	2008-12-25
1	AAA	2008-12-03

Query to create table in which the data of the above file(employee.txt) is loaded:

The Query is used to create a table 'Load Employee' in which the data from employee.txt is loaded into a table 'LoadedEmployee'.The format of the created table and the text plain should be same.

mysql> CREATE TABLE LoadedEmployee (
    ->              Empid int(10),
    ->              Empname varchar(60),
    ->              date date
    ->           );
Query OK, 0 rows affected (0.03 sec)

Query to load data from control file created above of Table named employee1:

The below Query helps you to load the data file 'employee.txt' into table 'Loaded Employee'. The SQL has a field delimiter. The  delimiter tab was replaced by \t.

mysql> LOAD DATA INFILE 'C:/Documents and Settings/Administrator/Desktop/employee.txt'
    -> INTO TABLE LoadedEmployee
    -> FIELDS TERMINATED BY '\t'
    -> LINES TERMINATED BY '\n';
Query OK, 8 rows affected (0.05 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

Query to show data loaded in table loadedEmployee:

mysql> select * from loadedEmployee;
+-------+---------+------------+
| Empid | Empname | date       |
+-------+---------+------------+
|     1 | Girish  | 2008-12-20 |
|     2 | Komal   | 2008-12-21 |
|     3 | vineet  | 2008-12-21 |
|     4 | Amit    | 2008-12-20 |
|     2 | Komal   | 2008-12-23 |
|     2 | Sandeep | 2008-12-24 |
|     2 | suman   | 2008-12-25 |
|     1 | AAA     | 2008-12-03 |
+-------+---------+------------+
8 rows in set (0.00 sec)