Import Signed Numeric data

[ZSauce]

I need to import a text file into Access. One of the fields contained in the text file is in Signed Numeric format, which I need to convert to a numeric value in Access.

For example, 6705N in the text file should convert to -670.55 in Access.

 

[Joe4]

What kind of file text file is it (fixed width, CSV, etc)?
If it is fixed width how does the data line-up (i.e. what does a positive value look like and where does it end versus where does the N in the negative value end)?

I am thinking that you want to import the data "as is", and run an Action Query to correct the data. Depending on how things line up, you may be able to import the sign indicator into its own field. If not, then import the whole field as Text, then perform a calculation on it (If the last character is N, take 0 - the value of the Text, less the last position, else take the value of the Text).

 

[ZSauce]

The text file is fixed width.

It appears to me that only negative values are affected by Signed Numeric.

Here is an example of a positive value:

0000000030282

Here are two examples of negative values:

000000013224}
000000014318M

You may peruse the column entitled "Negtaive Values:" at this link to get a handle on what I have: http://www.ucop.edu/irc/campus_specs/cgx/signed_num.html.

 

[Joe4]

I misunderstood a little. I thought the "N" stood for negative, when actually it represents a number and a sign. Looks like you are working with data that might have come from a Unix or MainFrame system that uses Binary or Hex characters (what a pain!).

If you search the net, you may be able to find some sort of conversion utility or add-in. Seems like someone would have had this problem before.

If not, I think you will need to make a conversion table in Access (similar to the reference table you linked to). Then import this number into two fields -- the signed numeric character, and everything else. Then, using your conversion table, you can create some calculated fields ina query that will convert the signed numeric value to a number, combine with the other field (rest of number) and apply the appropriate sign.

 

[ZSauce]

Here's a user-defined function I used to import signed numeric data. (A coworker provided it, and it worked beautifully.)

Function Signed_Num(theAmt As Variant) As Double
Dim thelast As String
Dim theFrnt As String

thelast = Right(RTrim(LTrim(theAmt)), 1)
theFrnt = Mid$(theAmt, 1, Len(theAmt) - 1)

If thelast = "{" Or thelast = "0" Then
thelast = "0"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "A" Or thelast = "1" Then
thelast = "1"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "B" Or thelast = "2" Then
thelast = "2"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "C" Or thelast = "3" Then
thelast = "3"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "D" Or thelast = "4" Then
thelast = "4"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "E" Or thelast = "5" Then
thelast = "5"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "F" Or thelast = "6" Then
thelast = "6"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "G" Or thelast = "7" Then
thelast = "7"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "H" Or thelast = "8" Then
thelast = "8"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "I" Or thelast = "9" Then
thelast = "9"
Signed_Num = Val(theFrnt & thelast) / 100

'==========================================================

ElseIf thelast = "}" Then
thelast = "0"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "J" Or thelast = "1" Then
thelast = "1"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "K" Or thelast = "2" Then
thelast = "2"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "L" Or thelast = "3" Then
thelast = "3"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "M" Or thelast = "4" Then
thelast = "4"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "N" Or thelast = "5" Then
thelast = "5"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "O" Or thelast = "6" Then
thelast = "6"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "P" Or thelast = "7" Then
thelast = "7"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "Q" Or thelast = "8" Then
thelast = "8"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "R" Or thelast = "9" Then
thelast = "9"
Signed_Num = Val(theFrnt & thelast) / -100

End If

End Function