MySQL LOAD statement populates a table

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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 -

P002BPP PublicationMumbaiIndiaNew Delhi101985-10-01

Code :

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

Sample table : publisher

+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| pub_id | pub_name                     | pub_city  | country   | country_office | no_of_branch | estd       |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| P001   | Jex Max Publication          | New York  | USA       | New York       |           15 | 1969-12-25 |
| P002   | BPP Publication              | Mumbai    | India     | New Delhi      |           10 | 1985-10-01 |
| P003   | New Harrold Publication      | Adelaide  | Australia | Sydney         |            6 | 1975-09-05 |
| P004   | Ultra Press Inc.             | London    | UK        | London         |            8 | 1948-07-10 |
| P005   | Mountain Publication         | Houstan   | USA       | Sun Diego      |           25 | 1975-01-01 |
| P006   | Summer Night Publication     | New York  | USA       | Atlanta        |           10 | 1990-12-10 |
| P007   | Pieterson Grp. of Publishers | Cambridge | UK        | London         |            6 | 1950-07-15 |
| P008   | Novel Publisher Ltd.         | New Delhi | India     | Bangalore      |           10 | 2000-01-01 |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+

MySQL LOAD statement with line terminator

MySQL LOAD statements with ‘ ’ 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 ‘ ’ can be used as a line terminator.

The text file contains the row like -

P002BPP PublicationMumbaiIndiaNew Delhi101985-10-01

Sample table : publisher

+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| pub_id | pub_name                     | pub_city  | country   | country_office | no_of_branch | estd       |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| P001   | Jex Max Publication          | New York  | USA       | New York       |           15 | 1969-12-25 |
| P002   | BPP Publication              | Mumbai    | India     | New Delhi      |           10 | 1985-10-01 |
| P003   | New Harrold Publication      | Adelaide  | Australia | Sydney         |            6 | 1975-09-05 |
| P004   | Ultra Press Inc.             | London    | UK        | London         |            8 | 1948-07-10 |
| P005   | Mountain Publication         | Houstan   | USA       | Sun Diego      |           25 | 1975-01-01 |
| P006   | Summer Night Publication     | New York  | USA       | Atlanta        |           10 | 1990-12-10 |
| P007   | Pieterson Grp. of Publishers | Cambridge | UK        | London         |            6 | 1950-07-15 |
| P008   | Novel Publisher Ltd.         | New Delhi | India     | Bangalore      |           10 | 2000-01-01 |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+

Code :

  1. LOAD DATA LOCAL INFILE 'path/pub.txt' INTO TABLE publisher  
  2. LINES TERMINATED BY   ;  

 

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  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

+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| pub_id | pub_name                     | pub_city  | country   | country_office | no_of_branch | estd       |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| P001   | Jex Max Publication          | New York  | USA       | New York       |           15 | 1969-12-25 |
| P002   | BPP Publication              | Mumbai    | India     | New Delhi      |           10 | 1985-10-01 |
| P003   | New Harrold Publication      | Adelaide  | Australia | Sydney         |            6 | 1975-09-05 |
| P004   | Ultra Press Inc.             | London    | UK        | London         |            8 | 1948-07-10 |
| P005   | Mountain Publication         | Houstan   | USA       | Sun Diego      |           25 | 1975-01-01 |
| P006   | Summer Night Publication     | New York  | USA       | Atlanta        |           10 | 1990-12-10 |
| P007   | Pieterson Grp. of Publishers | Cambridge | UK        | London         |            6 | 1950-07-15 |
| P008   | Novel Publisher Ltd.         | New Delhi | India     | Bangalore      |           10 | 2000-01-01 |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+

Code :

  1. INSERT INTO  publisher   
  2. values(‘P010’,’ Novel Publisher Ltd.’,’   
  3. 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 :

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 :

MySQL> LOAD DATA LOCAL INFILE 'book1.csv' INTO table std

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY ' '

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)