How do I change a charactor in a string to another charactor

[Pharmer]

Greetings

I have a project where I import large ASCII files with many fields, 1 of which is an account number. The application which generates the ASCII file allows some duplicate named records with the same acct number, replacing the last charactor with a letter based on the its value (A=0, B=1, etc.). The value imports as a 8 space Alpha-numeric field (the first seven digits are the number + the last is the letter code). In order properly use my data, I need to convert the letters back to their number counterparts. I saw (and used) a script for eliminating hyphens from dates which was going use as a template, but I can't find it.

Please Help

Doug

 

[footpad]

While this seems a relatively straightforward task, there may be some subleties to consider before writing the code. Without more specifics about the base number and the range of letters involved, it's hard to give you the exact script you're looking for.

For example, you mentioned the extra characters were based on numeric values, e.g. A=0, B=1, and so on. Extrapolating, this means you might have values like:

   100A
   100B
   100M
   200A
   200B
   200C

And so on. There are a few different ways you could translate these values, as shown in the following table:

   100A    1000    1000    100
   100B    1001    1001    102
   100M    1012    10012   112
   200A    2000    2000    200
   200B    2001    2001    201
   200C    2002    2002    202

Now, I'm guessing you're looking for results like those shown in the second column. The following script does that:

method run(var eventInfo Event)
var
   tc         TCursor
   si         SmallInt
   ch         String
   str        String
   strAcctNo  String
endVar

   errorTrapOnWarnings( True )

   if not tc.open( "Accounts" ) then
      errorShow( "Oops" )
   else
      tc.edit()
      scan tc :

         strAcctNo = tc."AcctNo"
         if strAcctNo.match( "..@", str, ch ) then
            si = smallInt( str ) * 10
            si = si + ansiCode( ch ) - 65
            strAcctNo = string( si )
         endIf

         tc."AcctNo" = strAcctNo
         tc.unlockRecord()

      endScan

      if tc.isAssigned() then
         tc.Close()
      endIf
   endIf

endMethod

You will likely need to modify things to match your needs. For example, this script replies on the ANSI sort order of the trailing character. So, it will delineate between upper and lower case characters. That is, "A" is returns 0 while "a" returns 32. If this isn't what you want, then you'll want to convert ch to upper case before adding its AnsiCode value to the si variable.

To do this, modify the calculation code to something like this:

            si = smallInt( str ) * 10
            si = si + ansiCode( ch.upper() ) - 65
            strAcctNo = string( si )

Also, you'll note that I multiple the base number (the left hand side of the string) by 10. This may or may not be what you want.

Finally, see if your Account Number field is the keyed field in the table. If so, you do *not* want to use a scan loop, as records may fly-away when modified and this will throw off the calculations of the SCAN loop.

If Account No is the keyed field, then you should probably remove the key for this process and then reapply it after updating the account numbers.

Finally, if you have detail records that use these account numbers as linking values, you should probably create a conversion table, one that maintains the original account numbers and the newly generated ones. It's a simple enough process;

1. Use a check query to create a table containing all current account numbers.

2. Rename the Answer table from the query so it will be retained in case Paradox is exited.

3. Restructure the renamed Answer table and add a field for the new Account Numbers.

4. Use a script similar to the one shown above to generate the new account numbers. Tweak the script until you're happy with the new values.

5. Use CHANGETO QBE queries or UPDATE SQL queries to replace the old account numbers with the new ones throughout your database.

As you can see, this seems like a simple process, but it really requires some careful thought, planning, and execution. As always, you should maintain a series of backups while doing this, as you don't want to lost any data or connections.

Hope this helps...

-- Lance