cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LPad
Regular Visitor

Not all table extracting correctly in PAD and how to solve it ??

I have a pdf of multiple pages and i want to extract all the tables. But only green mark table (in the attached image) extracted correctly but red mark table (in the attached image) is not. It is missing the header, couple of first row and first column. I have attached my flow here along with pdf. Please give me a solution that how can I extract the red mark table exactly as it is. Though column header is not a problem if its missing through extraction. Need a solution that any future pdf of such format must satisfy the flow and give correct extraction.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Agnius
Most Valuable Professional
Most Valuable Professional

A table like that will not be extracted nicely via the Extract tables from PDF action. You will need to use the Extract text from PDF action and then parse the text to create a table variable.

 

Here's a sample flow that could handle the table you marked as red in your PDF file:

Agnius_0-1689046362332.png

 

What it does is as follows:

  1. Creates a new table and deletes the empty row (as it is impossible to create a table without the empty row)
  2. Extracts the text from your PDF file
  3. Parses the text with regex to retrieve the relevant lines
  4. Splits the retrieved text into a list with an item for each line
  5. Loops through the lines and splits it by space to get each item separately
  6. If the line contains 5 items, inserts it directly into the table created in step 1, else, inserts it with some blank values in between.

Here's a snippet you can copy and paste directly into PAD to have the actions created automatically for you:

