-
I am having a table with 100 columns.I have got a comma delimited variable length text file with 1 million records.Each
line in text file represents one new record
How to import this records in text file to Oracle table.I guess we can do thru sqlldr.Please do send the syntax & also I would like to know the syntax without using position.I mean the real shortcut way to get data from text file to Oracle table
-
Hi,
if your text file columns are in the same order as the table and the fields are dilimited by comma
then you can use the following SQL
spool this to a file
column column_id NOPRINT
SELECT column_id,column_name||','
FROM dba_tab_columns
WHERE table_name = <desired_table_name>
and owner = <owner>
ORDER BY column_id
/
Then copy the text file to a control file for the SQL*Loader script.
Soumya
still learning
-
There is a pretty good explanation of control file syntax in Oracle Complete Reference book. However, it doesn't give you any examples.
If you need a basic contol file example post a request here. I'll see if I can find one.
-
This sample to start sqlldr
sqlldr dwdba/dwdba control=/dw1/createdb/sql/loader/ctl/dwbnd_brand.ctl
direct=false errors=999999 log=/dw1/createdb/sql/loader/logs/dwbnd_brand.log discard=/dw1/createdb/sql/loader/logs/dwbnd_brand.discard
and control file where name is set in loader script above
LOAD DATA
INFILE '/dw1/createdb/sql/loader/data/dwbnd_brand.data'
BADFILE '/dw1/createdb/sql/loader/logs/dwbnd_brand.bad'
INTO TABLE dwdba.dwbnd_brand
APPEND
FIELDS TERMINATED BY ','
(brand_code CHAR(20) "RTRIM(:brand_code)" ,
brand_descr CHAR(60) "RTRIM(:brand_descr)",
active_flag
)
Cheers,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|