[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to load  data csv file into a table using sqlloader

Posted on 2009-04-16
3
Medium Priority
?
749 Views
Last Modified: 2013-12-18
Hi,

I have a empty table Table_A with structure without data within schema say Schema_A. I also have the data csv file  with 100k of data to be loaded into that table table_A. How can I achieve it using the sqloader approach. Any sample code, links, resources, ideas highly appreciated. Thanks in advance.
0
Comment
Question by:gudii9
3 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 800 total points
ID: 24163198
Hi gudii9,

SQL Loader works off flat files, a CSV is a delimited flat file ... so it is perfect for this.  A link the 'Utilities" manual is below - I suggest you have a look at it.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm#SUTIL003

lwadwell
0
 
LVL 18

Accepted Solution

by:
sventhan earned 1200 total points
ID: 24164165

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);

There is an input data file stored at \oraclexe\text\student.txt with the following records:

1,Steven,King,17-JUN-77,515.123.4567
2,Neena,Kochhar,21-SEP-79,515.123.4568
3,Lex,De Haan,13-JAN-83,515.123.4569
4,Alexander,Hunold,03-JAN-80,590.423.4567
5,Bruce,Ernst,21-MAY-81,590.423.4568
6,David,Austin,25-JUN-87,590.423.4569
7,Valli,Pataballa,05-FEB-88,590.423.4560
8,Diana,Lorentz,07-FEB-89,590.423.5567
9,Nancy,Greenberg,17-AUG-84,515.124.4569
10,Daniel,Faviet,16-AUG-84,515.124.4169
You can create a control file at \oraclexe\test\student.ctl as:
LOAD DATA
APPEND INTO TABLE STUDENT
FIELDS TERMINATED BY ','
(id, first_name, last_name, birth_date, social_number)

When you are ready to load data, you can run the SQL*Loader with the "sqlldr" command:

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlldr userid=hr/fyicenter,
control=\oraclexe\test\student.ctl,
data=\oraclexe\test\student.txt,
log=\oraclexe\test\student.log
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31571228
thank you
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 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