| sql*loader [message #450306] |
Tue, 06 April 2010 05:37  |
dbagayatri
Messages: 7
Registered: April 2010
Location: Banglore
|
Junior Member |
|
|
I Have one csv file.i want to load to a table trough sql*loader.but in table 3 column is there.but in the csv file some record hav one semicolumn in last filed like this
1234;"hogit";78887;89
4567;"rtef";12565;89
how can we load...
|
|
|
|
| Re: sql*loader [message #450310 is a reply to message #450306] |
Tue, 06 April 2010 05:59  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Simply omit the fourth "column" from the CSV file. Something like this:SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
COL VARCHAR2(20)
NUMB NUMBER
Control file:load data
infile *
replace
into table test
fields terminated by ';'
optionally enclosed by '"'
(id,
col,
numb
)
begindata
1234;"hogit";78887;89
4567;"rtef";12565;89
Loading and the result:SQL> $sqlldr scott/Tiger@ora10 control=test3.ctl log=test3.log
SQL*Loader: Release 10.2.0.1.0 - Production on Uto Tra 6 12:58:00 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
SQL> select * from test;
ID COL NUMB
---------- -------------------- ----------
1234 hogit 78887
4567 rtef 12565[Updated on: Tue, 06 April 2010 06:00] Report message to a moderator |
|
|
|
| Re: sql*loader [message #450320 is a reply to message #450310] |
Tue, 06 April 2010 07:39 |
dbagayatri
Messages: 7
Registered: April 2010
Location: Banglore
|
Junior Member |
|
|
no,actually i want to load the last data also in the 3rd column.but semocolon is there..how can i omit the ; while loading..
i want to load like this..
ID COL NUMB
---------- -------------------- ----------
1234 hogit 7888789
4567 rtef 1256589
|
|
|
|
| Re: sql*loader [message #450323 is a reply to message #450306] |
Tue, 06 April 2010 07:54 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well the simplest solution would probably be to fix this at source - i.e. get the csv regenerated without the extra ;
Failing that I'd use an external table instead of sqlloader.
Define the external table with 4 columns. Then concatenate columns 3 and 4 to get the result you want.
|
|
|
|
|
|
| Re: sql*loader [message #450386 is a reply to message #450335] |
Tue, 06 April 2010 19:22 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In addition to what joy_division posted, you will need to use BOUNDFILLER and TRAILING NULLCOLS, as demonstrated below.
-- test.ctl:
load data
infile *
replace
into table test
fields terminated by ';'
optionally enclosed by '"'
TRAILING NULLCOLS
(col1,
col2,
col3 ":col3 || :col4",
col4 BOUNDFILLER)
begindata
1234;"hogit";78887;89
4567;"rtef";12565;89
-- table, load, and results:
SCOTT@orcl_11g> CREATE TABLE test
2 (col1 NUMBER,
3 col2 VARCHAR2(15),
4 col3 VARCHAR2(15))
5 /
Table created.
SCOTT@orcl_11g> $sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11g> select * from test
2 /
COL1 COL2 COL3
---------- --------------- ---------------
1234 hogit 7888789
4567 rtef 1256589
SCOTT@orcl_11g>
[Updated on: Tue, 06 April 2010 19:26] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
