CODEWITHSUNDEEP
sql-serverexcelvba
user8645201
user8645201

Reputation:

Mass Export From Excel Into SQL Server 2014

Testing the connection error.

SQL.ActiveConnection = Cn
 FROM (OPENROWSET('Microsoft.Jet.OLEDB.4.0','Data    Source=C:\Path\To\File.xls;Extended Properties=Excel 8.0;HDR=YES', Sheet1$)) wb
 End Sub

Upvotes: 0

Views: 63

Answers (2)

Parfait
Parfait

Reputation: 107587

Consider an SQL Server distributed query with OPENROWSET to Excel workbook. Below assumes you have such permission to run MSSQL command and your Excel file has headers:

INSERT INTO [dbSomething].[dbo].[tbl_Test] ([MyFieldValue]) 
SELECT wb.ColumnName 
FROM (OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
   'Data Source=C:\Path\To\File.xlsx;Extended Properties=Excel 12.0;HDR=YES', Sheet1$)) wb

Alternatively for older workbook types:

INSERT INTO [dbSomething].[dbo].[tbl_Test] ([MyFieldValue]) 
SELECT wb.ColumnName 
FROM (OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=C:\Path\To\File.xls;Extended Properties=Excel 8.0;HDR=YES', Sheet1$)) wb

To run this action query, place entire SQL in a VBA string and execute off the connection. There is no need for parameterization and hence the ADO command object.

strSQL = "INSERT INTO ... wb"

Cn.Execute strSQL

Upvotes: 1

Ahmad Gozin
Ahmad Gozin

Reputation: 23

If you have SSIS, it would be better and quicker to go that route. Doing it this way will mean that it is no longer an export process; it will be an import will all tasks being performed on the SQL Server and in SSIS.

Upvotes: 1

Related Questions