Learn how to create a task in which Automate accesses an SQL database and writes the results to a Microsoft Excel spreadsheet. Pat Cameron, Director of Automation, gives you a guided tutorial on setting up a database to Excel task in Automate. In this video you’ll learn how to:
- Execute a stored procedure with the Database Action
- Use the Excel Action to create a new Excel spreadsheet
- Write the query statements within the task itself
Watch this chapter now to learn more.
[Pat Cameron:] I've got two tasks here that both access a sequel database and write to an Excel spreadsheet. So let's take a look a the first one which executes a stored procedure. So I use the database action, found it with the search bar and the first thing I'm going to do is connect to that database. There's a number of ways that you can [00:00:30] do that, you can make a database connection and then build that connection in the task, select the driver that you wan to use, the server, et cetera. You can also use a connection string, enter that into the box or use a data link file. I've got a predefined connection and we talked about that in the data base action video, I believe. Then you can set a time out if you like.
[00:01:00] The second step then is going to execute the stored procedure, so you have two options when you make this connection, you can either create a sequel query here or run a stored procedure. I've also got a constant that, where I've got that DDO file defined. So I can just select a variable, go to constants and select my Minnesota customers stored procedure. If you needed to add, pass any parameters to this stored [00:01:30] procedure when it ran, you can just enter them here. So you can enter the name, the value, the data type, select from the box here.
Then for the direction, input or output, et cetera. I'm not passing any parameters so I'm just going to leave it like this and then what I'm going to do is I'm going to write the results of this stored procedure to a data set, ds_cust. Then I'll go ahead and close the connection, I [00:02:00] don't need to have that open while I'm writing to the spreadsheet, I'm going to do it from the data set.
So if we run those first three steps, and we can go down and we can take a look at the data that's in that ds_cust data set, just to make sure that we've got the right records that we want to write, and you can see we do. Number of customers, the state, these are all Minnesota.
The next thing I'm going to do then is I'm going to find the Excel action, [00:02:30] so I just use in the search bar at the bottom, and now I can drag and drop any of those actions. So the first thing that I'm doing is creating a new spreadsheet and I can select the location where I want the spreadsheet to be. Got it in my SQL folder, and I can have this application be invisible, overwrite the workbook, et cetera. So you can set any of those options that make sense in your case. I'll go ahead and, I can also [00:03:00] create one from a template.
Then I'm going to select the set cells action from Excel and what I'm going to set is a value of the cells in my worksheet from the data set. So I've got a dropdown box for the data set and I'm just going to start writing them up in the upper left hand corner and I'm also going to include the column names when I write that data out.
So if I run this task it will make that [00:03:30] connection, run the stored procedure, close up the connection, that runs really quickly, and then it will create a spreadsheet and write the contents of the spreadsheet, or the contents of the data base to that spreadsheet.
So that's our first task. Oh, then I want to close up the workbook as well. You could save it or not save it.
Alright. [00:04:00] So let's take a look at our second task then. The second one, a little bit different, instead of running a stored procedure, what I'm going to do is I'm going to write the query statements right in the task itself. So, first I'm going to create a variable, I'm going to add today's date to my spreadsheet, so I'm going to create a variable called today and then format it so I can find the format action, and I'm going to format that date and time, what I want it to look like [00:04:30] when I add it to my spreadsheet name. There's today's date, and then the same step to establish the connection.
I'm just going to use my pre-defined connection string and now I'm going to execute a sequel statement. So what I have, instead of running a stored procedure, I've got the actual details for the sequel statement. These are my select statement. I can just add that into that field and I can add as many of those as I like, you can separate them with [00:05:00] a semi-colon and I'm going to write that data out to a data set as well.
I've got some options here for cash data, query time out and the maximum number of rows returned as well. Then I'll close up that connection , just like I do with the other one. Again, you don't need to leave that open while you're creating your spreadsheet. Now if I run this and I take a look at the [00:05:30] data set that I've got to make sure that I've got the right data, I can just right click on data set, select inspect and now I've got some customer names, state and their team color. A little bit different records I've got here.
Now I'm going to create a new spreadsheet, same thing, I'll find that Excel action again, drag the create over, create a spreadsheet, and you can see there I entered today's, that variable for today's date on [00:06:00] the name of that spreadsheet. I'll write out the contents of the data set, so I just select the data set in the upper right hand corner, and I'll include the column names here as well. Then I'll close up my workbook when I'm finished. This one I'll save because it's got the date.
[00:06:30] So this will look just about the same when I run it but the top part runs very quickly, the spreadsheet gets created, the data gets written and then we're going to close up that spreadsheet. So that's all you need to do to access a sequel data base and then write the results onto a spreadsheet.