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

    Stellar Converter for Database

    MySQL /

    Stellar Converter for Database

    Stellar MySQL Log Analyzer

    MySQL /

    Stellar Log Analyzer for MySQL

    Stellar Repair for MySQL

    MySQL /

    Stellar Repair for MySQL

    MySQL /

    How to secure your SQL Database: Tips and Tricks

    Stellar Converter for Database

    MySQL /

    Database Converter Tools

    ‹ MySQL Database Backup› MySQLImport Options

    Recent Posts

    • ChatGPT Subscription Plans
    • Stellar Converter for Database
    • Stellar Log Analyzer for MySQL
    • Stellar Repair for MySQL
    • ChatGPT Capabilities
    • How to secure your SQL Database: Tips and Tricks
    • ChatGPT4 Conversational AI Features
    • Shaping the Future of Development: Exploring Key Trends in Software Engineering
    • Improving Java Performance with Multithreading
    • Open-source Vector Databases

    Back to Top

    Links

    • Contact
    • Privacy Policy
    • Cookie Policy

    www.TestingDocs.com