Variables.CreateNewDatatable InputTable: { ^['Index No.', 'Unit', 'Pack Breakup', 'Pack', 'Quantity'], [$'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
Pdf.ExtractTextFromPDF.ExtractText PDFFile: $'''C:\\RPA\\pad_pdf_file.pdf''' DetectLayout: False ExtractedText=> ExtractedPDFText
Text.ParseText.RegexParseForFirstOccurrence Text: ExtractedPDFText TextToFind: $'''(?<=MULTIPACK\\r\\nQUANTITY\\r\\nIndex No..+\\r\\n)(.+\\r\\n)+?.+(?=\\r\\n\\d+\\s\\d+\\r\\n.+PREPACK)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
Text.SplitText.Split Text: Match StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP FOREACH TextLine IN TextList
    Text.SplitText.Split Text: TextLine StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextLineList
    IF TextLineList.Count = 5 THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: TextLineList
    ELSE
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [TextLineList[0], '', TextLineList[1], '', TextLineList[2]]
    END
END

 

Note you will need to change the file path in the Extract text from PDF action for this to work.

 

An important note here: this flow will not work for a document where the table is split over two separate pages. That's because it parses the text using the headers and the totals after the table. Since the headers are repeated for each page and the totals are only there in the last page where the table ends, if your table is split over two pages, this flow would include the headers as text lines, so you would need to handle that. 

 

In order to handle it, you could add some extra conditions into the loop like this:

Agnius_1-1689046873583.png

 

This will skip the part of the loop that splits the line and inserts it into the table, if the line contains some of the text that should be in the headers.

 

Here's a snippet with the total flow:

Variables.CreateNewDatatable InputTable: { ^['Index No.', 'Unit', 'Pack Breakup', 'Pack', 'Quantity'], [$'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
Pdf.ExtractTextFromPDF.ExtractText PDFFile: $'''C:\\RPA\\pad_pdf_file.pdf''' DetectLayout: False ExtractedText=> ExtractedPDFText
Text.ParseText.RegexParseForFirstOccurrence Text: ExtractedPDFText TextToFind: $'''(?<=MULTIPACK\\r\\nQUANTITY\\r\\nIndex No..+\\r\\n)(.+\\r\\n)+?.+(?=\\r\\n\\d+\\s\\d+\\r\\n.+PREPACK)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
Text.SplitText.Split Text: Match StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP FOREACH TextLine IN TextList
    IF (Contains(TextLine, 'MULTIPACK', False) OR Contains(TextLine, 'QUANTITY', False) OR Contains(TextLine, 'Index No', False)) = True THEN
        NEXT LOOP
    END
    Text.SplitText.Split Text: TextLine StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextLineList
    IF TextLineList.Count = 5 THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: TextLineList
    ELSE
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [TextLineList[0], '', TextLineList[1], '', TextLineList[2]]
    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.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

 

 

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

View solution in original post

Agnius
Most Valuable Professional
Most Valuable Professional

P.S. You actually can create a data table with headers but no rows if you paste the code as 

Variables.CreateNewDatatable InputTable: { ^['Index No.', 'Unit', 'Pack Breakup', 'Pack', 'Quantity'] } DataTable=> DataTable

In which case you would not need the Delete row from data table action.

But this is not something you can actually do via the UI of PAD at all. The only way to do it is to create the action first, then copy it to a text editor, modify it to remove the empty row and then paste it back to PAD. So, the more natural solution is the one I posted above.

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

View solution in original post

7 REPLIES 7

Well.... the Extract tables from pdf action is fairly limited. 

 

If the document is always formatted like what you uploaded, you can extract to text and then pass that to something like Javascript, Python, or VBScript. I am looking at it now.

=======

mmonline_0-1689026736120.png

 

There are definitely pattern-esqe elements. I am guessing this would require a bit of work to nail down and make work properly.

 

The tables return are inconsistent even between the first non-standard table and the next.

mmonline_1-1689026946847.png

The above does not include the Index No. column.

 

The next one:

mmonline_2-1689027005826.png

Closer but pretty ugly.

 

I have not evaluated any of the other tables.

 

Sorry I could not provide a better answer.

Agnius
Most Valuable Professional
Most Valuable Professional

A table like that will not be extracted nicely via the Extract tables from PDF action. You will need to use the Extract text from PDF action and then parse the text to create a table variable.

 

Here's a sample flow that could handle the table you marked as red in your PDF file:

Agnius_0-1689046362332.png

 

What it does is as follows:

  1. Creates a new table and deletes the empty row (as it is impossible to create a table without the empty row)
  2. Extracts the text from your PDF file
  3. Parses the text with regex to retrieve the relevant lines
  4. Splits the retrieved text into a list with an item for each line
  5. Loops through the lines and splits it by space to get each item separately
  6. If the line contains 5 items, inserts it directly into the table created in step 1, else, inserts it with some blank values in between.

Here's a snippet you can copy and paste directly into PAD to have the actions created automatically for you:

Variables.CreateNewDatatable InputTable: { ^['Index No.', 'Unit', 'Pack Breakup', 'Pack', 'Quantity'], [$'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
Pdf.ExtractTextFromPDF.ExtractText PDFFile: $'''C:\\RPA\\pad_pdf_file.pdf''' DetectLayout: False ExtractedText=> ExtractedPDFText
Text.ParseText.RegexParseForFirstOccurrence Text: ExtractedPDFText TextToFind: $'''(?<=MULTIPACK\\r\\nQUANTITY\\r\\nIndex No..+\\r\\n)(.+\\r\\n)+?.+(?=\\r\\n\\d+\\s\\d+\\r\\n.+PREPACK)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
Text.SplitText.Split Text: Match StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP FOREACH TextLine IN TextList
    Text.SplitText.Split Text: TextLine StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextLineList
    IF TextLineList.Count = 5 THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: TextLineList
    ELSE
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [TextLineList[0], '', TextLineList[1], '', TextLineList[2]]
    END
END

 

Note you will need to change the file path in the Extract text from PDF action for this to work.

 

An important note here: this flow will not work for a document where the table is split over two separate pages. That's because it parses the text using the headers and the totals after the table. Since the headers are repeated for each page and the totals are only there in the last page where the table ends, if your table is split over two pages, this flow would include the headers as text lines, so you would need to handle that. 

 

In order to handle it, you could add some extra conditions into the loop like this:

Agnius_1-1689046873583.png

 

This will skip the part of the loop that splits the line and inserts it into the table, if the line contains some of the text that should be in the headers.

 

Here's a snippet with the total flow:

Variables.CreateNewDatatable InputTable: { ^['Index No.', 'Unit', 'Pack Breakup', 'Pack', 'Quantity'], [$'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
Pdf.ExtractTextFromPDF.ExtractText PDFFile: $'''C:\\RPA\\pad_pdf_file.pdf''' DetectLayout: False ExtractedText=> ExtractedPDFText
Text.ParseText.RegexParseForFirstOccurrence Text: ExtractedPDFText TextToFind: $'''(?<=MULTIPACK\\r\\nQUANTITY\\r\\nIndex No..+\\r\\n)(.+\\r\\n)+?.+(?=\\r\\n\\d+\\s\\d+\\r\\n.+PREPACK)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
Text.SplitText.Split Text: Match StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP FOREACH TextLine IN TextList
    IF (Contains(TextLine, 'MULTIPACK', False) OR Contains(TextLine, 'QUANTITY', False) OR Contains(TextLine, 'Index No', False)) = True THEN
        NEXT LOOP
    END
    Text.SplitText.Split Text: TextLine StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextLineList
    IF TextLineList.Count = 5 THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: TextLineList
    ELSE
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [TextLineList[0], '', TextLineList[1], '', TextLineList[2]]
    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.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

 

 

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

P.S. You actually can create a data table with headers but no rows if you paste the code as 

Variables.CreateNewDatatable InputTable: { ^['Index No.', 'Unit', 'Pack Breakup', 'Pack', 'Quantity'] } DataTable=> DataTable

In which case you would not need the Delete row from data table action.

But this is not something you can actually do via the UI of PAD at all. The only way to do it is to create the action first, then copy it to a text editor, modify it to remove the empty row and then paste it back to PAD. So, the more natural solution is the one I posted above.

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

@mmonline Thanks for trying it out. I have tested the text version but it's pretty much work.

Agnius
Most Valuable Professional
Most Valuable Professional

Did you notice that I provided the entire script in my reply above?

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution.

If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

 

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

Thanks @Agnius . But I don't want to stop at "PREPACK" instead all the table in between order to order. This model stops before prepack, though prepack table structure is same. I found that solution. But it stops before the next order. Indeed it's a great solution. Thanks again for helping me towards the solution. 👍

Agnius
Most Valuable Professional
Most Valuable Professional

Glad I could help. You should be able to take it from here and handle other tables in the file by adjusting the starting and ending keywords around the pattern.

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

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages