PAD Use Case: SQL to Excel to HTML Table to Email

PAD Use Case: SQL to Excel to HTML Table to Email | Power Automate Desktop

Рет қаралды 9,018

Күн бұрын

In this video, I use a suggestion given in a comment on a previous PAD video to try building out a use case in Power Automate Desktop that involves querying SQL Server for some data, placing that data into Excel, then taking that data from Excel, converting the datatable into a HTML Table, and then sending it in an HTML-enabled email from Outlook.
---------------------------------------------------------------------------------------------------------
Power Automate Desktop Version 2.10.36.21161
Windows 10 Pro
SQL Server 2019
SQL Server Management Studio
---------------------------------------------------------------------------------------------------------
/ davemorrisatl
---------------------------------------------------------------------------------------------------------
00:00 Introduction
01:08 Overview of what I intend to build
02:07 Create all the Subflows that we’ll need
04:31 Build Subflow for Query SQL Server
08:29 Build Subflow for Write To Excel
15:53 Build Subflow for Read From Excel
20:10 Build Subflow for Convert DataTable to HTML Table
33:36 Build Subflow for Send HTML Email
34:46 Final End-to-End Test and Closing remarks

Пікірлер: 66

@robertgrzelak3350 3 жыл бұрын

OMG! Thank You :-)

@eiestens 3 жыл бұрын

Hi Dave, this stuff is amazing!!! I love the way you teach! Thanks a lot!

@asam 3 жыл бұрын

My favorite PAD channel

@jameswyffels9414 Жыл бұрын

Holy Smokes! Thank you very much for this. I am very grateful for your help. Exactly what I needed (sans the HTML part). You are a fabulous teacher.

@arulmanohar 2 жыл бұрын

Hey Dave! Thanks for this video!

@claytonmckinney7392 Жыл бұрын

Thank you for providing this and going through the necessary workaround when this should be a feature to simply add headers (yes or no). This is why UiPath is so much better than Power Automate Desktop. The only reason I'm using PAD is because my company refuses to get a license to UiPath. Definitely worth the money in my opinion.

@TrassBI 2 жыл бұрын

Very useful.👏Thanks for sharing!🌝

@user-4755 2 жыл бұрын

Like a magician 🎩

@peternguyen1895 2 жыл бұрын

Freaking awesome!

@jeisonsanchez4842 2 жыл бұрын

Hey Dave, you probably already know this… if you set the run delay to 1ms at the bottom of the flow designer window, it should run almost as fast as it does from the console. 👍

@DaveTheRPAGuy 2 жыл бұрын

Thanks! Yeah I did discover that a little while after making this video, and I ended up using it in the most recent PAD video. I originally thought it was just a delay before the run starts. Please do let me know any time you think I might not know something though. I appreciate it!

@johankneubuhler5479 Жыл бұрын

I think you can get the column names passing DataTable.ColumnNames, without needing get from a row specific like 0

@Hunter-ud2gk Жыл бұрын

Hi Dave, really great stuff thanks for walking through this. Have a scenario that I am stuck with wondering if you have faced it... The query I am running might not always have any records as I am looking for pending orders. However, when the flow runs and there are no records, the table fails to be created in the "WriteToExcel" subflow. Have you encountered this / have a way the table can be created without data? Or is there a way to put a statement in here to bypass this piece of the process if the SQL query returns no data? Thanks!

@DaveTheRPAGuy Жыл бұрын

Hi Hunter, I meant to respond a couple weeks ago to this. I was going to suggest that you could create a blank table using the new DataTable action 'Create new data table' in the situation that no records are returned. I'm not sure what the purpose of that would be though, and I assume you'd just have to put in Exception Handling that would take the flow through the creation of a new data table that you define manually when you build the flow.

@janvanhaver6970 3 жыл бұрын

Bonus question: what does it take to adapt the formatting of the table if e.g. you want to make sure the address is shown as a single line, not 2 or 3 lines? Adding more code to HTML, I guess, or would changing the column width in Excel influence that too?

