How to export data from the SQL Server Management Studio into Excel file?
I used to save the data into .csv file and transfer the file out into my local machine via FTP or use the copy and paste command to transfer the data into the Excel directly from the database. This happened when you have control on the servers and security is not a big concern. Another method is using the SSIS.
Due to various reasons, my client did not allow installation of Excel into the database server and recommended me to use the Export Data function in the SSMS. I seldom used the Export Data function, anyway. Instantly, I gave a try after I was guided.
The steps are simple, guided and straight forward to follow. Here is the step:-
1. Right click the database > Task > Export Data.
2. Choose a data source. It means where the source location is. For this example, we select SQL Server Native Client 11.0. Enter the user credentials if you are using SQL authentication, otherwise, you can just continue with Windows Authentication.
Select the database which you want to export the data from.
3. Choose a destination. It means select the location where you want to save or export the data to. Destination selection, we choose Microsoft Excel, then enter the Excel file path where you want to save the file at. As for the Excel version, I believe it is based on the driver version we installed. If you want to keep the first row with column names, please check the checkbox.
Then, we can proceed with Next button.
4. Specify table copy or query. Here, we can choose either to select which table or tables we want to copy all its data into the Excel or we can write a SQL query to specify the dataset to be copied or exported. Let see how we can export a table out from the SSMS.
Choose the first option, Copy data from one or more tables or views. This allows copying the entire dataset from the selected tables or views into the Excel file. Select the tables by checking the checkbox at the Source.
The destination will name the Excel sheet according to the table’s name. If you want to change the name, you can click on each destination table’s name and edit it. If you do not want to export the entire table, you can select to write a query by choosing the second option from the previous screen.
Write your SQL statement in the next screen, just like below, which I selected top 1000 rows of records from the selected table. You can rename the table name at the Destination similarly to what I describe earlier in the next screen.
The table name will appear in the Excel sheet. One sheet for one table and as you know Excel has maximum number of rows, 65,536 for Excel 2003 and 1,048,576 for Excel 2007. I believe there is no concern about maximum column.
Next, we move to the Edit Mappings… button which is important to look into it especially when we want to format the Excel’s cell format.
In the Edit Mappings, you can select the data type of each field by clicking the dropdown selection list under the Type column. And, click OK to save the mappings. Then, you can view the dataset by clicking on the Preview button. A limited row of data will display in the next screen.
After editing the mappings and preview the dataset, it is time to review the data type mapping
5. Review Data Type Mapping. Click Next button to proceed and run the package.
6. Run Package. Nothing much you can choose, either run immediately or cancel the process. Click Next or Finish button to complete the wizard, it will run the package and save data into the Excel file.
Lastly, navigate to your Excel file path to retrieve the Excel file and check the data inside it.