SQL*LOADER (text file loading) 1

[babsjoy]

I receive a text file from another site which contains negative values.
The problem is that the negative sign is in a fixed position in the file not floating. Also within a single record the field values can be either positive of negative.
I currently have a CTL that would load the file correctly if all the fields in the record are negative (see below) but without having the file changed by the other site (which is not an option at this time)how can I get these negative values loaded into a table?
One option that has been thought of is to load the negative value in a separate column in the table then manipulate the fields accordingly or use an external table. If the external table is the best option I have no knowledge of this, could you fill me in or give me a reference (web site or other)to do some research ? Thanks in advance ...

INPUT DATA EXAMPLE(3 records):

010464 A000052 - 00027.00 00150.00 00000.00 -00027.00
011521 B000112 - 00100.94 00200.00 00000.00 - 00100.94
022135 L000064 - 00004.33 00345.00 00000.00 - 00004.33


CTL Example(which currently loads the last field. This could load the first three fields if all fields in the record were negative but that can not be assumed)

LOAD DATA
INFILE '\\tsos\test\data\afms_current.dat'
TRUNCATE
INTO TABLE AFMS_CURRENT_TABLE
WHEN (58) != '-'
(CON POSITION(001:006)CHAR,
JON POSITION(012:018) CHAR,
TOT_SURCHARGE POSITION(060:067) DECIMAL EXTERNAL
)
INTO TABLE AFMS_CURRENT_TABLE
WHEN (58) = '-'
(CON POSITION(001:006) CHAR,
JON POSITION(012:018) CHAR,
TOT_SURCHARGE POSITION(060:067) DECIMAL EXTERNAL
"TOT_SURCHARGE/-1"
)

 

[ddrillich]

Did you try:
TOT_SURCHARGE POSITION(058:067) DECIMAL EXTERNAL

Meaning, to include the sign as part of the field including the space in between.

I'm saying it because -
select - 4 from dual;
returns -4. So, spaces are ignored in SQL.

Regards,
Dan

 

[babsjoy]

I don't think I have.. but wouldn't that just make things easy. Sometimes the simple answers are the best solutions. I'm just concerned that the "TOT_SURCHARGE/-1" won't work (due to the space)but I willing to give it a try. I'll have to get back to you. Possibly tomorrow.
Thanks!!!

 

[babsjoy]

I tried your suggestion and got the following Oracle error:
ORA-01722: invalid number

 

[ddrillich]

You are right, I also just got the invalid number error.
What worked for me was the following:

create table tst (tst NUMBER);

tst.ctl:

LOAD DATA
APPEND INTO TABLE tst
(
tst position(1:5) DECIMAL EXTERNAL "replacetst,' ','')"
)
and the data file had:
- 111

In your case, I would therefore try:

TOT_SURCHARGE POSITION(058:067) DECIMAL EXTERNAL "REPLACETOT_SURCHARGE,' ','')"


Good luck,
Dan

 

[babsjoy]

I tried the REPLACE command before but it did not work(got an error). My guess is that I had the wrong syntax. I was going to ask you about the REPLACE command this morning. I will give your example a try and thanks again !!!!