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:

Sample table: publisher

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 from a local file named 'pub.txt' into the table 'publisher'
LOAD DATA LOCAL INFILE 'pub.txt' INTO TABLE publisher;

Explanation:

• This MySQL code is used to load data from a local text file named 'pub.txt' into a table named 'publisher' in a MySQL database.


• It uses the LOAD DATA INFILE statement, which is used to read rows from a text file into a table at a very high speed.


• The LOCAL keyword indicates that the file is located on the client side (i.e., where the MySQL client is running), as opposed to the server side. This allows for security measures as the server won't access files from the client directly.


• 'pub.txt' specifies the path to the local text file. Ensure that the file exists and the path is correct relative to where the MySQL client is running.


• INTO TABLE publisher specifies the destination table where the data will be loaded. In this case, it's the table named 'publisher' in the database.


• There should be a header line in the text file that matches the columns of the 'publisher' table, and the subsequent lines contain the data to be loaded into the table. The order of the columns in the text file should match the order of the columns in the table. If the columns don't match, you can specify column mappings in the LOAD DATA statement.


• Make sure the MySQL user has the necessary privileges to read the file and write to the table.

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 from a local file named 'pub.txt' into the 'publisher' table
LOAD DATA LOCAL INFILE 'path/pub.txt' INTO TABLE publisher
-- Specify that each line in the file is terminated by a carriage return and a newline character
LINES TERMINATED BY \r\n ;

Explanation:

• LOAD DATA LOCAL INFILE 'path/pub.txt' INTO TABLE publisher:


• This line instructs MySQL to load data from a local file named 'pub.txt' into the 'publisher' table.


• 'path/pub.txt' should be replaced with the actual file path where 'pub.txt' is located.


• LINES TERMINATED BY \r\n:


• Specifies the line terminator used in the file. Here, it indicates that each line in the file is terminated by a carriage return (\r) followed by a newline (\n) character.


• This line terminator is commonly used in text files on Windows operating systems.

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 a new row into the 'publisher' table
INSERT INTO publisher 
-- Specify the values to be inserted into the columns
VALUES ('P010', 'Novel Publisher Ltd.', 'mumbai', 'India', 'hydrabad');

Explanation:

• INSERT INTO publisher:
  
• This line indicates that we are inserting data into the 'publisher' table.


• VALUES ('P010', 'Novel Publisher Ltd.', 'mumbai', 'India', 'hydrabad'):


• Specifies the values to be inserted into each column of the table in the order they appear in the table schema.


• 'P010' is inserted into the first column.


• 'Novel Publisher Ltd.' is inserted into the second column.


• 'mumbai' is inserted into the third column.


• 'India' is inserted into the fourth column.


• 'hydrabad' is inserted into the fifth column.

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

Code:

  
-- Load data from a local CSV file named 'book1.csv' into the 'std' table
LOAD DATA LOCAL INFILE 'book1.csv' INTO TABLE std
-- Specify that fields in the CSV file are terminated by a comma
FIELDS TERMINATED BY ','
-- Specify that fields in the CSV file are enclosed within double quotes
ENCLOSED BY '"'
-- Specify that each line in the file is terminated by a carriage return and a newline character
LINES TERMINATED BY '\r\n'
-- Ignore the first line of the file (usually containing headers)
IGNORE 1 LINES
-- Map the columns in the CSV file to the corresponding columns in the 'std' table
(name, class, rollno);

Explanation:

• LOAD DATA LOCAL INFILE 'book1.csv' INTO TABLE std:


• This line instructs MySQL to load data from a local CSV file named 'book1.csv' into the 'std' table.


• FIELDS TERMINATED BY ',':


• Specifies that fields in the CSV file are terminated by a comma (,). This helps MySQL identify where one field ends and the next begins.


• ENCLOSED BY '"':


• Specifies that fields in the CSV file are enclosed within double quotes ("). This is useful when a field itself contains commas and should be treated as a single value.


• LINES TERMINATED BY '\r\n':


• Specifies that each line in the file is terminated by a carriage return (\r) followed by a newline (\n) character. This line terminator is commonly used in text files on Windows operating systems.


• IGNORE 1 LINES:


• Instructs MySQL to ignore the first line of the file. This is typically used when the first line contains headers and should not be treated as data.


• (name, class, rollno):


• Specifies which columns from the CSV file should be mapped to which columns in the 'std' table.

Sample Output:

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