In my previous post, I have given the example to import CSV file into Oracle Table using the stored procedure. And in this post, I am giving an example to import CSV file in Oracle using SQL Loader. There is a lot of difference between loading a file using the stored procedure and using SQL loader in Oracle to import CSV file. That you can see and compare both the methods and you will know.
To import CSV data into Oracle table using SQL Loader, you need to create a control file in which you will sequentially define column names as per the CSV file comma separated fields. For example, below is the one line of CSV file containing fields of EMP table:
In this case, you must know the field mapping from CSV file to the Oracle table in which you want to import the data using SQL Loader. Below is the SQL Loader control file example to import the above CSV file format into Scott schema EMP table.
SQL Loader Control File Example
LOAD DATA APPEND INTO TABLE emp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( EMPNO, ENAME, JOB, MGR, HIREDATE DATE "dd/mm/yyyy", SAL, COMM, DEPTNO )
You can see in above control file example that the table name specified there and also the column names. Save the file with extension name .ctl and call it using SQLLDR command to import data. Below is the example to import data in Oracle table using SQL Loader (SQLLDR command).
Using SQL Loader in Oracle to Import CSV File
sqlldr UserID/[email protected] data=DataFileNameWithPath control=ControlFileNameWithPath log=LogFileNameWithPath
sqlldr vinish/[email protected] data=f:\temp\abc.csv control=f:\temp\sqlldrex.ctl log=f:\temp\sqllog.log
The above command will first connect to Oracle database then will check for the data, control and log file and will start the import.
There are more parameters for SQLLDR command you can check it in Oracle help manual click here.