Home » RDBMS Server » Server Utilities » SQL Loader Number Format (11.2.0.2.0)
SQL Loader Number Format [message #520976] Thu, 25 August 2011 12:36 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
use text editor to eliminate "USD" prior to loading
Re: SQL Loader Number Format [message #520981 is a reply to message #520978] Thu, 25 August 2011 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
test_value "TO_NUMBER (substr(:test_value, 1, length(:test_value)-4), '999G999G999','NLS_NUMERIC_CHARACTERS=''.,''')"

Regards
Michel

Re: SQL Loader Number Format [message #520982 is a reply to message #520976] Thu, 25 August 2011 13:17 Go to previous messageGo to next message
Mdubois
Messages: 3
Registered: August 2011
Junior Member
Michel,

Thank you for your quick reply, your solution works perfectly.
Re: SQL Loader Number Format [message #521011 is a reply to message #520982] Thu, 25 August 2011 15:13 Go to previous message
Barbara Boehmer
Messages: 7861
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>

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


Current Time: Thu Apr 24 22:37:15 CDT 2014

Total time taken to generate the page: 0.06199 seconds