We’re having trouble importing certain text files into SQL Server using DTS because numbers are stored in an unusual way. For example:
A positive integer is OK. For example. the number 152 is stored as 152 in the text files.
The decimal point is not included. For example, 152.79 is stored as 15279.
A negative number is stored with a special symbol instead of the final digit. For example, -152 is stored as 15K & -152.79 is stored as 1527R. These cannot be imported into a numeric field.
Here’s the characters used for the final digit to signify a negative number.
} 0
J 1
K 2
L 3
M 4
N 5
O 6
P 7
Q 8
R 9
We need to fix any field that contains a negative sign. The data files contain hundreds of fields that must be checked. Ideally, we would create a custom function to do this. However, we do not know how to do this.
Here is how the function would be called. In this example, the custom function is named Fixnegative.
OutputNumber = Fixnegative(InputText)
The parameter named InputText is the text value to be checked & fixed. The output of the function is an integer. When the last character of the field is numeric, no change is required. When the last character of the field is one of the special characters listed above, then the result must include the correct final digit and be expressed as a negative number.
For example:
Fixnegative(152) returns 152
Fixnegative(15279) returns 15279
Fixnegative(15K) returns 152-
Fixnegative(1527R) returns 15279-
If we need to include a decimal position, we could do so as follows:
Fixnegative(15279) /100 returns 152.79
Fixnegative(1527R) / 100 returns 152.79-
I don’t think that it matters whether the negative sign is leading or trailing, as long as SQL Server undertands that the number is negative.
Thanks
