We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# manipulating text

on
Medium Priority
208 Views
109290511122555        *****WORKORDR1              NO
2Amount Charged  \$6.00

109290511122555        *****WORKORDR2              NO
2Amount Charged  \$12.00

109290511122555        *****WORKORDR3              NO
2Amount Charged  \$14.00

I have a text file with records similiar to the records above. I need to loop through the records and move the
dollar amount from line 2 and place it next to WORKORDR% in line 1. Does anyone know how I can accomplish this?
Comment
Watch Question

## View Solution Only

Commented:
I would create a new file.

Read the source file 2 lines at a time (checking for blank lines and ignoring them)
Rebuild the new string as required, then output to the new file.
Dana Seaman (danaseaman)

Commented:
Try this solution using Split and Join:

Option Explicit

Private Sub Command1_Click()
Dim FF         As Integer
Dim Length     As Long
Dim sText      As String
Dim vSplit()   As String
Dim NewArray() As String
Dim NewJoin    As String
Dim lNewCount  As Long
Dim pos        As Long
Dim i          As Long

FF = FreeFile
Open App.Path & "/WorkOrder.txt" For Input As #FF
Length = LOF(FF)
sText = Input\$(Length, FF)
Close FF
vSplit = Split(sText, vbCrLf)
Debug.Print "UBound(vSplit)", UBound(vSplit)
For i = 0 To UBound(vSplit)
pos = InStr(vSplit(i), "\$")
If pos Then
vSplit(i - 1) = vSplit(i - 1) & " " & Mid\$(vSplit(i), pos)
If lNewCount Then
ReDim Preserve NewArray(lNewCount)
Else
ReDim NewArray(0)
End If
NewArray(lNewCount) = vSplit(i - 1)
lNewCount = lNewCount + 1
Debug.Print vSplit(i - 1)
End If
Next
NewJoin = Join(NewArray, vbCrLf)
FF = FreeFile
Open App.Path & "/WorkOrderNew.txt" For Output As #FF
Print #FF, NewJoin
Close FF
End Sub

Senior IT Developer

Commented:
@ earngreen

You need the new file content like this ?
========================

109290511122555        *****WORKORDR1 \$6.00
109290511122555        *****WORKORDR2 \$12.00
109290511122555        *****WORKORDR3 \$14.00

Commented:
I need the new file to look like this

109290511122555        *****WORKORDR16.00             NO
2Amount Charged  \$6.00

109290511122555        *****WORKORDR212.00           NO
2Amount Charged  \$12.00

109290511122555        *****WORKORDR314.00           NO
2Amount Charged  \$14.00
Dana Seaman (danaseaman)

Commented:
Option Explicit

Private Sub Command1_Click()
Dim FF         As Integer
Dim Length     As Long
Dim sText      As String
Dim vSplit()   As String
Dim sValue     As String
Dim pos        As Long
Dim i          As Long
Dim j          As Long

FF = FreeFile
Open App.Path & "/WorkOrder.txt" For Input As #FF
Length = LOF(FF)
sText = Input\$(Length, FF)
Close FF
vSplit = Split(sText, vbCrLf)
For i = 0 To UBound(vSplit)
pos = InStr(vSplit(i), "\$")
If pos Then
sValue = FormatNumber\$(Val(Mid\$(vSplit(i), pos + 1)), 2)
pos = InStr(vSplit(i - 1), "WORKORDR") + 7
If pos Then
For j = pos To Len(vSplit(i - 1))
If Mid\$(vSplit(i - 1), j, 1) = " " Then
vSplit(i - 1) = Left\$(vSplit(i - 1), j) & _
sValue & Mid\$(vSplit(i - 1), j + 1)
Exit For
End If
Next
End If
End If
Next
For i = 0 To UBound(vSplit)
Debug.Print vSplit(i)
Next
sText = Join(vSplit, vbCrLf)
FF = FreeFile
Open App.Path & "/WorkOrderNew.txt" For Output As #FF
Print #FF, sText
Close FF
End Sub

