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?
 
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

0
 
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
0
 
ravindran_eeeCommented:
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
0
 
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.