@DaveTheRPAGuy 3 жыл бұрын

Hmm, interesting question! Changing the column width in Excel wouldn't affect the HTML, but yeah you could add some markup to the HTML. I looked it up for just a sec and tried at least the "width" attribute. I've read that it's deprecated so I'm not completely sure this is the best way, but in the table header of the column you could do something like this: Address What that should do is give 40% of the table's width to the Address column, and then the other columns should get an equal amount automatically such as 10%. I tested it a bit just now and it works okay. One other thing you could change is to put the table's own width at 100% instead of whatever I had in the video so that the table will attempt to use the full width of the window, and that would likely put the address on a single line on most monitors/resolutions without setting an individual column width.

@janvanhaver6970 3 жыл бұрын

@@DaveTheRPAGuy in another video I stumbled across, another guy added (in Power Automate, not PAD, but it should work here as well) a section at the beginning of the HTML to format the table in a fancy way.

@dellvostro2011 2 жыл бұрын

Hey Dave Have you done anything on from get data from Excel in to PDF document and then email the completed PDF document? Also brilliant channel, learning so much from you.

@DaveTheRPAGuy 2 жыл бұрын

I haven’t done that, but I’ll look into it.

@santiagoaylwin4056 2 жыл бұрын

Hi Dave, thanks very much for your video its help me a lot. But i have a question, if i want to change the design of the table, were i need to work, in the set variable(html)??

@DaveTheRPAGuy 2 жыл бұрын

Yeah in the subpage where the HTML structure is created. It’s been a while since I’ve looked back at this video. Let me know if you’re still looking for an answer here. I see you asked a week ago so you may have figured it out already.

@sandeephanda7453 2 жыл бұрын

Hi Dave, thanksforthe great video. Is there a way to append the result of the SQL querry, into a google sheet. Each query to append below the other

@DaveTheRPAGuy 2 жыл бұрын

I haven't played around with using Google Sheets from Power Automate Desktop at all. I would think that the cloud version of Power Automate has a connector for Google Sheets, but I haven't used the cloud side of PA in a couple years.

@daxteoh27 2 жыл бұрын

Hi Dave, once again thanks for your video! Is it possible to read data from excel and write it in website html table? Sort of, read for each exceldata then populate data according to html table column row by row.

@DaveTheRPAGuy 2 жыл бұрын

Yeah absolutely. I think the issue or thing to figure out is the format of the table in the website and then how you'd loop through it. I'm not aware of any existing action that would just take an Excel table and write it into a similar table in a website though. There's just so many types of tables in webpages, that I think it'd be hard to make that reusable or at least for it to exist in PAD already at this stage of its development.

@AmitSharma-lp1qt 3 жыл бұрын

Hi Dave, If you can please show how to display a message box for the dataset values one by one through a loop in an SQL table. I was unable to. It either shows the entire values in the table or just the headers.

@DaveTheRPAGuy 3 жыл бұрын

Hmm interesting. Yeah I’ll add that to my list. I plan to do some recordings this weekend.

@BaybieK 2 жыл бұрын

By the time you get to 15:33: If you get "Index out of range" error change the ColumnHeaders ColumnNumber variable to QueryResult[0].ColumnNames

@abdullahbinshahzad Жыл бұрын

I am in Example: hello 2 Their is no attributes in it how can i check if the li contain 2 then go to second list with hello Is their is a way to select one ui element based on the value into other

@dreamvideos1207 2 жыл бұрын

Hi dav, Thanks for this video Can you please help me as stucked with one scenario Filtering data from SQL and writing back to excel but not writing original data like I have 00013 value is available then writing it 13 to excel Please help

@DaveTheRPAGuy 2 жыл бұрын

Apologies for the late response. What I would try doing is using an Excel file as a template. Basically, you manually create an Excel file, format the cells to be Text or whatever you need, and then store that Excel file in a shared location. Then the automation should copy that Excel file when it goes to create new files, so then when you write into it with a number that has leading zeros such as 00013, it should keep the zeros because Excel knows that column is text. An alternative is to figure out how to format the cells to be text using PAD in the flow itself, but I just haven't checked to see if PAD has that capability, so I don't remember if it can already do this without custom code.

