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:
Name | Description |
---|---|
path | The address of the file. |
file_name | The name of the .txt file. |
table_name | The table where the data will be loaded. |
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:
Name | Description |
---|---|
table_name | Name of the table. |
value1,value2,... | Values which will be inserted into the sequence of columns. |
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook