For the last few posts, I’ve been going on about various ways in which you can import data from spreadsheets into SQL Server. But, why should it only work one way? Why can’t we export data as well?
One of the ways that you can export data is by using our old friend – the Import/Export Data Wizard!
Next, you’ll select the database that has the data that you’ll be exporting.
Notice that I used the “Microsoft OLE DB Driver for SQL Server” as the data source this time. You are more than welcome to use any driver listed on your system – just make sure that it works for you and that you can connect to your database through whatever means necessary.
I put in the information that will allow me to connect to the database with my credentials and move on to connecting to an Excel spreadsheet.
Give the Wizard the path and filename you want your spreadsheet to have and be sure to change the Excel version if you wish…
Also, notice that I have the “First row has column names” selected. This tells the Wizard that I was the same header information that I have in SQL Server.
Click Next and you’ll come to the next screen – shown below.
Here, for our purposes, I’ll select “Copy data from one or more tables or views.” In the second option, you can write a query to get the data you need, but it’s extremely similar to this option – so I’ll show the easier one.
Here we have our list of tables and views in the database. For every one that we want, we’ll select them by clicking on the checkbox to the left of the name.
Now we have our Destination! This will be the name of the tab in the spreadsheet. From here, you can click next until you see Finish.
It should generate a spreadsheet with the data you’re very familiar with –
With this wizard, as it was with the Import Data Wizard, there could be issues.
- The version of SQL Server you’re using can have issues with the conversion of data from SQL Server to Excel – yes, there are multiple places this could go wrong.
- You can select too many tables to export – the max number depends upon the version of Excel you’re trying to export to.
- One table has too many rows for Excel to support – the max number depends upon the version of Excel you’re trying to export to.
Next time, we’ll go over another way to export data to a spreadsheet! Until then!