SQL Loader (SQLLDR) is a utility in Oracle to load flat file data to a table. Here’s a sample control file logic helpful when Dates are present in the input file.
Date logic (format) in the control file for SQLLDR (SQL Loader processing)
Here’s a comma-separated input file. The first record is the header. While loading, the SQL Loader skips the first record. The input is a comma-separated file. The first field is Date, and the second field is employee-id. I have named the input file test.dat, and it is located in the D:/ drive.
JOINDATE,EMPID '01.10.2023','ABCDEFG' '02.12.2023,'HIJKLMN'
Before loading, create a table in Oracle SQL-developer
create table sample ( JOINDATE DATE, EMPID CHAR(07) );
Create a control file
Here the name of the control file is p.ctl, which is located in the D:/ drive.
load data INFILE 'D:\sample.dat' into table sample truncate fields terminated by "," ( JOINDATE POSITION(2:10) DATE"DD-MM-YYYY", EMPID POSITION(15:22) )
Run the SQLLDR utility
Type ‘cmd’, and open the command prompt. Run the below command, which loads flat file data to a table. The skip=1 states to skip the first record.
SQLLDR database-name/password@portname control=D:/p.ctl, skip=1