MySQL LOAD DATA INFILE statement

Overview

In this tutorial, we will learn about MySQL LOAD DATA INFILE statement. We can use this statement to import from a data file that contains table data. By default, MySQL assumes that the data file is located on the server host.

Syntax

The general syntax of the statement is as follows:

LOAD DATA [LOCAL] INFILE ‘<file_name>’

[IGNORE | REPLACE]

INTO TABLE <table_name>

format_specifiers

[IGNORE n LINES]

[column_list]

[SET assignment_list]

MySQL treats the backslash as the escape character in the strings. On Windows operating systems,
the path name separator character is ‘\’. The file name is denoted as a quoted string.

To load the data file named
‘C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\data.txt’

We can overcome this issue, by specifying the separators in the path names as
‘/’ or as ‘\\’ . We can specify the filename as:

‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.txt’

‘C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\data.txt’

The default data file format is that columns are separated by TAB characters and terminated by \n newline characters. Each input line contains a value for each column in the table. However, we can control the data loading operations with several clauses.

The LOAD DATA statement can read files that are located on the server host as well as on the client machine. By default, MySQL assumes that the file is located on the server host.

Example

The simplest LOAD DATA INFILE statement specifies only the data file and the table into which to load the file.

LOAD DATA INFILE ‘Country.txt’ INTO TABLE Country

CSV Import Example

There are several things that we need to know about the data file.

The column and the row separators
The order of the columns
The filesystem and the file privileges required to access the file?

In this example, we will import the comma-separated values( CSV) file format into a database table named CountryDemo

MySQL CSV Before Import

Suppose that a file csvcountrydata.txt contains the countries information, with values enclosed within double quotes and lines terminated by carriage returns. We will import the CSV text data file into the table with the following command:

mysql> LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvcountrydata.txt’
-> INTO TABLE CountryDemo
-> FIELDS TERMINATED BY ‘,’
-> ENCLOSED BY ‘”‘
-> LINES TERMINATED BY ‘\r’;
Query OK, 239 rows affected (0.16 sec)
Records: 239 Deleted: 0 Skipped: 0 Warnings: 0

MySQL LOAD DATA INFILE Example CSV

MySQL server assumes that the data file is located on the server. If the keyword LOCAL is added to the statement, the file is read from the client machine on which the command is executed.
For example, LOAD DATA LOCAL INFILE…

LOCAL means local to the client machine on which the command is executed. The client program reads the data file and sends the file contents to the MySQL server over the network.