INFILE
INFILE in SQL is used to read data from a text file. It is often utilized in the LOAD DATA INFILE statement for inserting multiple rows of data into a table from the file. The text file for INFILE is typically located on the server host and should be readable by the server.
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.