Hello! I just started learning how this software works a few weeks ago and I’m kind of stuck. Would like some guidance regarding this below:
I currently have an output file that I got by OCR-ing pdf and I want to code it such that the IF function finds this particular phrase in the output file, if it’s true THEN it’ll look for another string in the same output file and copy that. ELSE, it’ll look for a different phrase in the output file and copy that different phrase. Final output is to paste in an excel file…
I have same requirement to read the text file and then find out some string in the previous line so that my code will pick the next line and get the project id from that line and write it to excel. Will give you that code, if it is helpful, you can take it as reference and use it further.
Dim TextLine As String
Dim countOfIntegrationService As Integer = 0
Dim countOfLinesInFile As Integer = 0
Dim countOfHours As Integer = 0
Dim isTotalHoursLine As Boolean = False
Dim totalHoursLineCount As Integer = 0
Dim requiredTotalHoursLine As Integer = 0
Dim hoursCaptured As Integer = 0
Dim isIntegratedResource As Boolean = False
Dim objReader As New System.IO.StreamReader("D:\UIPath files\Time sheets\Attachments\Deepfieldglass.txt")
Dim _excel As New Microsoft.Office.Interop.Excel.Application
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim StringNeeded As String = String.Empty
Dim numberNeeded As String = String.Empty
Dim Address As String = String.Empty
Do While objReader.Peek() <> -1
TextLine = objReader.ReadLine() & vbNewLine
countOfLinesInFile = countOfLinesInFile + 1
If countOfLinesInFile = 3 Then
StringNeeded = TextLine
End If
For Each c As Char In StringNeeded
If IsNumeric(c) Then
numberNeeded = numberNeeded & c
End If
Next
Address = StringNeeded.Replace(numberNeeded, "")
Loop
_excel.Visible = True
wBook = _excel.Workbooks.Add()
wSheet = CType(wBook.ActiveSheet(), Microsoft.Office.Interop.Excel.Worksheet)
Do While objReader.Peek() <> -1
TextLine = objReader.ReadLine() & vbNewLine
countOfLinesInFile = countOfLinesInFile + 1
If TextLine.Contains("Total") = True And countOfIntegrationService <> 0 Then
If totalHoursLineCount = 1 Then
isTotalHoursLine = True
wSheet.Cells(1, 3) = "Total"
requiredTotalHoursLine = countOfLinesInFile
End If
totalHoursLineCount = totalHoursLineCount + 1
End If
If TextLine.Contains("Integration Services") = True Then
isIntegratedResource = False
countOfIntegrationService = countOfIntegrationService + 1
TextLine = TextLine.Split("Integration Services").Last
TextLine = TextLine.Split("-").First()
Dim Res As String = String.Empty
If countOfIntegrationService = 1 Then
Res = TextLine.Substring(TextLine.Trim.Length - 15)
wSheet.Cells(1, 1) = Res.Replace("(", "").Replace(")", "").Replace("-", "")
Else
Res = TextLine.Substring(TextLine.Trim.Length - 14)
wSheet.Cells(1, 2) = Res.Replace("(", "").Replace(")", "").Replace("-", "")
End If
Res = ""
End If
If countOfLinesInFile > requiredTotalHoursLine And totalHoursLineCount = 2 Then
If TextLine.Trim <> "" Or TextLine.Trim <> String.Empty Then
MessageBox.Show(TextLine)
If hoursCaptured = 0 Then
wSheet.Cells(2, 1) = TextLine.Trim
ElseIf hoursCaptured = 1 Then
wSheet.Cells(2, 2) = TextLine.Trim
Else
wSheet.Cells(2, 3) = TextLine.Trim
End If
hoursCaptured = hoursCaptured + 1
End If
End If
Loop
Dim strFileName As String = "D:\UIPath files\Time sheets\Attachments\Deepfieldglass.xlsx"
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wBook.SaveAs(strFileName)
wBook.Close()
_excel.Quit()
Is the phrase going to be the same every time? If so, then no need to search after finding a match
If outputText.Contains(“This is your phrase you’re looking for”)
Then (true side) Assign outputPhrase = “phrase was found”
Else (false side) Assign outputPhrase = “phrase not found”
Unfortunately the phrases won’t be the same every time. Only Phrase 1 will be the constant phrase.
Condition IF is to find “Phrase 1”
Then (true), it has to find “Phrase 2 - Currency and amount” and copy that phrase 2 to excel
Else (false), it has to find a different phrase, “Phrase 3” and copy it’s corresponding “Phrase 4 - Currency and amount” to excel instead.
Phrase 2 to Phrase n are different phrases. Also because there are quite a number of different currencies, is it recommendable to set a Switch Activity for the currencies and place it before my IF Activity?
I’m not sure I follow. Is it not an If-then-else? Is it instead if-then-elseif?
There’s also not a whole lot of information about the input text so i’m making a lot of assumptions. I’m assuming you only want currency and amount, not Phrase2 or Phrase4 included. Regex is perfect for this, make sure to import Regex.Text.RegularExpressions using the imports tab
If outputText.Contains(“Phrase1”)
Then (true side) Assign outputPhrase = Regex.Match(outputText,“(?<=Phrase 2 -\s)\S+”).Value
Else (false side) Assign outputPhrase = Regex.Match(outputText,“(?<=Phrase 4 -\s)\S+”).Value
if you want to include the words Phrase 2 and Phrase 4, then change it to be the following: