Extract data from pdf in power automate desktop Problem 3-Need Help

[bdmafuz]

Hello @Agnius you solve my Extract data from pdf in power automate desktop Problem-Need Help & Extract data from pdf in power automate desktop Problem 2-Need Help post. Now i have another issue. In my flow (Pic-1) i extract data from single page (Pic-2) but i have multi pages pdf file. When i select one multi pages pdf file it write only first page data. Can you tell me how can i do this.

Thanks & regards

bdmafuz

[Agnius]

In your Extract text from PDF action, set the "Page(s) to extract" value to "All" and it will extract all the text from all pages.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[bdmafuz]

@Agnius i check before (Pic-3) but it not work. Need set any loop ???

[Agnius]

What exactly did not work? What is it that you expect and what actually happens? Please provide more context.

[bdmafuz]

@Agnius First see my flow (Pic-1 & 2). When i run with select "All" it only write data from 1st page. But my pdf have 20 pages. If i select "Range" from-1 to-20 then it write data from 1st page, if i select "Single" and mentioned page number it can write data from my desire page. I want to write data from all pages. 

Thanks

bdmafuz

[Agnius]

Okay, I see. I thought you simply had a single case in a file that has multiple pages. But if you have multiple pages, each of which contains a separate case that you need to process, you do need another loop inside the existing one. You would need to loop through the pages and extract the text one page at a time.

I suggest doing a while loop with the condition being 1 = 1 (infinite loop) and then have a page index that you increment on each iteration with an Increase variable action.

Then use the page index in the Extract text from PDF action, so you keep reading one page at a time.

Finally, add a rule that makes your flow go to a label that is outside of the while loop on error on the Extract text from PDF action. This will make your flow stop extracting pages when you reach the final page. This is needed because there is no native way to get the number of pages in a PDF document, so you don't automatically know how many pages you need to extract.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[bdmafuz]

Hello @Agnius i think i found my problem it happen for my "Split Text" variable (Pic-1, attach previous replay). In my pdf i need "Employee ID" (Pic-4) but when i "Extract text from pdf" it show like this (Pic-5 & 6). Now how can i extract data with "Employee ID" & "eTin" ???

[bdmafuz]

@Agnius Can you give me screen short please

[Agnius]

Here you go:

You will need to add your actions for retrieving specific values in the place where I added the comment.

The Extract text from PDF action has the following On error settings: 

This will make it stop extracting pages from the current file, when the extraction fails (assuming there are no more pages in the file) and continue to the next file.

Here's a snippet:

LOOP FOREACH CurrentItem IN Files
    SET PageIndex TO 1
    LOOP WHILE (1) = (1)
        Pdf.ExtractTextFromPDF.ExtractTextFromPage PDFFile: CurrentItem PageNumber: PageIndex DetectLayout: False ExtractedText=> ExtractedPDFText
                ON ERROR
                    GOTO 'Exit nested loop'
                END
        # Your existing actions go here
        Variables.IncreaseVariable Value: PageIndex IncrementValue: 1
    END
    LABEL 'Exit nested loop'
END

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[bdmafuz]

Hello @Agnius Thanks for your help, by my flow Pic-1) it can write data on excel. On my folder have only one pdf file have 20 pages after 20 pages flow not save & close, it continue page 21,22,23..... and write last data again and again (Pic-2) loop not stop. When i check I found on my “Extract text from PDF” flow “On error” tab when I select label and save, it turns red automatically and deselect label (Pic-3).

[Agnius]

Okay, that seems like a bug with the Go To functionality.

Try using an On block error instead like this:

The On block error is set up to go to end of block:

Here's the snippet:

LOOP FOREACH CurrentItem IN Files
    SET PageIndex TO 1
    BLOCK 
ON BLOCK ERROR

END
        LOOP WHILE (1) = (1)
            Pdf.ExtractTextFromPDF.ExtractTextFromPage PDFFile: CurrentItem PageNumber: PageIndex DetectLayout: False ExtractedText=> ExtractedPDFText
            # Your existing actions go here
            Variables.IncreaseVariable Value: PageIndex IncrementValue: 1
        END
    END
END

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[bdmafuz]

Here's the my flow snippet: Attached with pdf & excel file. My pdf file have 20 pages after 20 pages flow not save & close, it continue page 21,22,23..... and write last data again and again loop not stop.  Check please and send me final snippet

Folder.GetFiles Folder: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext''' FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext\\etin.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
LOOP FOREACH CurrentItem IN Files
SET PageIndex TO 1
LOOP WHILE (1) = (1)
Pdf.ExtractTextFromPDF.ExtractTextFromPage PDFFile: CurrentItem PageNumber: PageIndex DetectLayout: False ExtractedText=> ExtractedPDFText
# Your existing actions go here
Variables.IncreaseVariable Value: PageIndex IncrementValue: 1
Text.ParseText.RegexParseForFirstOccurrence Text: ExtractedPDFText TextToFind: $'''eTIN:.*''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
Text.SplitText.Split Text: ExtractedPDFText StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
SET Line1 TO TextList[4]
SET Line2 TO Match
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: 1 FirstFreeRowOnColumn=> FirstFreeRowOnColumn
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Line1 Column: 1 Row: FirstFreeRow
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Line2 Column: 2 Row: FirstFreeRow
END
END
Excel.SaveExcel.Save Instance: ExcelInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