@WillySandi Жыл бұрын

Hi Dave, Can you set data format in excel? Ex: '0001', '0002' -> if we copy this data to excel, it will automatically set this as general format type. So the '0' will removed. In this case i want to keep the format as text, so it will keep the full format like '0001' dan '0002'. Is there a way to do this in Write to Excel worksheet?

@DaveTheRPAGuy Жыл бұрын

Hi, I'm a little late responding, but the best way I've found to handle this is one of two ways. Either (1) manually create an Excel file and manually set an entire column to be of datatype/formatting of "text" and then have the flow copy that file and use it like it is a template which effectively solves the problem you described because the whole column is already set to text. Or (2) set the formatting of the range of cells BEFORE using a Write action; if this functionality exists in PAD already then I'm not aware of it, so I would probably go with #1.

@ojimbageorge4157 Жыл бұрын

Thanks for this amazing video with large data it is really slow, is there an alternative way to create a data table considering data table action is available now ?

@DaveTheRPAGuy Жыл бұрын

I'm not sure I understand. What would you be trying to create a data table for?

@ojimbageorge4157 Жыл бұрын

@@DaveTheRPAGuy sorry my earlier question was confusing, what I meant was is there an alternative way to create an Html Table ? I tried using the step in this tutorial and I noticed that with large data, it takes forever

@DaveTheRPAGuy Жыл бұрын

@@ojimbageorge4157 Oh I see. Yes, the faster way would for sure be to write custom code for that using one of the actions for that such as powershell. I'm not aware of any kind of conversion action, so you'd just have to writr it yourself with the same logic.

@j4g36 2 жыл бұрын

Great, do you happen to have a way to iterate through an HTML table with Power Automate on a web page or either take the entire table?

@DaveTheRPAGuy 2 жыл бұрын

As far as I know, the best way to do this in PAD is with the 'Extract Data from web page' action. I haven't made any video about it, but Anders Jensen has. Here's a link that should take you to the point in the video where he starts using it: kzfaq.info/get/bejne/jb57Y9hj3r6wook.html

@hotelranimahaljaipur9380 2 жыл бұрын

how to connect dataverse with dekstop flow and make changes on dataverse table?

@DaveTheRPAGuy 2 жыл бұрын

Late response from me here. Haha. I haven't used dataverse at all yet.

@habimuc8959 5 ай бұрын

Hi Dave, great video, thanks. It is a little help for my task, but not complete, because i need to export the query result from the sql instance directly into a html table. this table i want to send with outlook. i want to skip the sql2excel step. how do i have to proceed here. Thank you. In any case, I have subscribed to the channel. Great videos. Thanks

@DaveTheRPAGuy 5 ай бұрын

I would think you should be able to just skip the steps as you said. It's been a while since I recorded this, but a SQL query should return a DataTable in PAD, and reading from Excel also returns a DataTable in PAD, so I assume you'd be able to do the same logic to convert to HTML Table by using the output from the sql query. did that not work for you?

@habimuc8959 5 ай бұрын

Hi Dave, I tried your instruction one step after another and get problem in the for each loop when I began with 1 in the variable %QueryResult[1].ColumnNames%. DPA needs 0 (%QueryResult[0].ColumnNames%) for looping correctly. May this help one or the other. Regards, HaBiMuc

@DaveTheRPAGuy 5 ай бұрын

I think others have also mentioned that at some point Microsoft added the ability to get the column names from the datable itself without having to reference a specific row first. I have not gone back to test this, but I do recall there being a property for that on the DataTable itself as well.

@sathya2870 2 жыл бұрын

hey hi dave, can you give me an idea about reading the captcha with numerical

@DaveTheRPAGuy 2 жыл бұрын

I've never tried it. I would think most captchas are too difficult for normal OCR, so I'd try to find an API that says it can handle reading captchas, and then you'd screenshot that portion of the screen and send the screenshot to the API and it would respond back with the numbers it read.

