OraFAQ Forum: Server Utilities » SQL Loader Number Format

Home » RDBMS Server » Server Utilities » SQL Loader Number Format (11.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader Number Format [message #520976]

Thu, 25 August 2011 12:36

Mdubois
Messages: 3
Registered: August 2011

Junior Member

Hi,

I have a small problem when I am trying to load data into a table using SQL Loader. The data I am trying to load should be a number, but it is in the format '999,999,999 USD'. When I try to load the data, I am getting an invalid number error, due to the USD (I have already accounted for the thousands seperators). My question is, how can I load the data as a number with USD in the format?

Sample Data(testfile.dat):

sample1, "342,2343,543 USD"
sample2, "564,324,465 USD"
sample3, "534,753,213 USD"
..etc..

Sample CTL File:

LOAD DATA
INFILE 'C:\testfile.dat
REPLACE
INTO TABLE test_table
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
test_name,
test_value "TO_NUMBER (:test_value, '999G999G999','NLS_NUMERIC_CHARACTERS=''.,''')"
)

EDIT: Sorry for the triple post, my browser hung up so I didn't know it posted Confused

[Updated on: Thu, 25 August 2011 12:38]

Report message to a moderator

Re: SQL Loader Number Format [message #520978 is a reply to message #520976]

Thu, 25 August 2011 12:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use text editor to eliminate "USD" prior to loading

Report message to a moderator

Re: SQL Loader Number Format [message #520981 is a reply to message #520978]

Thu, 25 August 2011 12:50

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

test_value "TO_NUMBER (substr(:test_value, 1, length(:test_value)-4), '999G999G999','NLS_NUMERIC_CHARACTERS=''.,''')"

Regards
Michel

Report message to a moderator

Re: SQL Loader Number Format [message #520982 is a reply to message #520976]

Thu, 25 August 2011 13:17

Mdubois
Messages: 3
Registered: August 2011

Junior Member

Michel,

Thank you for your quick reply, your solution works perfectly.

Report message to a moderator

Re: SQL Loader Number Format [message #521011 is a reply to message #520982]

Thu, 25 August 2011 15:13

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

Or:

test_value "TO_NUMBER (REPLACE (:test_value, ' ', ''), '999G999G999C', 'NLS_NUMERIC_CHARACTERS=''.,'' NLS_ISO_CURRENCY=''AMERICA''')"

as shown below:

-- testfile.dat:

sample1, "342,2343,543 USD"
sample2, "564,324,465 USD"
sample3, "534,753,213 USD"

-- test.ctl:

LOAD DATA
INFILE testfile.dat
REPLACE
INTO TABLE test_table
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
test_name,
test_value "TO_NUMBER (REPLACE (:test_value, ' ', ''), '999G999G999C',
'NLS_NUMERIC_CHARACTERS=''.,'' NLS_ISO_CURRENCY=''AMERICA''')"
)

SCOTT@orcl_11gR2> create table test_table
  2    (test_name   varchar2 (9),
  3  	test_value  number)
  4  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from test_table
  2  /

TEST_NAME TEST_VALUE
--------- ----------
sample2    564324465
sample3    534753213

2 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Previous Topic:	Audit Select..
Next Topic:	datapump imp full with network_link

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 13:41:59 CST 2025