Welcome!
Welcome Microsoft Excel and SQL Server developers!
Here you can find useful tips and working examples of Microsoft Excel and Microsoft SQL Server import and export techniques.
Sincerely,
Sergey Vaselenko
Excel-SQL Server Import-Export Technology Map
Technology | SQL Server Side | Excel Side | ||
---|---|---|---|---|
SQL Server to Excel | Excel to SQL Server | SQL Server to Excel | Excel to SQL Server | |
SSIS | yes | yes | ||
bcp/sqlcmd utilities | as CSV/XML | |||
Linked servers | yes | |||
Distributed Queries | yes | |||
OleDB-SQL utility | yes | |||
Native Excel | yes | |||
VBA | yes | yes | ||
VSTO | yes | yes | ||
Excel Add-Ins | yes | yes |
Take a look to Excel add-ins. These add-ins add powerful SQL Server data import-export features to Microsoft Excel.
For example, you may load data from tables, views, stored procedures, and save data changes back to target tables, views, or using specific stored procedures.
SQL Server Side Technologies for Excel Data Import-Export
Excel Import to SQL Server Using SSIS
The article contains descriptions and useful tips for two main steps of SSIS use:
- Create an SSIS package using SQL Server Business Intelligence Studio or Import-Export Wizard.
- Run an SSIS package using SQL Agent or dtexec command line utility.
Also, you can download a configurable batch file to run multiple SSIS packages.
Read more: Excel Import to SQL Server Using SSIS
SQL Server Export to Excel Using SSIS
The article contains descriptions and useful tips for two main steps of SSIS use:
- Create an SSIS package using SQL Server Business Intelligence Studio or Import-Export Wizard.
- Run an SSIS package using SQL Agent or dtexec command line utility.
Also, you can download a configurable batch file to run multiple SSIS packages.
Read more: SQL Server Export to Excel Using SSIS
SQL Server Export to Excel Using bcp/sqlcmd and CSV
The basic command:
sqlcmd -S . -d AzureDemo -E -s, -W -i ExcelTest.sql | findstr /V /C:"-" /B > ExcelTest.csv
You will find a complete solution to export data to CSV including the way to remove NULL values, and you can download a configurable batch file to run multiple CSV export tasks.
Read more: SQL Server Export to Excel Using bcp/sqlcmd and CSV
SQL Server Export to Excel Using bcp/sqlcmd and XML
The basic command for a file query using the sqlcmd utility:
sqlcmd -S . -d AzureDemo -E -s, -W -i ExcelTest.sql > ExcelTest.xml
The basic command for an inline query is different, and the bcp utility is used:
bcp "SELECT * FROM dbo02.ExcelTest FOR XML AUTO, ELEMENTS, ROOT('doc')" queryout ExcelTest.xml -S. -dAzureDemo -T -c
You will find a complete solution to export data to XML, and you can download a configurable batch file to run multiple XML export tasks.
Read more: SQL Server Export to Excel Using bcp/sqlcmd and XML
Excel Import to SQL Server Using Linked Servers
The article contains descriptions and useful tips for two basic steps:
- Add a linked server to Excel workbook.
- Use SELECT from the linked server or OPENQUERY function.
To add a linked server to Microsoft Excel 2003 workbook on 32-bit SQL Server use:
EXEC sp_addlinkedserver @server = 'ExcelServer1', @srvproduct = 'Excel', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'C:\Test\excel-sql-server.xls', @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'
To add a linked server to Microsoft Excel 2007/2010 workbook or on 64-bit SQL Server use:
EXEC sp_addlinkedserver @server = 'ExcelServer2', @srvproduct = 'Excel', @provider = 'Microsoft.ACE.OLEDB.16.0', @datasrc = 'C:\Test\excel-sql-server.xlsx', @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
To select data from a linked server use:
SELECT * FROM ExcelServer1...[Sheet1$]
To select data from a linked server using OPENQUERY function use:
SELECT * FROM OPENQUERY(ExcelServer1, 'SELECT * FROM [Sheet1$]')
Read more: Excel Import to SQL Server Using Linked Servers
Excel Import to SQL Server Using Distributed Queries
The article contains descriptions and useful tips. The main step:
- Use SELECT from OPENROWSET or OPENDATASOURCE functions.
To select data from Microsoft Excel 2003 workbook on 32-bit SQL Server use:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]
To select data from Microsoft Excel 2007/2010 workbook or on 64-bit SQL Server use:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
Read more: Excel Import to SQL Server Using Distributed Queries
Excel Import to SQL Server Using OleDB-SQL Utility
The article contains the solution to import Excel data to SQL Server using a command line utility implemented with C#. The core code of the utility:
sqlBulkCopy.DestinationTableName = sqlTableName; sqlBulkCopy.WriteToServer(oleDbDataReader);
You can download source and binary codes and configurable batch file to run import tasks.
Read more: Excel Import to SQL Server Using OleDB-SQL Utility
Excel Side Technologies for SQL Server Data Import-Export
SQL Server Import to Excel Using Native Excel Features
You can use native Excel features (Data, From Other Sources) to import SQL Server data into Microsoft Excel.
Excel-SQL Server Import-Export Using VBA
The article contains a working VBA example and description of two different import-export techniques: using Excel QueryTable and Excel Range objects.
Excel QueryTable | Excel Range | |
---|---|---|
SQL Server data import to Excel | yes | yes |
SQL Server data refresh in Excel | yes | yes |
Excel data export to SQL Server | yes | yes |
Read more: Excel-SQL Server Import-Export Using VBA
Excel-SQL Server Import-Export Using VSTO
The article contains a working VSTO solution for import-export using three different objects: Excel QueryTable, Excel Range, and VSTO ListObject.
Excel QueryTable | Excel Range | VSTO ListObject | |
---|---|---|---|
SQL Server data import to Excel | yes | yes | yes |
SQL Server data refresh in Excel | yes | yes | yes |
Excel data export to SQL Server | yes | yes | yes |
Read more: Excel-SQL Server Import-Export Using VSTO
SQL Server Import to Excel Using Excel Add-Ins
Using Microsoft Excel add-ins, you can add powerful features.
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 Ribbon.
- Change values of stored procedure parameters using 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 for users.
Read more: SQL Server Import to Excel Using Excel Add-Ins
Excel-SQL Server Import-Export Using Excel Add-Ins
Just install the SaveToDB add-in and you can:
- Connect, edit data and save changes to database tables.
- Export Excel data to a new database table using powerful Publish Wizard.
With a little SQL Server coding you can:
- Use stored procedures as Excel cell change event handlers to update any database data.
- Use specific INSERT, UPDATE and DELETE stored procedures to update database data with data changes maid in Excel.
Read more: Excel-SQL Server Import-Export Using Excel Add-Ins