Advanced replace query

[SirPoonga]

I am trying to convert data that was given to me in a text file to a
database. The text file was generated from a main frame system. Each
line is a record where fields started and ended at specific character.
I have splitted the data as needed.
As of now I treated all fields as text.

I have a field that should be converted to a numeric field. A number,
as text, looks like '00000012'. That's for positive numbers. For
negative, a -12 would look like '0000001r'. You might think, why use
r? I figured out how it determines negative. For the 1s digit, p=0,
q=1, r=2, ..., y=9. If you look at the ascii hex value you probably
can figure out why you start with p. P in hex is 50, y is 59. No
other letter has a hex of X0 where X is anything.

SO, I need to convert these values to numbers so I can do calculations
on them. I am wondering if there is a way to replace, say an r, with
the number 2, and then make the value negative?


Or is it possible to convert during import? Actually, it is. I had to
make my own import function to go through each line of hte text file
and pull out the field according to start and end character. So I need
to convert during import. One might think at this moment "why not just
use fixed width text import wizard". Because this file contains many
different record types that have different record layout. I created a
table that stores record layout info, so as each line of the file comes
in I check what record type it is. Then I query the record layout
table for fields, starts, and ends. Then I output the data into
appropiate tables for each record type.

i suppose I just answered my own question. However, is there a way to
do this after import?

 

[Sergey Poberezovskiy]

Sure there is:

You can create your own Public function in a Standard
Module and then use it in queries (update for instance):

