OraFAQ Forum: SQL & PL/SQL » Number Format with External Table

Home » SQL & PL/SQL » SQL & PL/SQL » Number Format with External Table

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

Number Format with External Table [message #157146]

Tue, 31 January 2006 09:00

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Hello,

i am receiving data via an .csv file and using an external table to load the data.

CREATE TABLE ext_tab_test1
(
  DEALERCODE NUMBER,
  DEALERNAME VARCHAR2(150),
  SUB_USD VARCHAR2(25),
  RED_USD VARCHAR2(25)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY ETL_FOLDER2
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
    BADFILE 'import_us_trans.bad'
    LOGFILE 'import_us_trans.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      DEALERCODE CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      DEALERNAME CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      SUB_USD CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      RED_USD CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'abc.csv'
  )
)REJECT LIMIT UNLIMITED

The Data in the CSV File looks like:

DEALERCODE DEALERNAME                                                                       SUB_USD                   RED_USD
---------- -------------------------------------------------------------------------------- ------------------------- -------------------------
         1 ABC                                                                              $0.00                     $14,806.67
         9 BDF                                                                              $0.00                     $0.00
         9 GHI                                                                              $0.00                     $12,198.35
        10 JKL                                                                              -$66,421.58               $32,642.13
        10 MNO                                                                              $0.00                     -$2,996.95
        10 PQR                                                                              -$60,621.14               $0.00
        10 STU                                                                              -$1,642.23                $0.00

1.) convert the SUB_USD and RED_USD to a number format like: 999,999,999,999.00 (without the the '$')

I tried this statement, but getting the below error:

SQL> select to_number(trim('$' from sub_usd),'999,999,999.00') from ext_tab_test1 where rownum<15;

select to_number(trim('$' from sub_usd),'999,999,999.00') from ext_tab_test1 where rownum<15

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01722: invalid number
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

2.) What is a good way to do this transformation? Is there any way to do it allready by loading via the external table?

thanks in advance!

Report message to a moderator

Re: Number Format with External Table [message #157152 is a reply to message #157146]

Tue, 31 January 2006 10:53

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

I've found some examples with the REPLACE Function. I'd be interested, whether that's a good way to convert the columns (using it with a procedure which insert the external table into an internal table)

select replace(replace(replace(replace(red_usd,'$',''),'-$',''),'(',''),')','') REPLACED from ext_tab_test1;

thanks for any comments...

Report message to a moderator

Re: Number Format with External Table [message #157171 is a reply to message #157152]

Tue, 31 January 2006 13:24

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

Senior Member

You need to use a delimiter that is not present in your data. Since you are using commas for your field delimiters, then any number with commas in it, will be read as separate fields. Another alternative is to use a fixed-length format, instead of delimited Either way, you can then use your replaces to remove the dollar signs and commas. Alternatively, you can use SQL*Loader instead of an external table and include your replaces in your control file, but you will still need either a different delimiter or fixed-length.

Report message to a moderator

Re: Number Format with External Table [message #157188 is a reply to message #157171]

Tue, 31 January 2006 20:02

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I use EOTs in CSV format with commas embedded in the text all the time. I suspect the problem is that the SUB_USD column in the data file is not enclosed in quotes.

Oracle handles embedded commas, provided the field is enclosed in quotes (or your chosen enclose char). It also handles embedded quotes provided they are doubled. eg. If you want to load a field with the following value:

Quote:

This field contains "quotes".

Then it must appear in the data file as:

Quote:

"This field contains ""quotes""."

The only exception to the CSV pseudo-standard is that Oracle does not seem to be able to handle embedded line-feeds within a string.

_____________
Ross Leishman

Report message to a moderator

Re: Number Format with External Table [message #157228 is a reply to message #157171]

Wed, 01 February 2006 03:17

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Hi,

Barbara, you said:

Quote:

You need to use a delimiter that is not present in your data. Since you are using commas for your field delimiters, then any number with commas in it, will be read as separate fields.

Well, that actually makes sense, but it is working with my REPLACE Statement. So i can convert the data from:

SQL> select dealercode,dealername,sub_usd,red_usd from ext_tab_test1 where rownum<15;

DEALERCODE DEALERNAME                                                                       SUB_USD                   RED_USD
---------- -------------------------------------------------------------------------------- ------------------------- -------------------------
         1 ABC                                                                              $0.00                     $14,806.67
         9 BDF                                                                              $0.00                     $0.00
         9 GHI                                                                              $0.00                     $12,198.35
        10 JKL                                                                              -$66,421.58               $32,642.13
        10 MNO                                                                              $0.00                     -$2,996.95
        10 PQR                                                                              -$60,621.14               $0.00
        10 STU                                                                              -$1,642.23                $0.00

to the format i need for the transformation to the number-Statement (thats why i have to remove the '$', and '('):

QL> select dealercode,dealername,replace(replace(replace(replace(sub_usd,'$',''),'-$','-'),'(',''),')','') REPLACED_SUB_USD,replace(replace(replace(replace(red_usd,'$',''),'-$','-'),'(',''),')','') REPLACED_RED_USD from ext_tab_test1;

DEALERCODE DEALERNAME                                                                       REPLACED_SUB_USD          REPLACED_RED_USD
---------- -------------------------------------------------------------------------------- ------------------------- -------------------------
         1 ABC                                                                              0.00                      14,806.67
         9 BDF                                                                              0.00                      0.00
         9 GHI                                                                              0.00                      12,198.35
        10 JKL                                                                              -66,421.58                32,642.13
        10 MNO                                                                              0.00                      -2,996.95
        10 PQR                                                                              -60,621.14                0.00
        10 STU                                                                              -1,642.23                 0.00

Does somebody have an explanation for that? Is it because of the "TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'," Statement within my External Loading Process?

Report message to a moderator

Re: Number Format with External Table [message #157356 is a reply to message #157228]

Wed, 01 February 2006 22:00

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

Senior Member

I originally missed the fact that you are using:

OPTIONALLY ENCLOSED BY '"'

which, as Ross already correctly pointed out, prevents the commas within the double quote from being mistaken for field delimiters, so your data loads into the correct fields.

To remove the dollars signs and commas from the numbers, your replace statements are about all you can do with an external table. If you were using SQL*Loader instead of an exeternal table, you could include the replace statements in your SQL*Loader control file instead of using them when selecting from the external table.

Report message to a moderator

Re: Number Format with External Table [message #157388 is a reply to message #157146]

Thu, 02 February 2006 02:04

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Thanks Ross and Barbara!

@Barbara: Because my company is using 8i and i would like to show new Features from 9i i will keep using the external tables.

Report message to a moderator

Previous Topic:	Deletion by CTAS
Next Topic:	Full table scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 21:11:51 CDT 2024