How to Load the Data from FLAT FILE to TEMP Table Using SQL LOADER
By Jag - August 29, 2013
Steps to Load Data from flat file to temp table using SQL Loader:
step1:data will given in flat file,it would be text file/table format etc
step2:to move the data from flat file to temp table,we use sql loader.
step3:save the flat file with name mytest.csv
step4:create contraol based on flat file data columns
i.e
open new notepad,
write below script i.e
loaddata
infile '/appldir/instancename/xxxxx/12.0.0/bin/mytest.csv'
infile '/appldir/instancename/xxxxx/12.0.0/bin/mytest.bad'
insert
into table my_test_table fileds terminated by "," optionally enclosed by '"'
trailing nullcols
(company "trim(:company)"
,year "trim(:company)"
,consultant_num "trim(:consultant_num)"
,id "trim(:id)"
,promotion_code "trim(:promotion_code)"
)
save this script with mytest.ctl
step5:move this mytest.csv,mytest.ctl files to temp folder in server location using winscp and putty software
step6:open the putty software for running the commands
step7:run the pseudo code(depends upon the instances)
step8:cd /tmp
in this only we have our files
step9:type this command "sqlldr"
we get all the needful commands in sqlloader
step10:run this below command to load the data i.e
sqlldr apps/apps1234 control='mytest.ctl' log='mytest.log' bad='mytest.bad' data='mytest.csv'
apps/apps1234-->connecting to the instance with username and password
from the above command
mytest.log --this shows log files records
mytest.bad --this shows failed files records
step11:to check for the log or bad files of our file.write command as
ls -ltr.mytest*
it shows all the files with name as mytest.
step12:to see only log report use this command i.e
more mytest.log
This way we can load the data from flat file to temp tables using SQL Loader.
step1:data will given in flat file,it would be text file/table format etc
step2:to move the data from flat file to temp table,we use sql loader.
step3:save the flat file with name mytest.csv
step4:create contraol based on flat file data columns
i.e
open new notepad,
write below script i.e
loaddata
infile '/appldir/instancename/xxxxx/12.0.0/bin/mytest.csv'
infile '/appldir/instancename/xxxxx/12.0.0/bin/mytest.bad'
insert
into table my_test_table fileds terminated by "," optionally enclosed by '"'
trailing nullcols
(company "trim(:company)"
,year "trim(:company)"
,consultant_num "trim(:consultant_num)"
,id "trim(:id)"
,promotion_code "trim(:promotion_code)"
)
save this script with mytest.ctl
step5:move this mytest.csv,mytest.ctl files to temp folder in server location using winscp and putty software
step6:open the putty software for running the commands
step7:run the pseudo code(depends upon the instances)
step8:cd /tmp
in this only we have our files
step9:type this command "sqlldr"
we get all the needful commands in sqlloader
step10:run this below command to load the data i.e
sqlldr apps/apps1234 control='mytest.ctl' log='mytest.log' bad='mytest.bad' data='mytest.csv'
apps/apps1234-->connecting to the instance with username and password
from the above command
mytest.log --this shows log files records
mytest.bad --this shows failed files records
step11:to check for the log or bad files of our file.write command as
ls -ltr.mytest*
it shows all the files with name as mytest.
step12:to see only log report use this command i.e
more mytest.log
This way we can load the data from flat file to temp tables using SQL Loader.
0 comments