Public Function ConvertNumber(ByVal inputText As String)
As Integer
Dim lastCharacter As String
Dim negativeNumber As Boolean
Select Case Right$(inputText, 1)
Case "r"
lastCharacter = "2"
negativeNumber = True
Case "q"
...
End Select
If negativeNumber Then
ConvertNumber = -Val(Left$(inputText, Len(inputText) -
1) & lastCharacter
Else
ConvertNumber = Val(inputText)
End If
End Function

Then in your update query you can just do the following:

Update myTable
Set myIntegerField = ConvertNumber(myTextField)

HTH

 

[SirPoonga]

So, I need to make another field as an integer to store the converted
value in. Or could I convert and store back in the same field. Then
after conversion change field type. Eitehr way should work.

 

[SirPoonga]

Instead of using a huge case statement I could do something like

DIM CONST P as INTEGER
DIM CONST Y as INTEGER
P=80
Y=89
....
If asc(lastCharacter)<=P and asc(lastCharacter)<=Y then
lastCharacter = Str(Val(lastCharacter)-P)
negativeNumber = True
end if
If negativeNumber Then
ConvertNumber = -Val(Left$(inputText, Len(inputText) -
1) & lastCharacter
Else
ConvertNumber = Val(inputText)
End If
End Function

 

[Dirk Goldgar]

I am trying to convert data that was given to me in a text file to a
database. The text file was generated from a main frame system. Each
line is a record where fields started and ended at specific character.
I have splitted the data as needed.
As of now I treated all fields as text.

I have a field that should be converted to a numeric field. A number,
as text, looks like '00000012'. That's for positive numbers. For
negative, a -12 would look like '0000001r'. You might think, why use
r? I figured out how it determines negative. For the 1s digit, p=0,
q=1, r=2, ..., y=9. If you look at the ascii hex value you probably
can figure out why you start with p. P in hex is 50, y is 59. No
other letter has a hex of X0 where X is anything.

SO, I need to convert these values to numbers so I can do calculations
on them. I am wondering if there is a way to replace, say an r, with
the number 2, and then make the value negative?


Or is it possible to convert during import? Actually, it is. I had
to make my own import function to go through each line of hte text
file and pull out the field according to start and end character. So
I need to convert during import. One might think at this moment "why
not just use fixed width text import wizard". Because this file
contains many different record types that have different record
layout. I created a table that stores record layout info, so as each
line of the file comes in I check what record type it is. Then I
query the record layout table for fields, starts, and ends. Then I
output the data into appropiate tables for each record type.

i suppose I just answered my own question. However, is there a way to
do this after import?

That looks like the old "zoned decimal" format, in which the last digit
has its high-order bits set to a value that indicates the sign. In your
example you used lower-case characters, but wasn't it really upper-case
characters; not "0000001r" but "0000001R" ? I threw together the
function below to convert these values based on the assumption that it's
upper-case characters indicating a negative sign. I can change it if my
guess is wrong.

'----- start of code -----
Function fncZonedToNumber(ZonedValue As Variant) As Variant

Dim strValue As String
Dim strLast As String

If IsNull(ZonedValue) Then
fncZonedToNumber = Null
ElseIf VarType(ZonedValue) <> vbString Then
fncZonedToNumber = CVErr(5) ' invalid argument
ElseIf Len(ZonedValue) = 0 Then
fncZonedToNumber = Null
Else
strLast = Right(ZonedValue, 1)
strValue = Left(ZonedValue, Len(ZonedValue) - 1)

If InStr(1, "0123456789", strLast, vbBinaryCompare) Then
strValue = strValue & strLast
ElseIf InStr(1, "PQRSTUVWXY", strLast, vbBinaryCompare) Then
strValue = "-" & strValue & Chr(Asc(strLast) - 32)
Else
fncZonedToNumber = CVErr(5) ' invalid argument
Exit Function
End If

fncZonedToNumber = Val(strValue)
End If

End Function
'----- end of code -----

Please note that the function hasn't been thoroughly tested.

 

[Sergey Poberezovskiy]

Or you can just combine multiple conditions within Case
statement:

Case "p" to "y"
...
Case Else
...

 

[Dirk Goldgar]

Or you can just combine multiple conditions within Case
statement:

Case "p" to "y"
...
Case Else
...

Watch out, though, because your code will not normally make any
distinction between upper- and lower-case letters (unless you set
Option Compare Binary for the module). And that could mess things up if
you do a numeric transformation of the ASCII value of the character.
That's why I decided to use InStr() in my version, with the
vbBinaryCompare option.

 

[Sergey Poberezovskiy]

Instead of using Right$(inputText, 1) and then compare to
range "p" to "y" one could use Asc(Right$(inputText, 1))
and compare to range 112 to 121, if they are concern about
case sensitivity - not as obvious when reading the code
though..

 

[Dirk Goldgar]

Instead of using Right$(inputText, 1) and then compare to
range "p" to "y" one could use Asc(Right$(inputText, 1))
and compare to range 112 to 121, if they are concern about
case sensitivity - not as obvious when reading the code
though..

True. Given the statement that the file came from a mainframe, I think
it's likely that it's one of the formats I'm familiar with, in which
case the the alphabetic character would be upper case. But you're quite
right, we don't really know, and the original post did use lower case
letters.

 

[SirPoonga]

Correction

If asc(lastCharacter)>=P and asc(lastCharacter)<=Y then
I'm looking to see if it is in range...

 

[SirPoonga]

It is lower case letters. I just realized I looked at the wrong ascii
values.

You are subtracting 32 because you want to go directly to the ascii
character for the number.
strValue = "-" & strValue & Chr(Asc(strLast) - 32)

I could also use what I wrote before, right?
p=112
strValue = "-" & strValue & Str(Asc(lastCharacter)-p)

This way if I do run across data from other sources that use uppercase
I just need to change the constant value. The computer will do the
figuring. No need for me to do any subtracting

Of course, I could do this too.
charp=112
char0=48
strValue = "-" & strValue & Chr(Asc(strLast) - (charp - char0))


Now, isn't my if statment doing the same thing as your? Just you are
doing a binary compare with an InStr and I am doing a range?

I add this into your function in the appropiate spots..
DIM CONST P as INTEGER
DIM CONST Y as INTEGER
P=112
....
If Asc(Right(ZonedValue, 1))>=P and Rsc(Right(ZonedValue, 1))<=(P+9)
then
ZonedValue= "-" & Left$(ZonedValue, Len(ZonedValue) - 1) &
Str(Asc(Right(ZonedValue, 1))-p)
end if
fncZonedToNumber = Val(ZonedValue)
....

That should get rid of the need for strValue and strLast.


I don't have to worry about null values. Though what you did is
probably more proper with the error code.

 

[SirPoonga]

btw, how did you get the preformatted code like that?

 

[Dirk Goldgar]

It is lower case letters.

Really? I'm surprised.

I just realized I looked at the wrong ascii
values.

You are subtracting 32 because you want to go directly to the ascii
character for the number.
strValue = "-" & strValue & Chr(Asc(strLast) - 32)

I could also use what I wrote before, right?
p=112
strValue = "-" & strValue & Str(Asc(lastCharacter)-p)

I don't think you want to subtract 112, I think you want to subtract the
difference between 112 and 48 -- Asc("0"). That difference is 64.

This way if I do run across data from other sources that use uppercase
I just need to change the constant value. The computer will do the
figuring. No need for me to do any subtracting

Of course, I could do this too.
charp=112
char0=48
strValue = "-" & strValue & Chr(Asc(strLast) - (charp - char0))
Yep.

Now, isn't my if statment doing the same thing as your? Just you are
doing a binary compare with an InStr and I am doing a range?

I'm not sure what you're doing at this point. Sure, you can do it with
a range, so long as you compare the numeric ASCII values, not the
(string) characters themselves.

I add this into your function in the appropiate spots..
DIM CONST P as INTEGER
DIM CONST Y as INTEGER
P=112
...
If Asc(Right(ZonedValue, 1))>=P and Rsc(Right(ZonedValue, 1))<=(P+9)
then
ZonedValue= "-" & Left$(ZonedValue, Len(ZonedValue) - 1) &
Str(Asc(Right(ZonedValue, 1))-p)
end if
fncZonedToNumber = Val(ZonedValue)
...

That should get rid of the need for strValue and strLast.

But why would you want to get rid of them? Calling the functions to
extract the substrings you want multiple times is bound to be less
efficient.

I don't have to worry about null values. Though what you did is
probably more proper with the error code.

Just trying to cover a reasonable number of the bases.

 

[Dirk Goldgar]

btw, how did you get the preformatted code like that?

I'm afraid I don't understand your question. Care to rephrase it?

 

[SirPoonga]

Your
'----- start of code -----
'----- end of code ------

It's fixed width in google groups display.

 

[SirPoonga]

I don't think you want to subtract 112, I think you want to subtract
the
difference between 112 and 48 -- Asc("0"). That difference is 64.

No no, that's not what is going on, look more carefully.
Dim char_p as Integer
char_p=112
If Asc(lastCharacter)>=char_p and Asc(lastCharacter)<=(char_p+9) then
strValue = "-" & strValue & Str(Asc(lastCharacter) - char_p)
End If

Let's say my character is 't'. 't' needs to end up as -4.

char_p=112
Asc('t') = 116

So

Asc('t') - char_p = 4

Therefore
Str(4) = '4'

Str is different than Chr. For Chr the argument is an ascii decimal
code, so it returns the ascii char. For Str the argument is a number,
it returns the string representation of that number. Str(112) would
result in the three character string '112', not 'p' which is what
Chr(112) would do.

Doing it this way I don't need to look up the fact that 'p' (112) is 64
away form '0' (48). Which is better incase I come across the need to
change to uppercase, then O just change char_p to 81.
extract the substrings you want multiple times is bound to be less
efficient.

You are right. That would slow it down.

 

[Dirk Goldgar]

the
difference between 112 and 48 -- Asc("0"). That difference is 64.

No no, that's not what is going on, look more carefully.
Dim char_p as Integer
char_p=112
If Asc(lastCharacter)>=char_p and Asc(lastCharacter)<=(char_p+9) then
strValue = "-" & strValue & Str(Asc(lastCharacter) - char_p)
End If

Let's say my character is 't'. 't' needs to end up as -4.

char_p=112
Asc('t') = 116

So

Asc('t') - char_p = 4

Therefore
Str(4) = '4'

Oh, I see what you're getting at. I completely overlooked the fact that
you were using Str(), not Asc(). But you'd better use CStr() instead --
Str() will give you a leading space on a positive number:

Str(4) --> ' 4', not '4'

 

[SirPoonga]

Cool, I noticed that, but it inserted into the field just fine.

Here's my final function, which also takes all leading 0s using the Val
function. I shouldn't run across nulls as numeric fields are 0 padded.
If I do it shouldn't cause a problem in calculations anyway.

Function convert_negint(thenum As String) As String
Const base = 112 'p ascii
Dim lastChr As String
lastChr = Right(thenum , 1)
If Asc(lastChr) >= base And Asc(lastChr) <= (base + 9) Then
thenum = "-" & CStr(Val(Left(thenum , Len(thenum ) - 1) &
CStr(Asc(lastChr) - base)))
End If
convert_negint = thenum
End Function

 

[Dirk Goldgar]

Your
'----- start of code -----
'----- end of code ------

It's fixed width in google groups display.

I didn't do anything special, just copied the code from the VB Editor
window and stuck on the "start" and "end" comments.