@sathya2870 2 жыл бұрын

@@DaveTheRPAGuy if you found API ,suggest me to do

@latifabdurrahman1211 3 жыл бұрын

i can't connect with mysql database! in tab dropdown provider what should i pick?

@DaveTheRPAGuy 3 жыл бұрын

I don't use MySQL, but you could check for connection strings to use here: www.connectionstrings.com/mysql/

@latifabdurrahman1211 3 жыл бұрын

@@DaveTheRPAGuy thanks dave i will try

@latifabdurrahman1211 3 жыл бұрын

@@DaveTheRPAGuy great it work Thanks :)

@calvinjeng 2 жыл бұрын

@@latifabdurrahman1211 how about to do ? please

@cnchandroo 2 жыл бұрын

Hi Dave, I have been watching your series on PAD and it is really fantastic and I learned a lot of tips and tricks. Keep sharing your knowledge. I do have one request to you, is it possible to leverage PAD for two factor authentication, where the verification code comes to user's mobile and user has to send email the verification code for 2nd level authentication to resume PAD actions. Here is my thought 1. After 1st level login, 2FA kicks in, PAD sends email to user 2. via email user sends verification code 3. PAD receives verification code and automatically enters the code 4. 2FA successful I might be completely wrong, but just want your opinion, if it is feasible using PAD. Thanks Chandrasekaran

@DaveTheRPAGuy 2 жыл бұрын

I have no experience with trying to use PAD to handle 2FA/MFA, but I would guess that it's possible to automate in PAD as long as a user can accomplish the same thing and especially if the verification code is sent to the user over email. I would be less confident if it involved text messaging or mobile Authenticator apps. That should also be possible, but I think it would be more difficult. However, you'd need to give PAD access to the email in some way. For example, if it's Gmail, then you may have to enable certain less secure settings to allow POP3 to be accessed by what Google considers to be less secure applications. If you have Outlook configured with the mailbox on the same machine where PAD runs, then that seems like another option as well. This is an interesting use case. I may look around later today to see if I can find a website that consistently prompts with 2FA login and see if I can automate it. If it works out, then I may go ahead and post a video about it. It seems like it should be a relatively simple thing to do.

@cnchandroo 2 жыл бұрын

@@DaveTheRPAGuy Thanks Dave for your response and looking forward your video on this.

@DaveTheRPAGuy 2 жыл бұрын

I'm struggling to find a website that handles 2FA by sending a code through email. Every website I've checked so far requires the use of either text messages or an authenticator app. Do you have a specific website you know of that sends emails with a code?

@cnchandroo 2 жыл бұрын

@@DaveTheRPAGuy Not handy right now, but will check and revert you, if I found any sites, which send code via email. Thanks

@ShahjehanMukoon-md3ln Жыл бұрын

How can we retrieve an HTML table data from an Outlook mail?

@DaveTheRPAGuy Жыл бұрын

From what I see, the PAD action "Retrieve email messages from Outlook" doesn't expose the underlying HTML of an email. This could actually just be an issue from my using a Gmail account in Outlook though. So, first I would try using that action and then use the property ".Body" from the result. Even if it doesn't contain the HTML, the table should have been converted to plain text but tab delimited. At that point, your only option really is string manipulation or perhaps Regular Expressions.

@haameemshimar8828 2 жыл бұрын

hy dave.is the power automate free for oracle db connection?

@DaveTheRPAGuy 2 жыл бұрын

I don’t know if the cloud part of Power Automate’s connector for Oracle DB is or is not free, but every action in Power Automate Desktop is free to use as long as you have access to the target database.

@joevaran 3 жыл бұрын

Dave, are you on vacation? We miss your videos!

@DaveTheRPAGuy 3 жыл бұрын

Haha thanks! I’ve just been super busy. I’ve also been trying to switch to showing my background in my office instead of using a green screen. I’ve put up a bunch of sound panels and stuff and I’m almost done with that, so hopefully I can start playing around and recording again soon. =)