aw that's so great to hear!

Yeah!! I didn't realize VBA could do this, so we both learned something. 😆

Did you add a breakpoint? The red circle? That will stop it after one loop, I think I added one in the video to show it... you can click the dot to turn it off. Also check what you're looping over for the actions and whether the thing that you're looping over contains all the files you want to loop on (you can check this by looking into the variable content after it runs).

Cool! It depends on what your variations are… I’ve used the switch/case cards for this, basically to check if it contains the word I’m looking for before parsing (with one case for each variant, and slightly different regex for each).

Try putting the if-statement above the parse text step - so it will only run the parse text and split steps if the file contains the text you're looking for. That way you don't have to error handle it, because it won't error. :) When you do this, make sure to have a step that resets any variable you were using to store values to blank at the end of the loop, so that doesn't insert the prior loop's value in Excel when the text doesn't exist.

Probably, you can use a regular expression to collect all matches for your query - I had GPT write one to try, this looks for the word "balance" and gets anything after it so you can see if something similar would work: balance:\s*\$?(\d+[\.,]?\d*) It should put all the matches in a list, then you can loop over those to do things after splitting out whatever you don't need. BUT you might also try connecting to the file with Power Query (it can use PDFs as a source) and see what that gives you. The only reason I didn't use it in the example is because multiple files usually don't have the exact same structure between the files, but if yours is all in one file it might work. Alternately there's a ton of new AI options in the cloud Power Automate for entity extraction that use AI Builder credits, and you likely have credits if you have Power Automate licenses to try.

Thanks! There's not a list of regular expressions per se - here's more info on them: coderpad.io/blog/development/the-complete-guide-to-regular-expressions-regex/ ChatGPT/copilot are very good at creating regular expressions, so I usually just start by giving them what I'm looking for and an example and it will usually work. Do make sure to tell it something along the lines of "just give me the regular expression, not Python, and do not use look-aheads or look-behinds" to get the "right" thing to plug in. :)

That's a really good question. I didn't need to get checkboxes so I didn't think to try - I found a PDF with checkboxes to prod at and when I copy and paste a checked box out of the PDF there is no indication that the box exists from that or that it's checked, which makes me think the extract text isn't the way to go here. I tried doing an internet search, too, and didn't find anything that looked like a functioning example of someone doing this, so you've stumped me lol. It's not supported in AI Builder either, apparently, which is usually a good fallback. Though maybe with OCR there it'd be able to "see" the checkmark?

Depends what the files look like, you could try using an action or regular expression that gets text between two values to get everything in between whatever comes before and after the address - that should get it no matter how many lines there are. Not sure how it needs to be broken out, but regular expressions are great at parsing the street address/zip/state/etc from a text block. You can use if-conditions to run different expressions according to various things (e.g. count the line break characters to get a line count and use that, or if it is a certain country parse it differently etc).

Are they scanned PDFs? Scanned PDFs can be very weird on the text end. I would try opening one that is having the issue and selecting the text inside the PDF and pasting it into a text editor just to see what it looks like - that'll tell you if the issue is the Power Automate action or the PDF itself.

@@bi-ome I had the same issue. I just created a PDF with one line of text and I was able to select it in the PDF and copy it and paste it to Notepad and it appeared as text so I'm thinking this only works on special types of PDFs and it's impossible to know which ones it will work with . . .

@@kbirstein Do you have an example of what the weird text looks like? It could be an encoding issue.

Sure, I use ChatGPT to produce the regex because that saves a lot of time. It provided this to get the full text of line 36: (?:.* ){35}(.*) So if you take that with your parse text action and put it in a variable, then use the action that splits text (search for the word split in the action menu) and split on " | ", you will get each of the values you want in an array. You can reference those by position with %yourVarName[0]% for the first item, %yourVarName[1]% for the second, and so on. You can insert those in columns in Excel like we do in the example in your loop that loops over the docs. 😀

