In this post, let us see new approach to import excel into SQL Server and export SQL server data to excel. There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked server & OPENROWSET query and SQLCMD.
BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly
BCP - Work around has to be done to include the header
SSIS - With dynamic source & destination, handling mapping increases the complexity of the package
SQLCMD - Cannot export output in Excel
R & Python language extension was introduced in SQL Server 2016 & 2017 as part of machine learning. With support of R in Azure SQL database and Java language extension support in SQL Server 2019, this new approach can be used extensively as it easy, fast and flexible.
BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly
BCP - Work around has to be done to include the header
SSIS - With dynamic source & destination, handling mapping increases the complexity of the package
SQLCMD - Cannot export output in Excel
R & Python language extension was introduced in SQL Server 2016 & 2017 as part of machine learning. With support of R in Azure SQL database and Java language extension support in SQL Server 2019, this new approach can be used extensively as it easy, fast and flexible.