Automate SQL Databases with Microsoft Power Automate Desktop

when I started my RPA developer career I found automating SQL databases very scary then a Friendly Senior developer took my hand and I realized how straightforward it is and of course how friendly he was let me do the same to you here I'm an assistant a Microsoft MVP let's learn some power automate desktop to connect to a SQL database go up to actions and find a open SQL connection and drag it in you can use the Builder by clicking here but it is rather slow so I prefer to creating this connection string myself first we will have our driver so start by writing driver equals and then in curly brackets I'll say sequel Server Like This and curly bracket end then I'll have a semicolon then I'll say server and I'll connect to my local database we set up the SQL server in the previous lesson if you haven't watched that you can do it by clicking the video in the upper right corner so here I'll say server equals then I'll have a parentheses start I'll say local and the parentheses end I'll have another semicolon and here I'll say integrated security like this equals true another semicolon and then I'll have the database name so I'll say database equals and my database is called Blacklist like this this Integrated Security equals two that means that we're using our Windows credentials we have variables produced called SQL connection we can refer to that connection when we want to access this database go click save here you can try to run it so if I go up here and click run it will take a few seconds and if I go over here to SQL connection and flow variables and I double click here we have opened it that's fine I can click close here usually you also want to close down this connection so I go up to actions and then I'll find a close SQL connection and drag it in this will just close our SQL connection that we have just opened which is fine but not much fun here let's also execute a SQL statement so I go up to actions and then I'll find a X shoot SQL statement drag it in between the open and close here we will get the connection by our SQL connection variable which we created up here it's also possible to use a connection string here but if we want to perform multiple queries that is not preferred the SQL statement again we will just use the SQL queries from the last lesson again I will just use this select it so I'll go copy it go back here and I'll paste it in here Ctrl V you're also more than welcome to write your own SQL query down here our variables produced is called query resolve that will contain the query result in a data table format I'll click save let's try to run it now so if I click run here again it will take around probably 10 seconds we open it and we executed it that's it if I move over here to query result and double click it you can see that we looked up this vat number we did it directly in the SQL Server management Studio before but here we have it and now we can use power automate desktop to connect to a SQL database so I'll click close here we have some sample data I downloaded it to my desktop you can find this Excel sheet in the video description so if I double click this Excel sheet and here I have my company's sheet this is a four column table I'll have the vat number which is unique identifiers that those are bad numbers I have a company name and then I have two blank columns called company check and the reason I want to look up each vat number in this Excel sheet I want to look it up inside my SQL database here I want to know if these ones are blacklisted companies or OK companies in case they are blacklisted I also want the reason here and that is the use case so close this Excel sheet we can also close down this notepad that we created before so I'll do that I want to grab the path of this companies here here I will say shift right click I'll move down and say copy as path back to Power automate desktop in the beginning I want a set variable set variable and I'll drag it in here this will be my Excel path so I go down here to variable and then I'll say Excel path and in the value I'll say Ctrl V I'll remove the quotation marks in the start and in the end power automate desktop don't use those the reason why we are keeping this Excel path in a variable instead of just hard coding in in an Excel action is that this is best practice this path can change and it will certainly not stay on my desktop this Excel sheet if I click save here then if I come back to the flow and I've moved my Excel sheet I can easily change the value of this variable let's also launch Excel so I go up here and I'll find a laurance Excel and I'll drag it in here I'll open up the following document document path I just created that up here called Excel path so I click this x and double click the Excel path I'll not make my instance visible if I scroll a little bit down you can see here that the variables produced is called Excel instance I'll refer to that one when I want to use this Excel book called stored in the Excel path here I'll click save I also want to read from this book now we're just launching it here with the launch Excel so I go up to actions then I'll find a read from Excel worksheet and I'll drag it in here we're reading in the Excel instance what do I want to retrieve I click this drop down and I'll say all available values from the worksheet that means that I get all the data inside this Excel sheet you want to click Advanced here because our data contains column names take that one the variables produced you can see here it's called Excel data that will be a data table that we can work with click save here what you also want to do is in the end we want to close this Excel instance so here I'll find a close Excel I'll drag it down here and for now we will not save it I will click save Let's test run this that we can actually open up the Excel we can read from it and we can close it again we haven't connected the Excel to our SQL queries yet but we will do so very soon that's it if you go over to your flow variables Excel data double click it here now we have our Excel sheet loaded into a data table so we can work with it let's iterate through this data table so that is for each one of these rows we want to use this vat number in our query right now it's quite static it says one two one five seven three two o but I want to use these vat numbers one by one let me click close here so go up to actions find a for each drag it in between the open and execute SQL statement the value to iterate that will be my Excel data so click this x here Excel data this was the data that we loaded into our data table that came from Excel here we stored into a variable called q and item we can rename it this is just a reference name so I can say current company that might be a little bit better because that says what's going on here you all click save so now we're entering to each value then let's drag in the execute SQL statement by tagging it bit the mouse and dragging it into the for each we need to replace this static value with the value from the Excel data which will now be in the current company one by one so double click here instead of this one here I will delete it we will use the qn company because the Q and company that is the current row when we do this iteration one by one that will get stored here so first we'll go over to this x variable picker always use this variable picker whenever you can then I'll take you and Company but this will be the entire row I only want the that number so here I'll either need to put in there column index or the column name column name is most stable because people can put in extra Columns of course they can also rename columns but that is less likely to find the column name click save here go over to your Excel data double click and the column name that is vat no with capital letters so I'll click close here I'll go into the execute SQL statement again and I move over here here you will have a hard bracket single quotation marks that space no then a single quotation mark and a parenthesis a hard parentheses end and we will produce a variable called query result let's try to write that one out so I click save here and let's just have a display message I'll drag it in still inside the for each year let me drag it over here I'll go down to message to display I'll click this little X here and I'll double click the query result go to variables produced let's not confuse ourselves so I'll just untick this button pressed since we are not going to use it I'll click save so what I will do here is that I'll run through this Excel data let me open it again I'll look up each vat number in our SQL database and then I'll get a query result back that means that if we can't find these bad numbers in the SQL database their query result will be blank let's go test it so here I'll click run and again it will just open that will take like five seconds and the queries will run very fast so the first one was blank I'll click OK blank okay here we have a blacklisted company sub photo didn't pay blank we have another one called might Sam that is root Behavior you'll see here that we get the entire row that is fine and we have the laby post let me just run quickly to it that's it so our automation works now now we just need to make it a little bit Beauty here so we can write the query results back to excel so I will use an if go up here to actions and then file an if here I'll drag it in after the execute SQL statement and here I want to look at the query result so if I go over to this Axiom and here you can see the query result that is also a data table so if I first let me just double click here so I want to say how many rows are in this data table and that means that if we have zero we know that we have a blank result then it's not a blacklisted company if it's not zero then it's a blacklisted company so what I can do instead here I can actually delete this and go back to this x I can go down to query result and click this Arrow here and then I can take the rows count for example let me double click here if I'll say the operator that will be not equal to not equal to 0 then I know I have a blacklisted company now I can click save let's try to move the display message up here so right now we will only print the blacklisted company and then we have took another step in our solution for this entire project we're opening it you can see that we're running through now we have the first blacklisted company the second and we have the Third that's it so now we're able to find three blacklisted companies in our data set and now instead of using these display messages let's update our data table called Excel data and let's print that data table back to excel so first I will delete this display message we'll need a helping variable and that is because we want to know where to write it back into this table that is not stored in the qn company so we don't know actually for each of these iterations we don't know where we are so we go up to actions and then we'll find a set variable again this is just the helping variable drag it in just before the for each we will call this one here row count and we will give it the value 0 that is because data tables are zero indexed the first row is index zero I'll click save then I want to increase this variable by one here in the end you can either use a set variable again or an increase variable they do the same I prefer the set variables so I drag this one that one in here in the end so go up to this X we will update the row count in the value click the X again row count we're going to add one to it and that is because for each iteration we want to add one to it so simply just go in here after the T and say plus one and we'll click save so now here in the if that means that we know we have a blacklisted company then we want to update the Excel data if I go over here to Excel data and I want to update the column called company checked I want to have a blacklisted in here and then the reason that will be the reason that we have in our SQL database so I'll click close here we will find another set variable and drag it in here first of all I want to update the Excel data data table so I click this x double click the Excel data but I also want to specify what cell and what column I want to update so I go up here and I can do that with indexes or variables or hard-coded values first one that is which cell I want to update and I use the row count for that let me drag it over here and go so I'll have a hard bracket here and I'll use the row count variable so I'll just type in row count we are inside these percentage signs that means that we don't need another pair of percentage signs then I can have a hard bracket end I'll have a hard bracket start and here I want to save it in the company check column so I'll have a single quotation mark and here I'll say company check another single quotation mark and then a hard bracket end the reason here is that the company check that is text another variable so this is a text variable coming from the data table and what value do I want to fill in the company check well we just go with blacklisted that's what we know so here I'll click save then we'll have another similar variable than this because we are lazy I prefer just to mark this Ctrl C Ctrl V I know we have two similar ones go in here now we're not going to update the company check but the reason so let me delete this company check type in reason instead we want to move into the query result for the reason so what I will do here is that I'll delete this blacklisted to see what we're actually going to take out I'll click save here this will give us an arrow that is fine but I want to go into this square root result I know that this is empty but if we have a blacklisted company then we will have one row in this data table that means that we can refer to this query result variable then we can say 0 for the first row and I want to get the reason back remember the query result that came from the execute SQL statement I'll click close here I'll move in here so the value that should be click the X here double click the query result move up here and again we set the zero and here I'll move into the reason of the reason column of the query result so I'll say reason and I'll have a hard bracket end so I'm updating the Excel data the row count that will be the current row that we're in and the reason column I'll do that by taking the value from the query result the first row and the reason column and that one we got from the execute SQL statement B keep in mind that we are in the if so we know that we have a blacklisted company we know that we have something here then I can click save so now that is done I also want to have in OK value if these two if this if is not zero that means that it equals zero and that means that it's not a blacklisted company I also want to have an OK value but first I'll go up to actions find an else and drag it in here and again I'm lazy so I have this in my clipboard I click this end and when I press Ctrl V that will paste in another one of this set variable double click to open it instead of blacklisted I'll have the oh okay like this I can click save so now we are running through this with the data I recommend you to go run it just to test it to see that we can update our data table and then if that works we just need to write it back to excel so here running through all 10 rows you can make it faster if you are changing this run delay tool one you can do this this is just the Run delay here in this editor you'll only see it in here when you move your robot to production it will be a run delay of one that is because you might often want to see what's going on when you're building these robots now go up to Excel data and click here here there you go we have now updated our Excel data data table with the data from the SQL database now we just need to save this to our Excel shoot so I could close here go up to actions and then find a right to Excel worksheet drag it down here between the closed SQL connection and close Excel the value to write that will be the Excel data double click here where do I want to write it since this is a data table I can start by writing in the upper left corner and then it will automatically fill in into the correct cells I'll say columnate and Row 2 and that is because we have headers in row one so here I'll click save one thing that we'll need to do is to go into close Excel double click here say save document and we'll click save here now try to run this again it will take a few seconds if you like this video then you can really help me a lot by giving it a thumbs up that's it here we have closed it let me move out here let us go into companies there you go your next lesson is here if you want to build your automation Network then you should join the Isle of automation Discord with more than 7 000 RPA developers ready to help you and network around your career

Channel: Anders Jensen

Views: 7,602

Keywords: power automate desktop, sql, sql in power automate desktop, automate sql, automate sql databases, automate sql with power automate desktop, sql power automate desktop, anders jensen, sql and power automate desktop, power automate desktop sql, power automate desktop tutorial, power automate desktop guide

Id: ABg-77uVTDM

Length: 23min 11sec (1391 seconds)

Published: Tue May 02 2023