How to Upload xlsx 2016 into MSSQL 2017 using Stored Procedure in asp.net page?

Question

User-1640542475 posted

Hi 

I have an issue that how to upload excel 2016 xlsx file into mssql 2017 server
Previously I use excel 2010 and Mssql 2012. The code is working fine (Given Below)

Now our client upgrade Office 2016 and MSSql server 2017 and VS 2017

I have changed to 

<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.16.0; Data Source={0};Extended Properties='Excel 16.0;HDR={1}'"/>

DECLARE @SQL nvarchar(1000)
SET @SQL = 'TRUNCATE TABLE ' + @TABLENAME
EXEC(@SQL)

    IF OBJECT_ID (@TableName,'U') IS NOT NULL
      SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
    ELSE
      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
 
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.16.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 16.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL

Error is 

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".
OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" returned message "Could not find installable ISAM.".

Pls Advice Me

Thank you

Maideen

Web Config

<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

Stored Procedure

<div>

ALTER PROCEDURE [dbo].[ZR_usp_ImportFromExcel07]
   @SheetName varchar(20),
   @FilePath varchar(100),
   @HDR varchar(3),
   @TableName varchar(50)
AS
BEGIN
    
	DECLARE @SQL nvarchar(1000)
	SET @SQL = 'TRUNCATE TABLE ' + @TABLENAME
	EXEC(@SQL)

    IF OBJECT_ID (@TableName,'U') IS NOT NULL
      SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
    ELSE
      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
 
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.16.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 16.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL

Also</div> <div></div> <div>

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

</div> <div></div>