[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBS substring math

Posted on 2011-10-10
16
Medium Priority
?
357 Views
Last Modified: 2012-05-12
I have a file with one of the records equaling "SE*1318*0001".  I need to reconstruct this as "SE*1317*0001" by subtracting 1 from the substring in position 4 through 7.  The value 1318 will vary from case to case, so the solution needs to be  generalized.
0
Comment
Question by:tim44202
  • 5
  • 3
  • 3
  • +2
16 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 36946267
StrOutput = Left$(Mystring, 1, 3) & Mid$(MyString(4, 7) + 1 & Mid$(MyStrig(9)
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 36946272
Sorry but the second part should be -1 and not +1.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 36946277
:(  and the last part should be Mid$(MyString(8)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Bill Prew
ID: 36946285
So you only want to change the lines that start with "SE", all others just get written back out?

~bp
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36946433
Okay, if my assumption is correct, here's a VBS script that will do the job.  Save as a VBS, and then run with either one parm, or two.  If just one parm, the filename passed will be processed and overwritten with the changes.  If two files passed the first file will be read and the changes written to the second filename (good for testing).

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
 
' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Loop through all lines of the file, adjust values on "SE" lines
For i = 0 To UBound(arrLines)
    If arrLines(i) <> "" Then
        If Left(arrLines(i), 2) = "SE" Then
            arrFields = Split(arrLines(i), "*")
            arrFields(1) = CLng(arrFields(1)) - CLng(arrFields(2))
            arrLines(i) = Join(arrFields, "*")
        End If
    End If
Next

' Rewrite file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write(Join(arrLines, vbCrLf))
objFile.Close

Open in new window

~bp
0
 

Author Comment

by:tim44202
ID: 36946707
did not solve the problem.

here is complete code which works fine other than the decrementing logic in lines 80 thru 82

    option explicit
    on error resume next
    dim objFSO       'as FileSystemObject
    dim fle1      'as file
    dim fle2      'as file
    dim strPath      'as string
    dim strFldr      'as string
    dim strLine      'as string
    strPath = "C:\x12 Test Files\4010_MACSIS_NonMCD_123303.txt"       'Put in the file you want to edit
    strFldr = "C:\x12 Test Files\OUT\5010_MACSIS_NonMCD_123303.txt"
    Main 'This Calls the Main sub
    sub Main()
    dim rtn 'as integer
          rtn = CopyStuff() 'This calls and runs the CopyStuff function
    if rtn = 1 then
          msgbox "Copy is complete"
    else
          msgbox "An error was found and the process was aborted. " & Cstr(rtn)
                'The & Cstr(rtn) will display the number returned by CopyStuff
                'After you've got your script running, you may want to remove this feature
    end if
    'Cleanup
    if not fle1 is nothing then set fle1 = nothing
    if not fle2 is nothing then set fle2 = nothing
    if not objFSO is nothing then set objFSO = nothing
    end sub
    function CopyStuff()
    set objFSO = CreateObject("Scripting.FileSystemObject") 'This creates the FSO
          'I've included error handling after each step
          if err.number <> 0 then
                msgbox "Error in Creating Object: " & err.number & "; " & err.description
                CopyStuff = 0 'Returns this number
                exit function 'Stop processing, go back to Main
          end if
    if not objFSO.FileExists(strPath) then 'The file to copy is not present
          msgbox "The " & strPath & " file was not found on this computer"
          CopyStuff = 2
          exit function
    end if
    if objFSO.FileExists(strFldr) then
          objFSO.DeleteFile(strFldr) 'If the temp file is found, delete it
    end if
          set fle1 = objFSO.OpenTextFile(strPath) 'Open
                if err.number <> 0 then       
                      msgbox "Error opening " & strPath & ": " & err.number & "; " & err.description
                      CopyStuff = 3
                      exit function
                end if
          set fle2 = objFSO.CreateTextFile(strFldr) 'Create the temp file
                if err.number <> 0 then       
                      msgbox "Error creating temp ini: " & err.number & "; " & err.description
                      CopyStuff = 4
                      exit function
                end If
            '****************************************************************************
          'Here's the work horse that does the copying
            '
            'Here is the replacement Logic
            '
            '****************************************************************************

          Do while not fle1.AtEndofStream 'Change this line, Change this one too
                strLine = fle1.ReadLine
                  strLine = Replace(strLine,"*U*00401*","*U*00501*")
                  strLine = Replace(strLine,"*X*004010X098A1","*X*005010X222*")
                  strLine = Replace(strLine,"ST*837*0001","ST*837*0001*005010222")
                  strLine = Replace(strLine,"34-0714441","340714441")
            if Left(strLine, 6) <> "REF*87" then
'*******************************************************************************************************************
'*          
'*            ****      Here is where I need the logic to decrement by 1 value in string position 4 to 7 and reconstuct the string
'*                  
'*                  if left(strLine,3) = "SE*" then
'*                        strLine = Left(strLine, 1, 3) & Mid$(strLine(4, 47) -1 & right(strline,5)
'*                  end if
'*
'*            the compile errors on the $
'*
'*******************************************************************************************************************
                  if left(strLine,3) = "SE*" then
                  strLine = Left(strLine, 1, 3) & Mid$(strLine(4, 47) -1 & right(strline,5)
                  end if
            fle2.WriteLine  strLine
            end if
          loop
          if err.number <> 0 then
                msgbox "Error transfering data: " & err.number & "; " & err.description
                CopyStuff = 5
                fle1.close
                fle2.close
                exit function
          end if
          
          fle1.close
           set fle1 = nothing
          fle2.close
           set fle2 = nothing
          
          
          if err.number <> 0 then
                msgbox "Error replacing " & strPath & " with new file: " & err.number & "; " & err.description
                CopyStuff = 6
          else
                CopyStuff = 1 'Remember that in Main, a "1" means successful
          end if
    end function
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36946788
Assuming the second component could have leading zeroes then try:

    str = "SE*0318*0001"
    arr = Split(str, "*")
    arr(1) = Right("0000" & CStr(arr(1) + 1), 4)
    str = Join(arr, "*")
   
Chris
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36948194
Okay, seems like both Chris and I favor the Split approach, I thought you wanted to subtract the third item, not always 1.  Given that, then change:

if left(strLine,3) = "SE*" then
    strLine = Left(strLine, 1, 3) & Mid$(strLine(4, 47) -1 & right(strline,5)
end if

Open in new window

to this:

if left(strLine,3) = "SE*" then
    arr = Split(strLine, "*")
    arr(1) = Right("0000" & CStr(arr(1) - 1), 4)
    strLine = Join(arr, "*")
end if

Open in new window

~bp
0
 
LVL 5

Expert Comment

by:gman84
ID: 36948197
Or... expanding on Chris' solution

    str = "SE*0318*0001"
    arr = Split(str, "*")
    arr(1) = Format(Cint(arr(1)) -1,"0000")
    str = Join(arr, "*")

Assuming that it must always contain 4 characters
0
 
LVL 5

Expert Comment

by:gman84
ID: 36948205
Also as a further note your code is slightly misformed
if left(strLine,3) = "SE*" then
    strLine = Left(strLine, 1, 3) & Mid$(strLine(4, 47) -1 & right(strline,5)
end if

Open in new window

Should of been
if left(strLine,3) = "SE*" then
    strLine = Left(strLine, 1, 3) & Mid$(strLine,4, 7) -1 & right(strline,5)
end if 

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36948450
Gman84

Bear in mind the CBS application hence format is not an option and why Bill and I used right ...

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36948455
vbs ...darned spill checkers!
0
 

Author Comment

by:tim44202
ID: 36949978
did not work.  I believe the problem is that the second component using the split or mid method is a string and the subtraction of 1 needs to have the string in component 2 which in my test case of "SE*1318*0001" is 1318 converted to an integer

but Cint does not seem to be allowing math to be performed on the substring of "1318" yielding "1317"

The value in the second component will vary but I always need to subtract 1 to account for the record being dropped by the logic  
" if Left(strLine, 6) <> "REF*87" then" this drops the record of which there is always only one per file.  The "SE*1318*0001" is a trailer record indicating that this particular file had 1318 records in the input file and the output file will have 1317 records.
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 36950009
What am I missing, I just created this as a small VBS script:

strLine = "SE*1318*0001"
Wscript.Echo strLine

if left(strLine,3) = "SE*" then
    arr = Split(strLine, "*")
    arr(1) = Right("0000" & CStr(arr(1) - 1), 4)
    strLine = Join(arr, "*")
end if

Wscript.Echo strLine

Open in new window

And it produced this output, which is what you are looking for.

SE*1318*0001
SE*1317*0001

Open in new window

~bp
0
 

Author Closing Comment

by:tim44202
ID: 36951249
The problem was answered  earlier.  I stupidy had a logic error if my IF..THEN..ELSE coding
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 36951709
Thanks.

~bp
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question