MySQL LOAD statement and usage of line terminator

LOAD statement

MySQL LOAD statement populates a table.

Suppose the user have a .txt file name 'pub.txt' in a folder of its own, containing 1 record per line and separated by tabs and arranged in order as the columns listed in the table. You can use LOAD statement to populate the table. For missing values, the user can use NULL values and that should be represented by ‘\N’ in the text file.

Syntax:

LOAD DATA LOCAL INFILE  '[path/][file_name]' INTO TABLE [table_name ];

Arguments:

Example:

In the following code, the content of the text file pub.txt will be loaded in the publisher table.

The path of the file should be mentioned.

The text file contains the row like -

P002<tab>BPP Publication<tab>Mumbai<tab>India<tab>New Delhi<tab>10<tab>1985-10-01

Code:

LOAD DATA LOCAL INFILE  'pub.txt' INTO TABLE publisher;

Sample table: publisher

MySQL LOAD statement with line terminator

MySQL LOAD statements with ‘\r\n’ as a line terminator can also be used to populate a table.

Example:

The following code will load the records  from the pub.txt file in publisher table. The ‘\r\n’ can be used as a line terminator.

The text file contains the row like -

P002<tab>BPP Publication<tab>Mumbai<tab>India<tab>New Delhi<tab>10<tab>1985-10-01\r\n

Sample table: publisher

Code:

LOAD DATA LOCAL INFILE 'path/pub.txt' INTO TABLE publisher
LINES TERMINATED BY \r\n ;

MySQL loading data into a table with insert statement

To insert new records into a table INSERT statement can be used. The values will be supplied by the user in the same order as columns are listed  in the table. String and date values are needed to be specified within quoted string.

Syntax

INSERT INTO  <table_name> values(value1,value2,....);

Arguments:

Example:

The following statement inserts one row into the table 'publisher' which contains the values according to the sequence of the columns.

Sample table: publisher

Code:

INSERT INTO  publisher 
values(‘P010’,’ Novel Publisher Ltd.’,’ 
mumbai’,’ India’,’ hydrabad’);

MySQL import data from csv using LOAD DATA INFILE

You can import data from a CSV file into a MySQL table. Here are the MySQL table structure and the CSV

MySQL table structure:

Sample Output:

MySQL> describe std;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| rollno | int(11)     | NO   | PRI | NULL    |       |
| class  | varchar(15) | YES  |     | NULL    |       |
| name   | varchar(45) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Details of the CSV file (book1.csv) :

NAME,CLASS,ROLLNO
David,V,9
Subir,Vii,12
Taniya,VI,10
Anish,V,14

Sample Output:

MySQL> LOAD DATA LOCAL INFILE 'book1.csv' INTO table std 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES (name, class, rollno);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
MySQL> SELECT * FROM std;
+--------+-------+--------+
| rollno | class | name   |
+--------+-------+--------+
|      9 | V     | David  |
|     10 | VI    | Taniya |
|     12 | Vii   | Subir  |
|     14 | V     | Anish  |
+--------+-------+--------+
4 rows in set (0.00 sec)

Online Practice Editor:

Previous: MySQL DATABASE
Next: MySQL advance creating table MySQL Constraints