Welcome!

Welcome Microsoft Excel and SQL Server Developers!

Here you may find useful tips and download 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.

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:

1. Create an SSIS package using SQL Server Business Intelligence Studio or Import-Export Wizard.
2. Run an SSIS package using SQL Agent or dtexec command line utility.

Also, you may download a configurable batch file to run multiple SSIS packages.

Read more: Excel Import to SQL Server Using SSIS

To top

SQL Server Export to Excel Using SSIS

The article contains descriptions and useful tips for two main steps of SSIS use:

1. Create an SSIS package using SQL Server Business Intelligence Studio or Import-Export Wizard.
2. Run an SSIS package using SQL Agent or dtexec command line utility.

Also, you may download a configurable batch file to run multiple SSIS packages.

Read more: SQL Server Export to Excel Using SSIS

To top

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 may download a configurable batch file to run multiple CSV export tasks.

Read more: SQL Server Export to Excel Using bcp/sqlcmd and CSV

To top

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 may download a configurable batch file to run multiple XML export tasks.

Read more: SQL Server Export to Excel Using bcp/sqlcmd and XML

To top

Excel Import to SQL Server Using Linked Servers

The article contains descriptions and useful tips for two basic steps:

1. Add a linked server to Excel workbook.
2. 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.12.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

To top

Excel Import to SQL Server Using Distributed Queries

The article contains descriptions and useful tips. The main step:

1. 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.12.0',
   'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
   'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

Read more: Excel Import to SQL Server Using Distributed Queries

To top

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 may download source and binary codes and configurable batch file to run import tasks.

Read more: Excel Import to SQL Server Using OleDB-SQL Utility

To top

Excel Side Technologies for SQL Server Data Import-Export

SQL Server Import to Excel Using Native Excel Features

You may 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

To top

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

To top

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

To top

Excel-SQL Server Import-Export Using Excel Add-Ins

Just install SaveToDB Add-In and you can:

• Connect, edit data and save changes to a database table.
• 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 INSERT, UPDATE and DELETE stored procedures for Excel data changes to update any database data.

Read more: Excel-SQL Server Import-Export Using Excel Add-Ins

To top