sqlldr CSV file load to Oracle DB

We used to get a lot of csv files to upload to QA database as well as production DB’s. It use to be very repetitive task of individually run each and every csv and ctl files to upload the data. So finally we made a small batch script which you can use to upload any number of csv files in one shot without worrying about the file names or building the commands for individual file names.

We are using sqlldr to upload the csv files to database.In below script you just need to change the tns entry and username password to connect to db.

Copy all the csv and ctl files into one folder and change the db connect details in script and run it.

Note: Make sure the csv file and the corresponding ctl file are in same name. (Ex: file1.ctl and file1.csv)

Please find below sqlldr.bat batch script to automate multiple csv file upload using sqlldr

@ECHO OFF
color A
DIR /B *.csv > files_list.txt
FOR /F "tokens=1,2 delims=." %%G IN (files_list.txt) DO (
SETLOCAL EnableDelayedExpansion
@echo.
@echo Processing File:%%G.%%H ...
@echo --------------------------------------------------------
@echo Running: sqlldr userid=DBUSERNAME/*******@DBHOSTNAME:DBPORT/DBSID control=%%G.ctl data=%%G.csv
sqlldr userid=DBUSERNAME/DBPASSWORD@DBHOSTNAME:DBPORT/DBSID control=%%G.ctl data=%%G.csv
@echo.
type %%G.log | find "Total logical"
@echo.
@echo File %%G.%%H Processed.
ENDLOCAL
)

Example Output:

Processing File:DataloadFile1.csv …
——————————————————–
Running: sqlldr userid=system/*******@techpaste.com:1581/techcdadb control=DataloadFile1.ctl data=DataloadFile1.csv

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 0
Total logical records discarded: 0

File DataloadFile1.csv Processed.

Processing File:DataloadFile2.csv …
——————————————————–
Running: sqlldr userid=system/*******@techpaste.com:1581/techcdadb control=DataloadFile2.ctl data=DataloadFile2.csv

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 0
Total logical records discarded: 0

File DataloadFile2.csv Processed.

Processing File:DataloadFile3.csv …
——————————————————–
Running: sqlldr userid=system/*******@techpaste.com:1581/techcdadb control=DataloadFile3.ctl data=DataloadFile3.csv

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 0
Total logical records discarded: 0

File DataloadFile3.csv Processed.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.