SEO

#1 (**permalink**) 02-24-2008, 09:35 AM

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

#2 (**permalink**) 02-24-2008, 09:35 AM

"Ralf Bender" <ralf.bender@arcor.de> wrote in message
news:41b87c71$0$16034$9b4e6d93@newsread4.arcor-online.net...
> 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

It might be choking on the commas due to NLS incompatibility

#3 (**permalink**) 02-24-2008, 09:35 AM

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

#4 (**permalink**) 02-24-2008, 09:36 AM

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@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
>
>

#5 (**permalink**) 02-24-2008, 09:36 AM

No, I set the NLS Options while starting the import with
NLS_NUMERIC_CHARACTERS=",."; export NLS_NUMERIC_CHARACTERS;

In some cases the positive numbers are loaded, but not the negative ones.

ana said the following on 09.12.2004 18:36:
> "Ralf Bender" <ralf.bender@arcor.de> wrote in message
> news:41b87c71$0$16034$9b4e6d93@newsread4.arcor-online.net...
>
>>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
>
>
> It might be choking on the commas due to NLS incompatibility
>
>

#6 (**permalink**) 02-24-2008, 09:36 AM

Hello all,

i got it. was a little tricky but it works.

if somebody interested send me a mail.

thanks

Ralf Bender said the following on 09.12.2004 17:25:
> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Q:SQL*Loader number format

**Q:SQL*Loader number format**