Re: Q:SQL*Loader number format

From: Ralf Bender <ralf.bender_at_arcor.de>
Date: Fri, 10 Dec 2004 08:01:22 +0100
Message-ID: <41b949c1$0$29832$9b4e6d93@newsread2.arcor-online.net>

Hi

sure, here it is:
LOAD DATA
    INFILE BSET.DAT
    APPEND
INTO TABLE BSET
    FIELDS TERMINATED BY "#"
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (

    MANDT NULLIF MANDT=BLANKS ,
    BUKRS NULLIF BUKRS=BLANKS ,
    BELNR NULLIF BELNR=BLANKS ,
    GJAHR NULLIF GJAHR=BLANKS ,
    BUZEI NULLIF BUZEI=BLANKS ,
    MWSKZ NULLIF MWSKZ=BLANKS ,
    HKONT NULLIF HKONT=BLANKS ,
    TXGRP NULLIF TXGRP=BLANKS ,
    SHKZG NULLIF SHKZG=BLANKS ,

    HWBAS "TO_NUMBER(:HWBAS,'99999999999999D99MI')",

    FWBAS NULLIF FWBAS=BLANKS ,
    HWSTE NULLIF HWSTE=BLANKS ,
    FWSTE NULLIF FWSTE=BLANKS ,
    KTOSL NULLIF KTOSL=BLANKS ,
    KNUMH NULLIF KNUMH=BLANKS

    )

zlmei_at_hotmail.com said the following on 09.12.2004 19:05:

> Hi,
> 
> Could you post your sql-loader control file?
> 
> Guang
> 
> Ralf Bender wrote:
> 

>>Hello
>>
>>I've got a big flat file with # as separator.
>>There is a number field which has negative sign at the end or nothing

> 
> if
> 

>>it is a positive number:
>>
>>D#100,00#100,00#BET
>>S#100,00-#100,00-#WISH
>>
>>In the database the field is a number(15,2)
>>
>>I try to load this file with sql-loader, but earn always errors like
>>invalid number ...
>>
>>I try (seen at ask tom)
>>column_name "to_number( :column_name, '9999999999s' )/100",
>>or
>>column_name "to_number( :column_name, '99999999D99MI' )",
>>
>>nothing works correctly. In some cases i got the positive numbers but

> 
> 

>>not the negative ones.
>>
>>Is there someone who can help me to climb this little rock?
>>
>>thanks
>>ralf

>
> Received on Fri Dec 10 2004 - 01:01:22 CST