SQL Reference - INFILE

Example

LOAD DATA INFILE 'data.txt' INTO TABLE my_table;

Output

Query OK, 3 rows affected (0.01 sec)
Records: 3    Deleted: 0    Skipped: 0    Warnings: 0

Explanation

The LOAD DATA INFILE statement in MySQL is used to read rows from a text file into a corresponding table in the database. In the example code, the ‘data.txt’ file is used as the data source and the data is loaded into the my_table table in the database. The output signifies that the query was successfully executed and that 3 rows of data were affected (i.e., inserted into the table) without any warnings or errors.

Example

CREATE TABLE employees
    (employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25));

BEGIN
    DECLARE
        file_handle UTL_FILE.FILE_TYPE;
    BEGIN
        file_handle := UTL_FILE.FOPEN('MY_DIRECTORY', 'employees.txt', 'R');
        FOR i IN 1..3 LOOP
            UTL_FILE.GET_LINE(file_handle, v_line);
            INSERT INTO employees VALUES
                (TO_NUMBER(SUBSTR(v_line,1,6)),
                SUBSTR(v_line,7,20),
                SUBSTR(v_line,27,25));
        END LOOP;
        UTL_FILE.FCLOSE(file_handle);
    END;
END;

Output

[
    {
        "employee_id": 100,
        "first_name": "Steven",
        "last_name": "King"
    },
    {
        "employee_id": 101,
        "first_name": "Neena",
        "last_name": "Kochhar"
    },
    {
        "employee_id": 102,
        "first_name": "Lex",
        "last_name": "De Haan"
    },
]

Explanation

This script uses the UTL_FILE package to read a file (employees.txt in the MY_DIRECTORY directory). Each record in the file is retrieved with a call to the UTL_FILE.GET_LINE function, which reads a line from the file into a string variable. The individual fields within the record are then identified by using the SUBSTR function and converted (if necessary) by using the TO_NUMBER function, and then the fields are inserted into the employees table.