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
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.
The article contains descriptions and useful tips for two main steps of SSIS use:
Also, you may download a configurable batch file to run multiple SSIS packages.
Read more: Excel Import to SQL Server Using SSIS
The article contains descriptions and useful tips for two main steps of SSIS use:
Also, you may download a configurable batch file to run multiple SSIS packages.
Read more: SQL Server Export to Excel Using SSIS
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
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
The article contains descriptions and useful tips for two basic steps:
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
The article contains descriptions and useful tips. The main step:
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
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
You may use native Excel features (Data, From Other Sources) to import SQL Server data into Microsoft Excel.
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
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
Using Microsoft Excel add-ins, you can add powerful features.
Without any coding you can:
With a little SQL Server coding you can:
Read more: SQL Server Import to Excel Using Excel Add-Ins
Just install SaveToDB Add-In and you can:
With a little SQL Server coding you can:
Read more: Excel-SQL Server Import-Export Using Excel Add-Ins