Home » RDBMS Server » Server Utilities » sql*loader
sql*loader [message #450306] Tue, 06 April 2010 05:37 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21670
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #450335 is a reply to message #450320] Tue, 06 April 2010 08:36 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Would this work in your control file:
.
.
.
col3 ":col3 || :col4",
.
.
.
Re: sql*loader [message #450386 is a reply to message #450335] Tue, 06 April 2010 19:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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

Re: sql*loader [message #450449 is a reply to message #450386] Wed, 07 April 2010 05:27 Go to previous message
dbagayatri
Messages: 7
Registered: April 2010
Location: Banglore
Junior Member
Thanks
Barbara ...

Previous Topic: load / unload csv file
Next Topic: Export dump of large table
Goto Forum:
  


Current Time: Tue May 11 20:58:10 CDT 2021