w3resource logo


MySQL load file function

MySQL LOAD_FILE() function

<<PreviousNext>>

Seondary Nav

Description

MySQL LOAD_FILE() reads the file and returns the file contents as a string.

To use this function, the file must be located on the host server, user must specify the full path name of the file, and user must have the FILE privilege. The file must be readable and size must be less than max_allowed_packet (set in the my.ini file) bytes. It returns NULL if the file does not exist or can’t be read.

Syntax

LOAD_FILE (file_name)

Where file_name is a file name with path.

MySQL Version : 5.6

Example : MySQL LOAD_FILE() function

For this example, first we will take a backup of the data of author table using the following command -

Code

SELECT * INTO OUTFILE 'C:\\aut_backup.sql'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
FROM author;

Explanation

The above MySQL command will create a file aut_backup.sql in the C drive. aut_backup.sql file will contain all the records of author table.

Contents of aut_backup.sql

"AUT001","William Norton","UK","Cambridge"
"AUT002","William Maugham","Canada","Toronto"
"AUT003","William Anthony","UK","Leeds"
"AUT004","S.B.Swaminathan","India","Bangalore"
"AUT005","Thomas Morgan","Germany","Arnsberg"
"AUT006","Thomas Merton","USA","New York"
"AUT007","Piers Gibson","UK","London"
"AUT008","Nikolai Dewey","USA","Atlanta"
"AUT009","Marquis de Ellis","Brazil","Rio De Janerio"
"AUT010","Joseph Milton","USA","Houston"
"AUT011","John Betjeman Hunter","Australia","Sydney"
"AUT012","Evan Hayek","Canada","Vancouver"
"AUT013","E. Howard","Australia","Adelaide"
"AUT014","C. J. Wilde","UK","London"
"AUT015","Butler Andre","USA","Florida"

In the next step, we will create a table called temp_aut, whose structure is same as author, with the following command -

Code

create table temp_aut like author;

Explanation

The above MySQL command will create an empty table temp_aut.

In the next step, we will insert data into the temp_aut table from aut_backup.sql file with the following command -

Code

load data infile 'C:\\aut_backup.sql' into table temp_aut fields terminated by ',' enclosed by '"';

Records of the temp_aut table after inserting data

mysql> select * from temp_aut;
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      | 
| AUT002 | William Maugham      | Canada    | Toronto        | 
| AUT003 | William Anthony      | UK        | Leeds          | 
| AUT004 | S.B.Swaminathan      | India     | Bangalore      | 
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       | 
| AUT006 | Thomas Merton        | USA       | New York       | 
| AUT007 | Piers Gibson         | UK        | London         | 
| AUT008 | Nikolai Dewey        | USA       | Atlanta        | 
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio | 
| AUT010 | Joseph Milton        | USA       | Houston        | 
| AUT011 | John Betjeman Hunter | Australia | Sydney         | 
| AUT012 | Evan Hayek           | Canada    | Vancouver      | 
| AUT013 | E. Howard            | Australia | Adelaide       | 
| AUT014 | C. J. Wilde          | UK        | London         | 
| AUT015 | Butler Andre         | USA       | Florida        | 
+--------+----------------------+-----------+----------------+
15 rows in set (0.00 sec)

The above data is same as author -

mysql> select * from author;
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      | 
| AUT002 | William Maugham      | Canada    | Toronto        | 
| AUT003 | William Anthony      | UK        | Leeds          | 
| AUT004 | S.B.Swaminathan      | India     | Bangalore      | 
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       | 
| AUT006 | Thomas Merton        | USA       | New York       | 
| AUT007 | Piers Gibson         | UK        | London         | 
| AUT008 | Nikolai Dewey        | USA       | Atlanta        | 
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio | 
| AUT010 | Joseph Milton        | USA       | Houston        | 
| AUT011 | John Betjeman Hunter | Australia | Sydney         | 
| AUT012 | Evan Hayek           | Canada    | Vancouver      | 
| AUT013 | E. Howard            | Australia | Adelaide       | 
| AUT014 | C. J. Wilde          | UK        | London         | 
| AUT015 | Butler Andre         | USA       | Florida        | 
+--------+----------------------+-----------+----------------+
15 rows in set (0.00 sec)

All String Functions

MySQL String Functions, slide presentation



<<PreviousNext>>

Looking for some other tutorial?