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

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

    Wednesday, May 1, 2019 4:46 AM

Answers