MySQL - LOAD DATA Statement

Table of content

LOAD DATA Statement

Using the LOAD DATA statement, you can insert the contents of a file (from the server or a host) into a MySQL table. If you use the LOCAL clause, you can upload the local files contents int to a table.

Syntax

Following is the syntax of the above statement −

LOAD DATA
   [LOCAL]
      INFILE 'file_name'
      [REPLACE | IGNORE]
      INTO TABLE tble_name
      [{FIELDS | COLUMNS}
         [TERMINATED BY 'string']
         [[OPTIONALLY] ENCLOSED BY 'char']
         [ESCAPED BY 'char']

Before discussing some examples first of all, let us verify whether loading local data is enabled, if not you can observe the local_infile variable value as −

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Following is the output of the above query −

Variable_name Value
local_infile OFF

Before you load data from a file make sure you have enabled local_infile option as −

SET GLOBAL local_infile = 'ON';

Make you grant file (or, all) privileges to the database in which your table exist −

GRANT ALL ON test.* TO 'root'@'localhost';

Example

Assume we have created a table using the CREATE statement as shown below −

CREATE TABLE DEMO (NAME VARCHAR(20));

And if we have a file named test.txt with contents as −

'Raju'
'Swami'
'Deva'
'Vanaja'

Following query loads the contents of the test.txt file in the above created table −

load data infile "directory path/test.txt" into table DEMO;

Verification

If you verify the contents of the DEMO table you can observe the records in it as −

select * from DEMO;

Output

The above mysql query will generate the following output −

NAME
Raju
Swami
Deva
Vanaja

FIELDS and LINES

Using the clauses FIELDS and LINES you can choose the field and line terminators in the file from which you need to load data.

Example

Assume we have created a table using the CREATE statement as shown below −

CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   INCOME INT);

And if we have a file named data.csv with contents as −

Krishna,Sharma,19,2000
Raj,Kandukuri,20,7000
Ramya,Ramapriya,25,5000
Alexandra,Botez,26,2000

Following query loads the contents of the data.csv file in to the above created table −

load data infile "Data Directory Path/data.csv" into table employee
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Verification

If you verify the contents of the DEMO table you can observe the records in it as −

select * from employee;

Output

Following is the output of the above query −

FIRST_NAME LAST_NAME AGE INCOME
Krishna Sharma 19 2000
Raj Kandukuri 20 7000
Ramya Ramapriya 25 5000
Alexandra Botez 26 2000

STARTING BY clause

Using the STARTING BY clause you can use a particular sting to mark the starting of a record or a field.

Example

Assume we have a text file sample.txt with the following contents −

$Krishna,Sharma,19,2000
$Raj,Kandukuri,20,7000
$Ramya,Ramapriya,25,5000
$Alexandra,Botez,26,2000

Following query inserts the contents of the above text file into the employee table −

load data infile "directory path/sample.txt" into table 
employee FIELDS TERMINATED BY ',' LINES STARTING BY '$';

Verification

If you verify the contents of the EMPLOYEE table you can observe the records in it as −

SELECT * FROM employee;

Output

The above mysql query generates the following output −

FIRST_NAME LAST_NAME AGE INCOME
Krishna Sharma 19 2000
Raj Kandukuri 20 7000
Ramya Ramapriya 25 5000
Alexandra Botez 26 2000

Uploading Specific Columns from the File

You can also upload only specific column values from a text file. To do so you need to specify the column names in the query.

Example

Assume we have text file named test.txt with the following contents −

100,Thomas,5000
200,Jason,5500
30,Mayla,7000
40,Nisha,9500
50,Randy,6000

You need to place the names of the columns at the end of the query following query inserts the contents of the test.txt file in the employee table −

LOAD DATA INFILE 'Directory Path/test.txt'
INTO TABLE employee
FIELDS TERMINATED BY ','
(age, first_name, income);

Since we didn't have values for the column last_name in the file all the values for this column will be NULL as shown below.

SELECT * FROM EMPLOYEE;

Output

The above query produces the following output −

FIRST_NAME LAST_NAME AGE INCOME
Thomas NULL 100 5000
Jason NULL 200 5500
Mayla NULL 30 7000
Nisha NULL 40 9500
Randy NULL 50 600

Input Preprocessing

In the LOAD statement you can treat the values from the file as user variables, preprocess them and generate value for other columns. Then you can assign this generated value to the desired column using the SET clause.

Example

Assume we have created a table with name test which stores name average score of a person (in 3 subjects) as shown below −

CREATE TABLE TEST (NAME VARCHAR(10), AVG INT);

Assume we have a file containing name and scores (in all 3 subjects) of a person as follow −

Radha, 25, 30, 35
Swami, 28, 36, 31
Deva, 32, 30, 29
Vanaja, 31, 24, 14

Following query reads the scores of each employee as variables, calculates the average score and stores the result in the avg column using the SET clause.

LOAD DATA INFILE 'Data Directory/test.txt'
   INTO TABLE test
   FIELDS TERMINATED BY ','
   (name, @m1, @m2, @m3, @avg)
   SET avg = (@m1+@m2+@m3)/3;

Verification

After executing the LOAD statement, you can verify the contents of the test table as shown below −

select * from test;

Output

Following is the output of the above query −

NAME AVG
Radha 30
Swami 32
Deva 30
Vanaja 23
Advertisements