We help IT Professionals succeed at work.

How to SQL load a nagative number with currency

redds1
redds1 asked
on
I have the following csv file:
Amount
"$ 23,456,87.98"
$ 10.0
"$ 34,879,87.98"

and a table with number data type. I am unable to load this column into the table. Please help
Comment
Watch Question

Top Expert 2011

Commented:
- format/do a find-replace to remove the dollar sign. then you can use the datatype number with 2 precision eg: ALTER TABLE tablename MODIFY columname number(7,2)
Most Valuable Expert 2011
Top Expert 2012

Commented:
either use a string type so you can store the currency symbol.  Or drop the currency symbol,  and commas and use numeric type
awking00Information Technology Specialist

Commented:
to_number(trim(translate(amount,'1$,'1',)))
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>nagative number

I assume "negative".  Do the double quotes make it "negative"?

I assume sql loader?

Commented:
First Step:

Remove $ sign and comma sign from the price

Second step:

and change datatype to numeric(8,2)

Author

Commented:
I am trying to automate the process. Also, I forgot to mention that the number can be positive or negative e.g. $ -23,234,76.87 and so on. Is there a way to load them into a number table column?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
All,

I believe this is a SQL Loader issue.  This will handle the '$' and ',' just fine.  No need to 'replace' them.

Also a generic 'number' data type will handle the decimals.
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>Is there a way to load them into a number table column?

Using Sql Loader or what?

Please provide sample data and expected results.  If you have a sql loader control file already, please post it.
Top Expert 2011

Commented:
"I am trying to automate the process. Also, I forgot to mention that the number can be positive or negative e.g. $ -23,234,76.87 and so on. Is there a way to load them into a number table column"

- if you run this using sqlloader to load the data into oracle, just replace the $ and , sign then run the sqlloader again. make sure your datatype is as proposed earlier.
Most Valuable Expert 2011
Top Expert 2012
Commented:
assuming correctly formatted strings... try this

to_number(replace(x,' '),'L999,999,999.99')


however, your data doesn't appear to have good content

$ -23,234,76.87


note the 76  that group should have 3 digits

also,  the negative should be to the left of the dollar sign, alternatively,  remove the $


Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>just replace the $ and , sign then run the sqlloader again. make sure your datatype is as proposed earlier.

You do not need to replace anything.  SQL Loader will handle it just fine.

Author

Commented:
slightwv:this is how my control file is:

OPTIONS (SKIP=1)
LOAD DATA
INFILE test.csv'
TRUNCATE
INTO TABLE TS_test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
 (
  amt "TO_NUMBER(replace(:amt,' '),'L999,999,999.99')"
 )

The numbers are like -$ 13,746,063.00 and so on

and this amt is defined as number in the oracle database. I am getting an invalid number error....
awking00Information Technology Specialist

Commented:
In your control file -
...
INTO TABLE yourtable
(...,
 amount "to_number(trim(translate(:amount,'1$,'1',)))",
 ...
)
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Are the double quotes part of your data?

If so you need:
OPTIONALLY ENCLOSED BY '"'

If this still doesn't work, please provide sample data that 'fails'
awking00Information Technology Specialist

Commented:
Typos, try again -
 amount "to_number(trim(translate(:amount,'1$','1')))"

Author

Commented:
thanks slightwv and sdstuber, it worked.
awking00Information Technology Specialist

Commented:
Still got it wrong -
"to_number(trim(translate('$ -123,456,789.01','1$,','1')))"