extract information from PDf and store it in excel using Desktop flow

(0) Share

Report

Posted on by Rakesh_Tiwari

Hi All,

i am new to power automate, and i am try to get some hands-on on Power automate Desktop flows.

i have few pdf files which contains few information like Invoice no, Billed To Amount etc...

i have read the multiple file from folder, and launched excel file to store the extracted information then itereate through the each files using for each then used extarct text from Pdf action and stored in a variable extractedText.

Now how to find above information from it. see below img for reference

Thanks in advance

Rakesh

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (0)

Kaif_Siddique

2,108 Super User 2024 Season 1 on at

Like (1)

Report

Copy link

Link copied!

Hi @Rakesh_Tiwari ,

Here is a solution:

Copy and Paste to PAD

Folder.GetFiles Folder: $'''D:\\Sample Files''' 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: $'''D:\\Invoice Data.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
LOOP FOREACH CurrentItem IN Files
Pdf.ExtractTablesFromPDF.ExtractTables PDFFile: CurrentItem MultiPageTables: True SetFirstRowAsHeader: True ExtractedPDFTables=> ExtractedPDFTables
Variables.RetrieveDataTableColumnIntoList DataTable: ExtractedPDFTables[0].DataTable ColumnNameOrIndex: 0 ColumnAsList=> BilledTo
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: BilledTo Column: 1 Row: FirstFreeRow
Variables.RetrieveDataTableColumnIntoList DataTable: ExtractedPDFTables[0].DataTable ColumnNameOrIndex: 1 ColumnAsList=> DateofIssue
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DateofIssue Column: 2 Row: FirstFreeRow
Variables.RetrieveDataTableColumnIntoList DataTable: ExtractedPDFTables[0].DataTable ColumnNameOrIndex: 2 ColumnAsList=> InvoiceNumber
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: InvoiceNumber Column: 3 Row: FirstFreeRow
Variables.RetrieveDataTableColumnIntoList DataTable: ExtractedPDFTables[0].DataTable ColumnNameOrIndex: 3 ColumnAsList=> AmountDue
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: AmountDue Column: 4 Row: FirstFreeRow
Variables.IncreaseVariable Value: FirstFreeRow IncrementValue: 1
END

If I have answered your question, please mark my post as Solved.

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

Regards

Kaif

Was this reply helpful? Yes No

Rakesh_Tiwari 8 on at

Like (0)

Report
Copy link

Link copied!

Hi @Kaif_Siddique ,
Thanks for your reply,
Can you Pls check for this error?
Also let me know how to get method panel to select the methods
Extracted data is coming like this i believe.

Was this reply helpful? Yes No
Kaif_Siddique 2,108 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Could you share your flow screenshot and sample PDF files ? if still facing the issue.

In my case, i have considered single page pdf file which has only one table (total 5 pdf files using for testing). Below is the sample pdf file.

And extracting the pdf table data to excel.

If I have answered your question, please mark my post as Solved.
If you like my response, please give it a Thumbs Up.

Regards
Kaif

Was this reply helpful? Yes No
Rakesh_Tiwari 8 on at

Like (1)

Report
Copy link

Link copied!

Hi Kaif,
Actually, the output was coming as text not as table, so i used split text action and then used regex to extarct the information from it.
And that worked.
But, thanks for all your effort. in future also i try posting my issue, pls try to answer them.

Happy Automation
Rakesh

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @Rakesh_Tiwari
If you decide to extract text (not tables) - then use "parse text" with regex expressions to get your data.
Examples of expressions based on your data:

(?<=Billed To\s\s).+(?=.)

(?<=Invoice Number\s\s)\d+(?=.)

(?<=Amount Due $INR$\s\sRs.).+

If your documents are consistent, and always the same, it should play out just fine.
However, sometimes extracting tables gives better results and then you need to work out how tables are extracted, and you need to see what table cell reference for which field.

that's what you need to test and see what is better for your case.

Was this reply helpful? Yes No
takolota1 4,980 Moderator on at

Like (0)

Report
Copy link

Link copied!

You can also now use this template to extract data from PDFs without Regex directly in a cloud flow using GPT: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Extract-Data-From-PDFs-and-Images-With-GPT/td-p/2201345

Was this reply helpful? Yes No