Use Excel to Connect to SQL Server Data

all right this is anthony smoke data plus analytics let me go ahead and show you what i'm going to show you here first so i'm in excel and i have this uh this data set i'm going to show you that i've got a hundred rows in here as you can see and column wise i go out to column s right so let's say i do something like this let's say i delete so many rows we're gonna go ahead and delete these and let's say i delete so many columns right let's go ahead and delete these as well all right so now i can do this i can go here to data and i can say refresh and magically my 100 rows come back and then i'm out to column s again so how am i doing this how am i doing this refresh i am connected to sql server within excel so i've got a direct connection to sql server within excel that allows me to always refresh that data in excel uh i can copy it out to another sheet and do what i need to do but i don't have to export it out into a csv then load it into excel or control copy out of sql server if i got a million rows and 50 columns right that's going to blow up my clipboard right so i want to do that direct connection i'll show you how to set this up coming right up [Music] all right thanks for tuning in everyone this is anthony smoke go ahead and check me out on anthonysmoke.com definitely hit subscribe here on youtube as always if you learned something go ahead hit that like button leave a comment and make sure you ring the bell so you get a notification when i drop a new video you can also follow me on twitter at anthonysmokes okay so the use case for this type of refresh is going to be for ad hoc purposes if you need to easily refresh your data in excel for ad hoc purposes only do not use this to put production um you know worksheets uh out there for people to consume i'm talking to you financial analysts i know you love your excel and i know that you uh like to take manual inputs from any and everywhere so don't do it uh financial analysts i i'm just i'm just picking with you but uh let me show you how we set this up i'm going to go into sql server and so i'm using wide world importers dw this is a sample database from microsoft that you can uh yeah you can do a google search on this and import this into uh into sql server but i'm taking a look at this fact dot order table you can see i have so many columns here and so if i do a select star if you know your sql i'm just saying select uh all the columns and rows from fact dot order will execute this and let's take a look at my row count 231 412 rows so let's say i needed these in excel and then let's say next week when it refreshes this table i need to refresh this into excel because maybe i have some ad hoc process set up where i reference this data again not production okay so now let's go into uh excel i've got a blank excel here first of all let me sorry let me go back here let me just show you what what version of sql server i'm using here so sql server management studio version 18.11.1 so you can see all that good stuff here and now let me go into a blank copy of excel and so we're going to uh be on the data pane here the data ribbon i'm going to go to from database from sql server database and then it's going to ask for uh the server name and so where do i get that server name so what you can do if you don't know if you have access to sql server great so i'm just going to go here to connect to database engine and it's going to bring up my local server name i can copy that if you need to reach out to your dba if you're at work and ask for the um the server name if you need to set this up so we're going to go in here and say server and then for the database i'm using uh y uh wide world importers dw so let's go ahead and put that in here and then let's not mess with the uh advanced options you can put a sql statement in here so we'll come back to this but for now let's just go ahead and keep the default options that are selected i'm gonna say okay and then it's gonna work its magic here so okay so i get the navigator it's gonna work its magic here in a second after i select uh fact dot order and you'll see it gives me a little preview of the data i can check it out it's gonna show like these uh these value value fields but don't worry it should come in correctly it will come in correctly if i hit transform data that's gonna bring up power query where i can do some transformations again i'll show you a little bit more about that later but for now let's just hit load now it's going to work its magic it's going to create a connection a query and you can see it's loading rows and we should have 231 412 just like we had uh in sql server so we can confirm that i'm going to go all the way down you can see i've got 231 412 in the order key that's good so let's uh let's go back up and then i come over to column s my lineage key is the last column so that looks correct so now i have this data in here and that's good so i can i can take this data i can copy it somewhere but let's what do i need to do if i want to edit the connection what do i need to do if i need to edit the connection let me show you that here in a second it seems like it would be straightforward but microsoft makes it a little hard to find so i'm going to show you that here in a second stick around okay so let's assume that you are sql savvy or sql savvy like i am and you need to make some edits to the query um in sql so let's go back over to the database and instead of pulling in all 231 000 rows i'm going to run this query it gives me select top 100 from the table between this date for the order date key that's january 1st 2013 to january 10 2013 and i just need these hundred rows right i want to edit the query uh with this sql so how do i do that uh in in excel so first thing i'm going to do well let me show you the wrong way first the way that i assumed you would do it so let's go ahead and uh queries and connections let's bring that up and then you can select let's go to properties and let's hit this right here query properties and you'll see there's a definition and there's text here select star from fact order so great but i can't edit this text i can't even hit this button this is grayed out so that's that's very annoying so that's why you follow me so you get these little tidbits how do you make that uh that change right because this seems like the intuitive way to do it properties on the connection no microsoft makes it a little tougher for you to find that's why you're here so let's go ahead and cancel out of this and what i'm going to do i'm going to right click here i'm going to say edit and that's going to bring me into power query right and so first thing i'm going to do on the source i'm going to select this gear that's the key this gear right here and if i go into advanced options you'll see there's an area for my sql statement great so i can come in here i'm going to copy that and paste it into here say okay it's gonna crank for a little bit and you'll see i've got all of my columns with the data that comes in and do i have my hundred rows if i scroll all the way down i've got a hundred rows so that is how you edit the the sql statement um applied to the query it's kind of hidden in here you have to know like hey um let me hit this gear so i don't know why they make it as difficult to find out but but there it is and so let's get rid of this navigation step and so what we can do now let's close and load and it's going to load it into here i've got a hundred rows loaded and if i get rid of this you'll see if i scroll all the way down whoops going too far i guess i could hit ctrl end i've got my 100 rows come back up all right go over to s column s lineage key i have everything refreshed so that is how you edit the sql in the connection that we set up and just to show you let's say if i go in here i select everything and i hit delete it's going to you know ask me do i want to delete the associated query with the data and i'm going to say no right i'm just deleting the data and so what i can do i can say refresh and it's going to bring all of my data back again go down i got my 100 rows come back up everything is here out to uh out to column s so i can delete things here and always have it refreshed even if i change values so if i want to make another copy of this data i'm going to hit ctrl a here and make sure that i got the uh the column headers in here too so i hit ctrl a to to copy everything or to select everything i should say i'm going to hit control c and if i go to a new sheet and just paste or i could just say control v let's just do that i have a copy of the data but when i hit queries and connections you'll see it makes another query so this can also be refreshed this is not what i want right i just want i'm just going to control z out of this gets rid of that query what i need to do if i want a copy that's not connected to a query i'm just going to again hit control c and then i'm going to come in here i'm going to paste special values does that give me my headers it does and so now i have a copy of that data without it being connected to a query so if i close this and then i'm going to hit ctrl a in here hit ctrl a then ctrl t that's going to ask me do i want to create a table i do i have a table and so if i go over here if i go back to data and hit queries and connection that table is not associated with a query like my main copy is so i have this version that can always be refreshed and then i can copy that into a different sheet where i can do modifications and whatever i do here sticks right if i refresh it does not affect this sheet so you can do your modeling here you can do your you know whatever you need to do here and you always get that fresh copy in this sheet so very handy um again the use case here if you need to get that data out of sql server without the hassle of creating a csv importing it into excel you need that refreshed data and again we're not using this for production purposes looking at you financial analysts do not distribute excel workbooks as your production process this is for ad hoc modeling only so if you're a financial analyst you're mad at me go ahead and let me know in the comments i'm here for the i'm here for the smoke i want all the smoke i'm having smoke but anyway thanks for tuning in everyone hope you appreciated this tip it's just one of those little things editing the sequel that's a little hard to find that you may have to uh you know do some research to find but that is why you follow this channel so again does that mean smoke hope you learned something from this lesson here get out there do some great things with your data thanks for watching everyone [Music] you

Channel: Anthony Smoak

Views: 48,062

Keywords: Excel, excel tips and tricks, excel tutorial, learn excel, microsoft excel, microsoft excel tutorial, microsoft sql server, sql server tutorial, sql tutorial

Id: UzfYbWZr6ro

Length: 13min 51sec (831 seconds)

Published: Tue Mar 29 2022