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

TechnologySQL Server SideExcel Side
SQL Server
to Excel
Excel to
SQL Server
SQL Server
to Excel
Excel to
SQL Server
SSISyesyes
bcp/sqlcmd utilitiesas CSV/XML
Linked servers yes
Distributed Queries yes
OleDB-SQL utility yes
Native Excel yes
VBA yesyes
VSTO yesyes
Excel Add-Ins yesyes

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:

  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 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:

  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 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:

  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

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

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 QueryTableExcel Range
SQL Server data import to Excelyesyes
SQL Server data refresh in Excelyesyes
Excel data export to SQL Serveryesyes

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 QueryTableExcel RangeVSTO ListObject
SQL Server data import to Excelyesyesyes
SQL Server data refresh in Excelyesyesyes
Excel data export to SQL Serveryesyesyes

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.

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

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