Problem with Transferring Table Data from a PDF to Excel Spreadsheet

[KaiHalpin]

This is a little bit more of a complex problem so I will give you a little more detail. 

I am trying to transfer data from a large number of pdfs into an excel sheet.

So the pdf's I am working with come in slightly different formats. One of them (See First Image (bottom page)) has a single table that I need. However, when I run this type of pdf through the Extracted PDF tables command, I always received back 2 tables. One (2 columns and x number of rows) with the information of the Metered Rate, Tubing Pressure, Mechanical Loss, etc. The Second table (the data I need) picks up the rest of the data (ex: 998 - 1018, PERI, 1134 - 1145 100%). As a result I use the command Write to Excel Worksheet on the following value ExtractedPDFTables[1].datatable in order to only pick up the data from the second table and then import said data into my excel spreadsheet. This is what I want to happen.

However, when doing the second type of pdf (please see 1st image first page), the program has a problem. With this type of pdf there are two tables that I will need to extract to Excel. However, when running the Extracted PDF tables, it still only picks up 2 tables. The second table comes out exactly how the second table comes out in the first scenario so I have no problems. However, the first table includes the undesired information along with the data I need. I have attached images of first scenario in image 2 and the images of second type of pdf in image 3. 

For clarification for the second scenario first table, I only want the information in rows 5-6.

I am wondering if there are any workarounds or solutions you guys may have to workaround this issue. Thanks for your time.

[MichaelAnnis]

If it is consistent enough that you are only getting these 2 results, that’s great news. 

paste the whole thing and use the Excel functions to delete the columns you don’t need. 

do you know which PDFs are which version?

if not, use conditional (IF) to logically decide which one got pasted and delete the columns if it’s wrong.

good luck!

[Deenuji]

@KaiHalpin 

You can use "Find/Replace" action for data table and get the row number where that dashes are starts and ends.

If we use the below actions

We will get output like below in DataTableMatches variable.

In our scenario, Line begin from the 4th row and end by the 8th row. Therefore, we also need to exclude the lower 'B'.

To obtain the actual values, we start from DataTableMatches[0][0] + 1, which corresponds to where the actual table rows begin.

For concluding the table, we use DataTableMatches[0][1] - 1(one row before the line) -1 to remove the additional row for the 'Lower B' data

Then you can use "Delete row from datatable" action and delete all rows except the rows between Actual value starting row and end row.

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 🚀

[Steveg48]

One thing I’ve done in a similar situation is to do an extraction or other test to determine which format I’m dealing with. Then I tailor my final extraction based on that result.

[KaiHalpin]

Hi, thanks for the response. I have the row numbers of all of the rows I want to delete in a table. I was wondering how I am able to use the "Delete row from Excel Worksheet" Function to delete all of these rows on my spreadsheet. Sorry I am new to using this program. Thanks for the help.

[Deenuji]

@KaiHalpin 

Please follow the below approach:

Code:

Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2'], [$'''Metered Raye''', $''':1000'''], [$'''Tubing Pressure''', $''':495'''], [$'''Mechanical Loss''', $''''''], [$'''Interval Type: Interval %% Loss Remarks''', $''''''], [$'''______________________________________''', $''''''], [$'''1420 - 1503 PERI 1450 - 1490 77%%''', $''''''], [$'''1580 - 1561 PERI 15200 - 1561 23%%''', $''''''], [$'''Lower B''', $''''''], [$'''______________________________________''', $''''''], [$'''Metered Rate''', $''':650 B/D'''], [$'''Tubbing Pressure''', $''':570 PSI'''] } DataTable=> DataTable
Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: DataTable AllMatches: True ValueToFind: $'''__''' MatchCase: False MatchEntireCellContents: False ColumnNameOrIndex: 0 DataTableMatches=> DataTableMatches
SET StartRow TO DataTableMatches[0][0]
Text.ToNumber Text: StartRow Number=> StartRowAsNum
SET StartRowAsNum TO StartRowAsNum + 1
LOOP LoopIndex FROM 1 TO StartRowAsNum STEP 1
    Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
END
Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: DataTable AllMatches: True ValueToFind: $'''Lower''' MatchCase: False MatchEntireCellContents: False ColumnNameOrIndex: 0 DataTableMatches=> DataTableMatches
SET EndRow TO DataTableMatches[0][0]
Text.ToNumber Text: EndRow Number=> EndRowAsNum
SET EndRowAsNum TO EndRowAsNum
LOOP LoopIndex FROM EndRowAsNum TO DataTable.RowsCount STEP 1
    Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: EndRowAsNum
END

How to copy/paste the above code in your power automate desktop?

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 🚀