sqlldr CSV file load to Oracle DB

FAQ

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.


ADVERTISEMENT

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 [email protected]:DBPORT/DBSID control=%%G.ctl data=%%G.csv
sqlldr [email protected]: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 [email protected]: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 [email protected]: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 [email protected]: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.

sqlldr command to upload csv

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

3 Responses

  1. Bill says:

    This looks like something I could use. I have used sqlldr in the past and my control file has always specified the infile. So what does your control file look like when using your method.

  2. Arnab says:

    Does it need us to create separate tables in advance?

Leave a Reply