Comprehensive Guide to Excel and SQL Server Integration

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
Technology	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.

The sample shows how to load data using a stored procedure and save changes back to a database with the SaveToDB add-in

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.

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.

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.

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.

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$]')

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$]

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.

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

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

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.

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.

The sample shows how to load data using a stored procedure and save changes back to a database with the SaveToDB add-in