You can add either an "if" branch or "cases" that checks the PDF text to see if it contains some particular unique string for each circumstance before extracting the text (you'd use a different extraction expression for each branch). There's actions for "get all Excel worksheets" that you could probably combine with "set active Excel worksheet" to change the worksheet before it gets the first free row to insert the data. You'd get-all outside the loop, then inside each conditional branch have it set the active sheet before it inserts. I'm not sure if the action that swaps sheets gives itself sufficient time to complete or not before continuing, you might need to add a short pause between that and inserting (just something to watch for, I've not tried it yet - might be fine). :)

I feel like AI Builder is probably going to be easier for your case if you have the credits for it. The method in this video is better for extracting very particular information from the text using what amounts to trigger words to find the content. It has the advantage of being "free", but is not as flexible as a LLM would be.

​@bi-ome Thanks. I am looking for a free resource if possible in the first place, but let's see. By the way, how much credit does it require for doing this task, and do you know any other way to do this task for less money?

@@komailbutt2998 AI builder credits come with most of the Power Platform license packages, so you might have enough floating around to work with. They’re a first-come-first-serve sort of thing at the tenant level.

Is there something in particular you’re trying to do? The thing with Power Automate is it can do SO many things, it helps to know what people are looking for

I had a conversation with ChatGPT about this, because I'm not a regex expert, it says that regex is designed around finding text after the target and not before. Is there static text before that you can use instead? The wrench is that Power Automate doesn't seem to accept "lookbehinds" in regex like some applications can, so you have to get creative.

There's lots of ways to do the error handling - you could put the part that selects the value and adds to Excel steps in an error block and set it to continue at the end of the block if it hits an error (which will happen when it tries to do things to a null value). That would basically just have it continue on to the next PDF if it doesn't find a value - not sure if you need to record the blank or not. You could also use the "if" conditional to check the isolated value (after you remove the word you're looking for) or the second position in the array, using a "is not blank" and put the update Excel actions there. Meaning it would only add them to the Excel file if they had a value. You could use "else" to set it to some specific text like "no value" probably too? The error handling takes some trial and error to get working the way you want it to.

Thank you so much for the response. I’m able to get it work with your suggestions. Thank you!

Cloud doesn’t have regex per se, but you can extract from PDF with things like AI Builder to get what you’re after via prompt. The issue I have with it is that when it gets the file content from a PDF, it’s in base64 and has to be converted to be usable by AI… There’s various methods and workarounds for that, and alternatives you can use for more $$ like Syntex. The technique in this video is basically the budget DIY version of all that! 😅

@@bi-ome thank you for your prompt reply. ah I was searching around KZfaq as well and like you mentioned I did see solution relying on AI builder to mark/highlight each data field from PDF file. Too bad that regular expression is not supported by cloud which is a bit shocking for me. thank you very much!!

@@taesunyoo4637 You can use regex with Syntex/SP Premium! Just not Power Automate. Syntex is a more holistic version of all of this, it's premium priced but they put a UI around it and let you auto-extract the data and apply it to the item in the SP library.

If you just want the one value, you can use your target text and (.+) as the regular expression. That should get everything on the next line. If you are wanting to extract the WHOLE table, that depends on the structure of the PDF - if it's just one table, Power BI can sometimes grab it, but if you have multiple tables or a table that spans multiple pages and isn't always in the exact same location, there is entity extraction with AI in AI Builder or Syntex.

@@bi-ome thank you for your response Christine ! After further investigation, i discovered that Power Automate desktop has not only a functionality to extract text as you exposed in your video, but also the functionality to extract tables from PDF. Both functionalities are very complementary and very powerful. Thank you again !

@@Steph-ho2gl Awesome! Want to link to the resource you used? I don't actually use PAD all that often, this video was based on a technique I picked up while scrambling to do a particular project, so I'm interested!

Hi Christine, I used the following sequence : 1. Select on file using the action Message box - Select file >> Variable = “Selectedfile” 2. Extract table from PDF file [from SelectdFile into “ExtractedPDFTables” variable] 3. Message box where Power automate will list all the tables he identified in the PDF 4. Launch Excel (which I have prefilled with the header column I want to keep. 5. Insert “For each” to loop all the identified tables [Variable used = “ExtractedPDFTable”] a. Then embed one “if” condition and I specified a specific content on one header column. In fact, my PDF file contains 42 tables, and only one is interesting for my purpose because it contains a specific key word in the CurrentItem.datatable.columnHeadersRow Contains Key word”. i. Obtain the first free line in my excel >> It creates a variable “FirstFreeRowOnColumn” ii. Write in excel [CurrentItem.datatable]when condition is met b. End if 6. End the loop started in 5. Hope it can help you! @@bi-ome

@@Steph-ho2gl Thank you so much for taking the time to share!

I think this will be a very difficult one but technically possible... you might want to go with Power Automate Cloud for this instead of desktop, since you'd be triggering it when an email is received. There is a comment in this particular thread that has what looks like a viable solution to me, using the HTTP connector in a cloud flow: ideas.powerautomate.com/d365community/idea/94b667cd-89cc-4c61-8982-79b53c1a6019 Alternately, you can parse email text in the desktop version, maybe do regex to get the URLs ( \bhttps?://[^\s]+ ), then use that to launch a web browser with the URL (from a variable), and save them... Web-based PDFs are a particular pain because the "save" button for a PDF is not something PAD recognizes as a UI element typically, so what I've done as a workaround is use "send keys" to print it to PDF (control p). When you're saving it, you can use send keys again to send a particular text string to the filename field based on a variable (I use send keys with "tab" to tab down to that field... lol... because it can't seem to select it with the recorder). Increase the time between keys slightly or it will get "ahead of itself" while things load.

@@bi-ome Thank you. If I find a solution I will let you know.

Printing is tricky because often the UI browser often has trouble selecting menu options from the print menu when it runs. What I have done in the past is use "send keys" to do all the navigation in the print/save menu, using tab to navigate through options and "enter" to "click" selected buttons. I increase the delay between keys to significantly above the default, because if they run too quickly together they will trigger before the prior one is finished applying. You can use the action that allows users to enter an input to collect the save path if it will be different each time, then use the path concatenated with the filename in the filename slot in the save menu - that will save the file to whatever path is entered (e.g. C:\Users\username\Documents\FolderName\filename.pdf). Since this isn't usually a selectable input field, I use "send keys" to send the filename/path to that box - the cursor will be there by default when the window opens, if it's not for whatever reason you can send more keys to get there. 😊

The numbers were just in the PDF file, they're part of the table text. If you want row numbers in the text editor though, you can use a different text editor - e.g. VSCode would have that.

@@bi-ome Hello thank you for replying me ! i have been doing this power automate for my corporate JDE software and out of 10 times maybe 3 times it will log in successfully , by chance , do you know what's the problem?

The only way I've gotten the web pages to PDF to work is to "print to PDF" via using "send keys" with hotkeys, with tab to tab through menus and enter to "click" print. Lol. It works okay that way but you need to have pauses in there to space out the send keys actions. You'd just loop over the Excel rows and launch a browser with the link as the address and close the browser at the end of the loop.

It might be "getting" a line break character that you can't see until it's in Excel. You can try one of the methods here and see if one of these works: www.reddit.com/r/excel/comments/zolxmo/how_do_i_clear_this_empty_line_breaks_for_each/

Regular expressions are really commonly used to parse addresses, my guess is Chat GPT or the internet has them readily available. I would try to get the address, then split on the line break and comma to separate out the street, city, and zip-

Yes, there are SharePoint actions in the desktop app, but they will trigger premium licensing. You might consider using the Excel file to feed a PowerShell script, too, that would also work!

Do you have this feature turned on? "Power Fx enabled (Preview)." I heard from another commenter that this setting was on and it was changing the structure of all of the variable references. You might toggle it off and see if that does it. This video was created before this feature was added.

​@@bi-omeIt worked, because I couldn't figure out how to disable Power FX, what I did was I just created a new flow, and yes, it worked. Thank you for your help.

I'm not sure what a Leads data pdf sheet is, but it will work on any selectable text data in PDFs. If you want it to go to CSV, you can save the Excel file as CSV type at the end of the flow. There's also an action to go directly to CSV from a data table variable, skipping Excel, but we're not using data table variables in this particular tutorial - either will work

Yes, as long as they follow a similar structure (e.g. state name is always after "State:") and the text is selectable in the PDF (not scanned). If you're working with scanned ID cards, you need to use OCR on the text first, which you could do with a call to an API service.

The % signs are how you reference variables - so you can type %your-variable-name% to use them in formulas. It's just how Power Automate knows you're making a reference vs just normal text - so you can type whatever you want in the box. They won't have percentages in the dropdown menu. 🙂

@@bi-ome That was one of the first things i tried. It gives me an error that reads "Parameter 'Value to write': Unexpected characters. Characters are used in the formula in an unexpected way." I don't know if i am on a newer version or if there is a setting for the % signs. In your video, all your variables have % signs around them. In my use of power automate, nothing has a % sign around it.

@@piratefish5856 You are using PA *desktop*, right? Like from your start menu? Power Automate Cloud is very different and they are often confused--

@@bi-ome I am using the PA desktop app. Version 2.40.157.24023, which is the most recent version as of 01/29/2024. To try to be more specific, on your Write to Excel worksheet popup, in the Excel Instance block, on your video is shows "%ExcelInstance%" but for me i see "=ExcelInstance"; same thing for the Value to write block, your video shows "%InstitutionName%" by default, my screen shows "=InstitutionName"

Hm your comments somehow hit the moderation filter, and when I approved them they disappeared... but you mentioned something about the variables not having % in them? I would go to the step that they are created in, e.g. the step that opens the Excel file, and check that they have percentages there. When I name a variable in a step, it auto-adds the percentage signs, but perhaps yours don't for whatever reason? Also they only show up when you're editing the step and click on the var name in the editor, if that's part of it.

Sure, I went back and checked and its not super straightforward to download them from the FFIEC site directly, you have to put in the institution ID to get each file (it's been a while), but I found what looks like the files I used on my PC and uploaded them here: christine-payton.com/resources-files/

That usually means it’s blank, that there’s nothing in it. I would check to see what ends up in your variable when it runs, your expression may need adjustment-

​@@bi-ome I have got the same error , there is a [] sign besides my variable name please can you help me with the same??

It's running in a loop on the files, so it'll extract each thing you configure it to once per document. and create a row in Excel for each. If your files are structured differently, you can provide more info about what you're trying to do and I can try to offer a suggestion. :)

@@bi-ome See in my pdf I just dont have one id or one name I have multiple and I want to extract all of them one by one in excel so is there a way to do it? Like run a loop until all the values are extracted and then stop?

@@user-ws4ih1ev1q Yeah, there is a toggle in the "parse text" action where you can toggle whether it gets the first occurrence only or all. So if the names always occur after the text "Name: " you'd use Name: (.+) for your regex, toggle off "first occurrence only" and it should just put them all in an array. You can reference those based on array position, or for-each loop over the items in the array to do things with them. That said, if your PDFs are complex, it might be a lot easier to use AI tools like Syntex (but more expensive).

@@bi-ome Actually I used a python script to extract the data, the normal Parse Text was not working, so I dont know how to reach to the solution of my problem.

Yes? I would not generally do this for one handful of PDFs, it's just an example. 🙂 This will work on any PDFs that have selectable text in them, because the expression is just getting text next to or above or below other text, but not scanned PDFs.

You would need to use OCR on it in the flow to get the text. There’s various tools to do this, easiest to use is probably AI Builder in Power Automate Cloud-

If you sync it with Google drive to your local computer, and then open it with Excel, maybe? The thing is most of the actions for spreadsheets are Excel-specific, so it really needs to modify the file in Excel unless you create a CSV or something instead. A CSV would probably work, but you'd be going about it very differently.

You can only have the Excel file open in one instance. So if you are testing your flow, you need to remember to close the file between runs so that it’s not already open when the flow tries to open it :)

Good question! Power Automate Cloud does not have regular expressions and pulls the file content via API, meaning it comes in as base64 (garbled random letters). In order to do anything with it, you have to either translate it out of base64 (which requires a third-party connector that is not free) or use OCR on it as if it were an image to "recognize" the text and concatenate it all together. Then you'd use another AI connector on it, typically AI Builder or Azure Open AI, to parse the text and pull out the parts you want to put in Excel. Or use SharePoint Premium, which basically packages the above into a solution for you but is more expensive. So, long story short, since Power Automate Desktop is installed on most Windows machines and is free to use locally, it's often much less of a pita and less expensive to just use that vs cloud for this particular job. Power Automate Desktop essentially is just a robot that controls your computer where you can tell it what actions to take, so it bypasses the base 64 issue and allows regular expressions so you can extract without AI. But it's technically possible to get the same output with either.

@@bi-ome you are right. Currently trying AI builder method. Again, thanks for the informative content.

Yes, quite literally nothing you will ever make in Power Automate desktop will work the first time you run it - it requires troubleshooting to get things working. That's part of the process. :)

If you have any particular questions you can ask here and I'll try to answer. Any automation tool will require a technical person, or at least someone who can make it through a video or two lol. I would recommend this tool for people who use Microsoft 365, thus are already invested in Microsoft stack products. I used it here because it's what was available to me at the time.

You can look for other language words with regular expressions too!

Lol, no, thanks though - I have been using most of these tools a really long time. PA Desktop is pretty new for me, but I picked up this particular trick out of desperation trying to complete a particular project while doing a lot of mental flailing around trying to learn the tool. Thought it might save someone else some stress to share. 🤣

Puede ser complicado cuando tienes que lidiar con comas y signos de dólar, porque el PDF es texto y no necesariamente reconoce que esos son parte del número. Yo intentaría algo así (usé GPT para obtener esta fórmula, es muy bueno para hacer expresiones regulares). Current Balance \$\s*(\d{1,3}(,\d{3})*) Explicación: Current Balance \$: Coincide con el texto literal "Current Balance $". El signo de dólar se escapa porque $ es un carácter especial en regex. \s*: Coincide con cero o más caracteres de espacio en blanco. (\d{1,3}(,\d{3})*): Captura el número. \d{1,3}: Coincide con 1 a 3 dígitos (para el primer grupo del número). (,\d{3})*: Coincide con cero o más grupos de una coma seguida exactamente por tres dígitos.

@@bi-ome thank you so much for putting this video together, I have watched 3-4 times already and I almost finish my flow, the only thing is the same issue or similar as above, but my amounts do not have a dollar sign and are not next to the word but down below in a different row or underneath, and I have up to millions for the amount, could you please help me with the expression? Thank you

​@@aycampos Yeah, the expression TargetText: (.+) should get whatever is on the line below the target text (replace target text with the key word you're looking for). I'd run it with that and see if you get something you can split parts off of. It depends on if you have other text in that second line or not. For all of these I am just using ChatGPT to help me write regular expressions, just give it an example of what your content looks like and ask it to write "regex without look-aheads or look-behinds". It's very good at regular expressions. 🙂

@@bi-ome oh wow, thank you for getting back to me so quick, I'll try both the expression and ChatGPT.