Power Automate for Desktop - Two Position Variables to subtract from multiple values

[slmacche]

I am extracting data from a PDF. I have two variables. One gives me the position of all the occurrences of the string "Business Purpose," and another gives me the position of all occurrences of "Description." I would like to have the first value in the Business Purpose" variable be subtracted from the first value in the "Description" variable. This is relatively easy in the cloud version, but I'm having difficulty determining which value in the variable to use in the Desktop version. I would also like to use a variable to determine that value. For example:

Count all values in the variable "Business Description". This would be the starting "counter." Then, each time it is read, the counter goes down until it is below 0.

Sample

BusinessPurpose[0]

BusinessPurpose[1]

etc.

In layman's terms, here is what I'm trying to do:

For each value in "Business Position"

Increase variable Counter by 1

End

For each Counter

Business Purpose[Counter]-Description[Counter]

(the rest of the process would go here, but I only need to know the syntax for the variable with the counter.)

End

I've attached an image of the arrays I have set up.

Sample Variables

[Deenuji]

@slmacche 

Please find the below approach as per your layman's terms. Feel free to extend or modify the logics.

Code:

Pdf.ExtractTextFromPDF.ExtractText PDFFile: $'''C:\\Users\\deenu\\OneDrive\\Documents\\Combined.pdf''' DetectLayout: False ExtractedText=> ExtractedPDFText
Text.ParseText.Parse Text: ExtractedPDFText TextToFind: $'''Supplier''' StartingPosition: 0 IgnoreCase: True OccurrencePositions=> BusinessPurpose
Text.ParseText.Parse Text: ExtractedPDFText TextToFind: $'''germany''' StartingPosition: 0 IgnoreCase: True OccurrencePositions=> Description
SET Counter TO 0
LOOP FOREACH BusinessItem IN BusinessPurpose
    SET Counter TO Counter + 1
END
LOOP LoopIndex FROM Counter TO 0 STEP -1
    SET Difference TO BusinessPurpose[LoopIndex] - Description[LoopIndex]
END

How to copy/paste the above code into your desktop flow?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[slmacche]

I'm getting an "out of range" error. My goal is to extract the text and populate an excel table. 

[Deenuji]

@slmacche 

Could you please explain your full use case with some example and if possible attach some screenshot of your pdf file as well.

[slmacche]

I'm trying to extract the following from each item (beginning on page 2) in the attached report. I've highlighted the info.

Date

Cost Code

Amount

Business Purpose

Thank you so much for your continued assistance.

[Deenuji]

@slmacche 

You may adopt the following approach, which is stable (albeit time-consuming), as your table was not formatted correctly. Therefore, if we opt for the 'extract table' method, it may not always be stable.

Code:

Pdf.ExtractTextFromPDF.ExtractTextFromPageRange PDFFile: $'''C:\\Boot\\Transaction Report.pdf''' FromPageNumber: 2 ToPageNumber: 3 DetectLayout: False ExtractedText=> ExtractedPDFText
Text.SplitText.Split Text: ExtractedPDFText StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> ExtractedpdfList
LOOP FOREACH CurrentItem IN ExtractedpdfList
    SET Last_TransItem_Flag TO False
    IF Contains(CurrentItem, $'''PCard Transaction''', True) THEN
        SET Last_TransItem_Flag TO True
        Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''\\d{2}/\\d{2}/\\d{4}''' StartingPosition: 0 IgnoreCase: False OccurrencePositions=> DatePositions Matches=> DateMatches
        IF DatePositions.Count > 0 THEN
            SET Date TO DateMatches
        END
        Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''G--\\d{6}-\\d{5}''' StartingPosition: 0 IgnoreCase: False OccurrencePositions=> CostcodePositions Matches=> CostcodeMatches
        IF CostcodePositions.Count > 0 THEN
            SET Costcode TO CostcodeMatches
        END
        Text.ParseText.RegexParseForFirstOccurrence Text: CurrentItem TextToFind: $'''\\d+\\.\\d{2} USD''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> DisbAmtPosition Match=> DisbAmtMatch
        IF DatePositions.Count > 0 THEN
            SET DisbAmt TO DisbAmtMatch
        END
    END
    IF Last_TransItem_Flag = True THEN
        SET Costcode TO Costcode + CurrentItem
    END
    IF StartsWith(CurrentItem, $'''TransactionNumber''', True) THEN
        Text.ParseText.RegexParseForFirstOccurrence Text: CurrentItem TextToFind: $'''TransactionNumber\\s(\\d+)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> TransPosition Match=> TransMatch
        IF TransPosition > 0 THEN
            SET TransNum TO TransPosition
        END
        # Write your excel row here...
        SET Date TO $'''%''%'''
        SET Costcode TO $'''%''%'''
        SET DisbAmt TO $'''%''%'''
        SET TransNum TO $'''%''%'''
    END
END

How to copy/paste the above code into your PAD?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[Deenuji]

@slmacche 

I will share some optimized method tomorrow EOD

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[slmacche]

Looking forward to it.

[Agnius · Accepted solution]

You don't need to use a counter here. It just adds an extra unnecessary loop.

All you need is this:

You can paste this code into your flow:

LOOP LoopIndex FROM 0 TO BusinessPurpose.Count - 1 STEP 1
    SET Difference TO BusinessPurpose[LoopIndex] - Description[LoopIndex]
END

Or, if you want to go backwards, do this:

The reason why you got an "Index out of range" exception in your previous attempt was because indexes are 0-based in PAD, and your counter was 1-based. The final item should be %Counter-1% or, as shown in my case, it's %BusinessPurpose.Count-1%.

[slmacche]

That worked. Thank you so much!