How to import data from Microsoft SQL Server into Microsoft Excel

sql server is great for storing data but how can you use it in excel find out in this video how you can load tables and queries into excel power query and powerpivot hello i'm philip burton from filecarts.cod.uk in this article we are going to look at how we can use sql within excel sql stands for structured query language it is used in many databases such as microsoft sql server in such a database you store data in tables and you can run sql queries which are based around select statements to retrieve data the advantages of using a database such as microsoft sql server to store your data include the data is strongly typed meaning that you will not be able to store a number in a date field for instance this makes your data instantly validated it can be a central data repository for your data on multiple projects multiple people can access the same data at the same time this reduces duplication and inconsistencies and it's also secure security is built into relational database management systems microsoft sql server offers several layers of security so for these reasons you can see that that's why we should be storing data in sql server in the first place how do you access an sql server well first of all you need a data connection if you're using a work sql server then you will be given details of your server by your it department this will include the server name this can be taken from the connection string if you have it and the authentication method you'll be using evo windows authentication which uses your windows username and password or sql server authentication which uses a separate username and password now you can install sql server on your own computer for free but i would recommend only doing this if you're going to play about with it if you want other people to use it then i would suggest installing it on the central server you may have to get a paid for audition or you could perhaps use the express edition if not many people are going to be using it if you do have microsoft sql server on your computer then the server name could be localhost one word or maybe just a dot and you'll probably be using windows authentication so let's connect to sql server you can use this to retrieve the microsoft sql server data as you can see here i have got a database called adventure works 2014 and within that i have got lots of tables views and stored procedures so you can use this to retrieve sql server data but how can you use excel to do this well there are three different places in where you can load sql data first of all in the main excel window in the get and transform window also known as a power query editor and in the power pivot window also known as the data model we'll look at each of these places in turn in this video so first of all connecting sql to the main excel window so this is the main excel window and it used to be used methods like microsoft query however this has changed to load data from sql server now you go to data get data now if you've got excel 2016 you may see get data over here from database from sql server database as i say this has superseded previously used methods now there are four different sql server data sources that you might want to query firstly you might want data from a table for the raw data secondly you might want query results from a previously created view this is the result of an sql server data analysis that's been encapsulated stored as a view thirdly you might want the results from a stored procedure this could be a more complicated analysis or one that involves parameters for example you may just want old sales from the state of florida and you could use florida as a parameter fourthly you may wish to run an ad hoc sql query using the select statement first of all we need the server name so i'm going to go back into ssms going back to connect and there's a server name so i'm just going to copy this into the server very useful if you've got a really long server name now if you want to run a stored procedure or an ad hoc query then at this stage it says database optional but you would need to add the database name so let's get the database name as well so database name adventureworks 2014. then you would need to click on the advanced options and put in your stored procedure or an ad hoc query so if i was doing an ad hoc query i could do select star from and name of your table for instance so copy that in and then you would press ok but in this version let's assume i'm not going for that i'm going for table or query now when i click ok the next thing it will ask is are you using windows authentication or are using sql server authentication so whichever authentication you're using you need to select the details computer won't ask me that because i previously authenticated then if i'm getting the results of a table or query we can then select it so i'm going to select human resources dot v employee now we've got two buttons at the bottom load and transform data i'm going to have a look later and see what happens when i click on transform data for now i'm going to click on load so now i've made the link we have our table it's an excel table and i can use it just like other data stored in a table if the data changes in sql server it's very easy to refresh all i do is go to table design and refresh or i can right and click and go to refresh it will then reload the data so this is one way of doing it so i'm just going to close the sheet and open up a new one so what happened if when i got this data i didn't click on load but i clicked on transform well this would get me to the get and transform window and here you can see the results you can then perform additional manipulations in getting transform also known as power query editor before the data is transferred into excel for example maybe i want to just show two of the columns no problem i can remove all the other columns maybe i want to add additional columns so let's add a new column so i'll go to add column and probably the easiest way to add a column is to go to column from examples from all columns and so i'll type what i want the results to be and the computer works out the formula from that so let's rename it as full name now maybe i don't want all of the roles either maybe i want to summarize the data so see how many people the same last name i've got well i can do this by going to the group by so i'm grouping the last name and i'm counting the number of rows so let's sort it you can see we've got some people with two or three instances but i don't want to do that so i'm going to get rid of these last steps you can see that this is a macro language the computer is taking the data it's removing other columns it's inserting a merge column and renaming it the advantage of doing it this way is that if you refresh the data the computer goes through all of these steps again you don't have to do them again and again and again additionally power query will try to reduce the amount of data that it needs to receive from sql server using a process called query folding you can see that it's not retrieving all of the data now it's just retrieving two of the columns and similarly if i said i don't want all of the rows i just want to keep the top 50 rows then power query will just retrieve the top 50. so only the needed rows and columns are retrieved from sql server in the first place they might be thinking no that's not true because it's all the way out here it's not there no sql server will work out that this top 50 should be implemented in here so this reduces network traffic and increases the speed of retrieving that data now we can now leave the power query by going to home close and load and the data would then be loaded into an excel table as before in this reduced form however if you go to close and load 2 instead then we've got options we can instantly create a pivot table or pivot chart without necessarily loading the data into excel as a table or we could just save it as a connection without loading the data at all so what's the point of that well if i wanted later to create a pivot table for example i could say i want to choose my query so by doing this it means that i don't need to load the data into excel it's one less spreadsheet that i need now i'm going to get rid of that and i'm going to edit this that gets me back into the power query window and if i go back to my connections i can also re-choose where i want it to load to and there is a check box for add this data to the data model if i click on that the data will be exported to power pivot also known as the data model so this is the third way of connecting sql to excel using the data model also known as powerpivot to do this we go to data and manage data model and you can see that our data has already been inserted but what if we wanted to insert data directly into there well we can go to home from database from sql server and then it's a fairly similar model will be that the dialog box looks a bit different so i can write a query or i can select a list of tables and views i can select what i want so let's go for the non-view version of employee and i can also select related tables so you can see it's selecting a few more for me click finish and the data is being imported there you go click close we can see all of these additional tables in there however in addition these tables are linked together how because we've got primary keys and foreign keys in sql server and i've asked them to bring in the related tables now once the data has been imported then i can do more so for instance maybe i want to go into the person table and join together first name and last name again i can do that so this will be full name and it's equal to first name and a space and last name so this would be an example of a calculated column additionally i can hide things so maybe i don't want to see the person type just right and click on it and go to hide from client tools and then when i go and create a pivot table by going to home pivot table pivot table i can use all of these different tables so i want to say okay this employee for all of the employees this is actually a not something that i should sum up i'm going to get from another table say salesperson the total sales year-to-date and now it is only showing those items which are in both tables so you can see the potential huge flexibility when using powerpivot well i hope you've enjoyed this video if you have why not click on the like and then subscribe and tick that bell next to it that means that you will be notified of any new videos are you interested in power query or powerpivot then why not join me for my analyzing and visualizing data with microsoft excel course where we'll have a look at pivot tables get and transform data also known as power query and creating a data model or powerpivot do you want to learn sql statements query then why not have a look at my sql server essentials in an hour of course here we'll be looking at the six principal clauses of the sql select statement select from where group by having an order by and i've also got an eight hour course and a 29 hour course if you want to go into more detail thank you very much for watching this video and keep learning you

Channel: SQL Server 101

Views: 1,195

Keywords: sql server, sql, microsoft sql server, sql server learn, sql server videos, t-sql, tsql, load sql into excel, sql data into excel, sql into microsoft excel, import sql, import sql into excel, import sql server into excel, sql server into excel, add sql into excel

Id: N-WiLT6JQes

Length: 14min 51sec (891 seconds)

Published: Wed Apr 07 2021