How to load csv file into Oracle using Sql Loader

I have few csv files with table data.I would like to export into oracle 10g using sqlloader.can you please tell me step by step procedure to do it including writing .ctl file and dmp(dump)file.Any sample code,resources,links highly appreciated.Thanks in advance
LVL 7
gudii9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gatorvipCommented:
The steps are fairly simple:
- identify the structure of the CSV files
- create similar structure in your Oracle table(s)
- run SQL Loader  to load data from CSV to Oracle

Excellent FAQ here: http://www.orafaq.com/wiki/SQL*Loader_FAQ

There are many examples on the web:
http://www.cs.jhu.edu/~yarowsky/sqlloader.txt
http://oreilly.com/catalog/orsqlloader/chapter/ch01.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ritesh_GargCommented:
1. Create a control file with extension .ctl
E.g.
LOAD DATA
TRUNCATE
INTO TABLE test_table
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(CUSTomer_name,
BATCH_NAME,
LINE_TYPE      "upper(:LINE_Type)",
DESCRIPTION    "ltrim(rtrim(:DESCRIPTION))"
)

2. Use the command:
sqlldr userid=scott/tiger@dev control=file.ctl   data=data.csv
Ravindran GopinathanCommented:
Another option will be to make use of External Tables.. This is a newly introduced feature in Oracle 10g. The steps are,

-> Create a directory (where the CSV file is present)
-> Create an external table pointing to the CSV file
-> Create a normal table and populate data from normal table
-> Drop external table

The below links provides lot more details and samples

http://www.orafaq.com/node/848

Thanks,
Ravi
schwertnerCommented:
External Tables are read-only tables, so you can use them
to solve your problem.
create directory med as 'c:\med' ; -- on main 
GRANT READ  ON DIRECTORY med TO lsmedical;
GRANT WRITE ON DIRECTORY med TO lsmedical;
 
 
 
drop table lsmedical.USERMEDBASIC_EXT;
create table lsmedical.USERMEDBASIC_EXT ( 
                   PZN  VARCHAR2(33),
                   NAME VARCHAR2(111)   )
    organization external  
    (type oracle_loader  
     default directory med 
     access parameters (records delimited by newline 
     fields terminated by ',') 
     location ('med-de-formatted.txt','med-de-insulin-formatted.txt',
               'med-us-formatted.txt','med-us-insulin-formatted.txt')); 

Open in new window

awking00Information Technology SpecialistCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.