In our last blog post – seen here – I showed one way of getting data into an Excel spreadsheet. If you attempted it, you saw how many clicks there. There’s lots of clicking with the wizard.
Now, let’s try doing it a different way.
First, let’s open up the one tool that you’ll really need for this – Excel.
Once you click on the Data tab across the top, you should get this view:
Click on “Get Data” – on the far left-hand side of the screen. Click on “From Database” and “From SQL Server database” and you’ll get the following prompt:
Type in the name of your SQL Server and database in the corresponding prompts (the Warning Symbol goes away once a recognized SQL Server is input) and after some necessary credential information is verified, you get to the following screen:
Notice here that you see all of your existing tables and views in the database you have selected. Select the one that you want to get data from – it will show a preview on the right-hand side to make sure it’s what you truly want.
Click load and boom! You have your data!
Far fewer clicks and we get our data – no muss, no fuss.
Now, there are a few things to remember with this method
- If you are using Azure or some other database, you’ll need to click the correct method under “Get Data” to get the data you want. Notice that if your data is in Azure (Microsoft’s Cloud Platform), it can be found at “From Azure” and then “From Azure SQL Database”
- If you have an earlier version of Excel, you still won’t be able to get more than the number of rows it allows per tab. Even in the latest versions, if there are 2 million rows of data, you won’t be able to fit it into 1 tab. (Please be careful not to do this as your DBA will be upset with you.)
I cannot mention enough that you should have a good idea of how many rows you’ll have in each table you’re exporting before attempting to export to Excel.
And with that, we have come to the end of our time together for this post. Next time we’ll go into yet another way to export data to Excel from SQL Server. Until then!