Learn how to extract data from a Microsoft Excel spreadsheet and insert that data into an SQL database using Automate tasks. Andrew Usher, Technical Representative, gives you a guided tutorial on setting up an Excel to SQL task in Automate. In this video you’ll learn how to:
- Select the Excel spreadsheet and how to get the needed cells
- Create the SQL connections and insert them into the SQL itself
- Find and use a predefined SQL connection
Watch this chapter now to learn more.
[Andrew Usher:] We're just going to take a quick look at extracting data from an Excel spreadsheet and inserting that later into a Sequel database.
First of all, let's just quickly go to our selections and because we want to grab everything within that spreadsheet, we're just going to use the get cells action. Then all we need to do is click on the folder and reference the [00:00:30] Excel spreadsheet that we wish to use. Because we're going to grab everything within that spreadsheet, we're just going to use the get range of cells by reference and we're going to alter the [inaudible 00:00:41]. We don't care if there's ten rows and columns in the first run, or there's 100 rows and columns in the second one. Then all we're going to do is enter that information or store that information in a data set called DS_employees. To give the values a more meaningful name when we reference them from the data set, we're going to use the first [00:01:00] row as column names as well.
There we go, we've completed our step to grab all the information from that spreadsheet, so now we actually need to go ahead and basically create our Sequel connections and insert that into Sequel itself. If we go ahead and open the Sequel connection, all you need to do is build your connection and then go ahead and select your provider. Then reference your server name and instance. Then all we need to do is [00:01:30] reference our credentials, basically. You're going to use your Windows log note, or you can use some specific user and password. In this example we're going to use our Windows and then all you need to do is specify the table that you wish to use.
Just to ensure that it works, before you use this in your task, we can test the connection. That's one way.
Another way is to actually use a predefined Sequel connection that you've created at the start. Again, if we select the automate one here and come back into Task Administrator, what [00:02:00] you can see here is that we have a list of Sequel connections. Then again, we can go ahead and create the new, give the connection a name, select the data link, and then we're going to go ahead with the same procedure we just done. Your provider, your connection, so your server, your instance, your credentials, the database you wish to use. It's a similar process.
The perk of using a predefined Sequel connection is that if you reference this connection within 20 tasks, but you need to change one simple, maybe it's the password [00:02:30] for example, you only have to change this in one location and it updates across the 20 other tasks. You don't have to go into each of those 20 tasks to amend this connection.
If we come back and use this connection string here, so there we go. We've basically turned around and said we were going to grab everything within that spreadsheet. We're going to create our Sequel connection, so then what do we do? All I'm going to do now is I'm just going to run step one. Just so that I can show [00:03:00] you basically what we do. Basically all you can see here is if we go and inspect this data set, it's going to show you all the information that we have. When we actually go ahead and loop through the data set and then insert the information into Sequel, what we do is we reference these values here so the DS_employees.forname.gender.joined et cetera, so this is what builds our Sequel query that we use.
Now what we're going to do is just going to actually, like I said, loop [00:03:30] through that data set. I can drop the loop action, select the data set we wish to use, and then select okay. If we go back to our database actions, like I said, we're just going to run a Sequel query. You can see here, because we've already created our connection in step two, we're just going to reference that session. Because I already have the Sequel statement available, I'm just going to copy and paste that in here. Again, you can see here, insert [00:04:00] into this table within that data base. These are the column names within the database, or that table should I say, and these are the values that we're going to add, so forename into the forename column, surname into the surname column, and so on.
A good way of referencing these variables or the values collected from that spreadsheet is to basically use this percent sign here. Again, you can see here that we can just references forenames. I want to add this into here. This is how you can references [00:04:30] these values within that data set.
If we go ahead and select okay for this and then at the end, just our best practices, we close the connection as well.
Let's just go ahead and run this. What you're going to see is it's going to open up the spreadsheet, loop through that data set, and insert that information into a Sequel database. Once this is finished, what we'll do is we'll come back into Sequel management studio, we'll execute [00:05:00] the command and there we go. We can see all of the entries from that spreadsheet.