We can use three common file formats to export SQL Server data to Excel:
- Native Microsoft Excel workbook
- CSV file
- XML file
Only SQL Server Integration Services support export to Microsoft Excel workbook.
SQL Server Express Edition does not allow saving an SSIS package so you can use this way only if you have SQL Server Standard or higher.
Microsoft Excel users can open CSV file the same way as a native Excel file.
So, exporting to CSV files is suitable for most cases, and you can use a simple command line utilities instead of SQL Server Integration Services.
The disadvantage of the exporting to Excel workbooks or CSV files is that users receive new files every time and lose their changes.
XML can be more suitable as a user can import XML file to an Excel workbook once and then refresh the data from new files.
The articles in this section describe three export techniques and contain a working code for a quick start.
- SQL Server Export to Excel using SSIS
- SQL Server Export to Excel using bcp/sqlcmd and CSV
- SQL Server Export to Excel using bcp/sqlcmd and XML
SQL Server Import to Excel
Microsoft Excel has features that allow connecting to SQL Server and SQL Azure, importing and refreshing data.
However, Microsoft Excel data features are poor really.
You may add powerful features with Microsoft Excel add-ins.
Without any coding you can:
- Connect to stored procedures the same way as tables and views.
- Change a group of connection strings at once.
- Change an Excel table query using the ribbon.
- Change values of stored procedure parameters using the ribbon.
- Use Excel custom views for database queries.
With a little SQL Server coding you can:
- Use stored procedures as Excel event handlers to implement the drill-down feature.
- Use translation views to translate Excel table column names to business language dynamically.
- Use translation views to translate database object names to business language.
- Use configuration views to filter database objects available to users.
See details here: