How to export data from sql server to excel using import export wizard
How many times we have the need to export our data in SQL Server Database to Excel for some manipulations or any other stuff. I guess many times? So how do we achieve this?
There are two ways of achieving this. One is using the inbuilt option of Import Export feature in SQL Server using SQL Server Management Studio and other way around is using SQL Server Integration Services (SSIS) which is a bit robust and customisable compared to Native Import Export Wizard.
Here, I am explaining on How to export data from sql server to excel using import export wizard using below step by step Instruction.
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server Instance from where you want to export the data to Excel
2. Once you are connected to SQL Server Instance, Expand Databases, Right Click on Database from which you want to Export Data, Go to Tasks and Click on Export Data.
3. SQL Server Import and Export Wizard appears, click Next.
4.In Choose a Data Source screen, select the Data source, Server Name and Database from which you want to export the data.
5. Under Choose a Destination screen, Select Microsoft Excel in Destination
6. Under Excel connection settings, in Excel file path select browse, provide a excel file name and click open
7. Now the screen should look something similar to below and click next.
8. Under Specify table copy or query screen, you can select either of the option, copy data from one or more tables or views or write a query to specify the data transfer to. Here I am selecting the second option as I just want 4 column data from a single table. You can wirte your customised queries here as well including joins. Now click Next
9. Under Provide a Source Query, write your query and click next.
10. Under Select Source Tables and Views, accept the default values or Provide a Custom Name for Excel Tab. Now click on Edit Mappings if you want to cross check something or want to do some manipulations and click ok and click next.
11. Under Review Data Type Mapping, review the data and click on Next.
12. Under Save and Run Package, accept default or click optional Save SSIS Package if you want this to use again sometime. If you dont select Save SSIS Package option, you will be sent to step 14 directly. Click Next
13. Under Save SSIS Package, provide a name for SSIS Package, Description and click on Browse, to set location where you want to save package and click next.
14. Under Complete the Wizard screen, click finish to complete export.
15. Click on Close to complete and close wizard.
16. Now go to location where you saved excel file and package. You can see that a SSIS Package is created and a ExcelFile with your data.
Hope the article was helpful.
Now information on second method of exporting data using SSIS can be found here: How to Export data from SQL Server to Excel Using SSIS