?
Solved

How to load csv file into Oracle using Sql Loader

Posted on 2008-11-19
5
Medium Priority
?
4,412 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:gudii9
5 Comments
 
LVL 20

Accepted Solution

by:
gatorvip earned 400 total points
ID: 22998969
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
 
LVL 6

Assisted Solution

by:Ritesh_Garg
Ritesh_Garg earned 400 total points
ID: 22999287
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
 
LVL 10

Assisted Solution

by:ravindran_eee
ravindran_eee earned 400 total points
ID: 22999965
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 400 total points
ID: 23002081
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 total points
ID: 23008315
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question