[Agnius]

The one thing you did incorrectly there is that you added all of your actions after the Increase variable action. I specifically added a comment to show you where to add your files, but you added them in a different position. This would make it skip the first page all the time, because you increment the index before you extract the data.

Here's what it should be like:

Folder.GetFiles Folder: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext''' FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext\\etin.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
LOOP FOREACH CurrentItem IN Files
    SET PageIndex TO 1
    LOOP WHILE (1) = (1)
        Pdf.ExtractTextFromPDF.ExtractTextFromPage PDFFile: CurrentItem PageNumber: PageIndex DetectLayout: False ExtractedText=> ExtractedPDFText
        Text.ParseText.RegexParseForFirstOccurrence Text: ExtractedPDFText TextToFind: $'''eTIN:.*''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
        Text.SplitText.Split Text: ExtractedPDFText StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
        SET Line1 TO TextList[4]
        SET Line2 TO Match
        Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
        Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: 1 FirstFreeRowOnColumn=> FirstFreeRowOnColumn
        Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Line1 Column: 1 Row: FirstFreeRow
        Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Line2 Column: 2 Row: FirstFreeRow
        Variables.IncreaseVariable Value: PageIndex IncrementValue: 1
    END
END
Excel.SaveExcel.Save Instance: ExcelInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[bdmafuz]

Hello @Agnius sorry for disturb you all time. I think i can't understand you properly. After follow of your instruction all of my data extract and write on excel properly. On my pdf file have only 20 pages after 20 pages flow not stop, it show continue page 21,22,23..... and write last data again and again (Pic-1) on excel, loop not stop, i stop manually. In my previous replay i attached my pdf & excel file.

[Agnius · Accepted solution]

Seems like I was actually incorrect to assume that PAD will fail when reading a page that is out of range. Apparently, it reads the last page and does not fail, thus the On block error action does not kick in.

Instead, I would suggest using the following code:

Folder.GetFiles Folder: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext''' FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext\\etin.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
LOOP FOREACH CurrentItem IN Files
    Pdf.ExtractTextFromPDF.ExtractText PDFFile: CurrentItem DetectLayout: False ExtractedText=> ExtractedPDFText
    Text.ParseText.RegexParse Text: ExtractedPDFText TextToFind: $'''(?<=\\r\\n)\\d{5}(?=\\r\\n)''' StartingPosition: 0 IgnoreCase: False Matches=> Matches1
    Text.ParseText.RegexParse Text: ExtractedPDFText TextToFind: $'''eTIN:.*''' StartingPosition: 0 IgnoreCase: False Matches=> Matches2
    LOOP LoopIndex FROM 0 TO Matches1.Count - 1 STEP 1
        SET Line1 TO Matches1[LoopIndex]
        SET Line2 TO Matches2[LoopIndex]
    END
    Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
    Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: 1 FirstFreeRowOnColumn=> FirstFreeRowOnColumn
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Line1 Column: 1 Row: FirstFreeRow
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Line2 Column: 2 Row: FirstFreeRow
END
Excel.SaveExcel.Save Instance: ExcelInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

This will read all pages at once and then use Parse text to retrieve the relevant values both for the employee ID and eTin, but will return all matches instead of a single match. The result will be two lists that you can loop through and write to Excel.

Alternatively, to make it a bit faster, you can actually write the list to Excel right away, without looping through each value. You can do that like this:

Folder.GetFiles Folder: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext''' FileFilter: $'''*.pdf''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\TOSHIBA LAPTOP\\Desktop\\ext\\etin.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
LOOP FOREACH CurrentItem IN Files
    Pdf.ExtractTextFromPDF.ExtractText PDFFile: CurrentItem DetectLayout: False ExtractedText=> ExtractedPDFText
    Text.ParseText.RegexParse Text: ExtractedPDFText TextToFind: $'''(?<=\\r\\n)\\d{5}(?=\\r\\n)''' StartingPosition: 0 IgnoreCase: False Matches=> Matches1
    Text.ParseText.RegexParse Text: ExtractedPDFText TextToFind: $'''eTIN:.*''' StartingPosition: 0 IgnoreCase: False Matches=> Matches2
    Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
    Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: 1 FirstFreeRowOnColumn=> FirstFreeRowOnColumn
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Matches1 Column: 1 Row: FirstFreeRow
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Matches2 Column: 2 Row: FirstFreeRow
END
Excel.SaveExcel.Save Instance: ExcelInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.