Commented:
danaseaman
Your code is similiar to what i need but the dollar amount may vary and I am not sure if your code would do the job. i have the following code that i have used in the past to get the position of dollor amount. it will always lie in position 18. Would there be a way to call this value in your code and append it to the WORKORDR3 field.

109290511122555        *****WORKORDR3              NO
2Amount Charged  \$14.00

Public Sub GetValue()
If Left(OutRec, 1) = "2" Then
X = Mid(OutRec, 18)
X = Left(X, InStr(1, X, " ") - 0)
MinValue = Val(X)
End If
End Sub

Commented:
danaseaman

With your code instead of looking for the \$ is there a way to look for that position on line two?

Thanks
Dana Seaman (danaseaman)
Commented:
Option Explicit

Private Sub Command1_Click()
Dim FF         As Integer
Dim Length     As Long
Dim sText      As String
Dim vSplit()   As String
Dim sValue     As String
Dim pos        As Long
Dim i          As Long
Dim j          As Long

FF = FreeFile
Open App.Path & "/WorkOrder.txt" For Input As #FF
Length = LOF(FF)
sText = Input\$(Length, FF)
Close FF
vSplit = Split(sText, vbCrLf)
For i = 0 To UBound(vSplit)
If Left\$(vSplit(i), 1) = "2" Then
sValue = FormatNumber\$(Val(Mid\$(vSplit(i), 19)), 2)
pos = InStr(vSplit(i - 1), "WORKORDR") + 7
If pos Then
For j = pos To Len(vSplit(i - 1))
If Mid\$(vSplit(i - 1), j, 1) = " " Then
vSplit(i - 1) = Left\$(vSplit(i - 1), j) & _
sValue & Mid\$(vSplit(i - 1), j + 1)
Exit For
End If
Next
End If
End If
Next
For i = 0 To UBound(vSplit)
Debug.Print vSplit(i)
Next
sText = Join(vSplit, vbCrLf)
FF = FreeFile
Open App.Path & "/WorkOrderNew.txt" For Output As #FF
Print #FF, sText
Close FF
End Sub

Not the solution you were looking for? Getting a personalized solution is easy.

Senior IT Developer

Commented:
@ earngreen , try this

Private Sub Command1_Click()
Dim sFileContent As String
Dim sNewFileContent As String
sFileContent = "109290511122555        *****WORKORDR1              NO" & _
vbCrLf & "2Amount Charged  \$6.00" & _
vbCrLf & "109290511122555        *****WORKORDR2              NO" & _
vbCrLf & "2Amount Charged  \$12.00" & _
vbCrLf & "109290511122555        *****WORKORDR3              NO" & _
vbCrLf & "2Amount Charged  \$14.00"
sNewFileContent = ExtractContent(sFileContent)
MsgBox sNewFileContent '<---- Save this to new file
End Sub
Private Function ExtractContent(FileContent As String) As String
Dim oReg As New RegExp
Dim oMat, oMCol
Dim sResult As String
Set oReg = CreateObject("VBScript.RegExp")
oReg.IgnoreCase = True
oReg.Global = True
sResult = vbCrLf & FileContent & vbCrLf
oReg.Pattern = "(\n\d+[ \t]+\*{5}WORKORDR\d+)([ \t]+NO\r\n\d+Amount Charged[ \t]+\\$)(\d+\.\d+)([ \t]*\r)"
Set oMCol = oReg.Execute(vbCrLf & FileContent & vbCrLf)
For Each oMat In oMCol
sResult = Replace(sResult, oMat.Value, oReg.Replace(oMat.Value, "\$1\$3\$2\$3\$4"))
Next
sResult = Left(sResult, Len(sResult) - 2)  'Removing vbCrLf
sResult = Right(sResult, Len(sResult) - 2) 'Removing vbCrLf
ExtractContent = sResult
Set oReg = Nothing
End Function
Senior IT Developer

Commented:
Change this line
>>Dim oReg As New RegExp
to
Dim oReg

Commented:
danaseaman

That worked great!

Thanks a lot for the help!
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile