TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

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.

Import using mysqlimport

https://www.testingdocs.com/import-data-with-mysqlimport-utility/

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL Database Backup› MySQLImport Options

Recent Posts

  • Update draw.io on Windows
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com