Is there a better way to connect to an Excel file

Question

User709978785 posted

I use the following connection code to connect to an excel file after it is uploaded, but if I use the connection to view the objcommand data in a gridview It will not release the connection until the session times out. So I'm having to wait 20 minutes to upload the file again.

So I'm wondering if there is a better way to connect to the Excel file that will not lock it up?

 

 Protected Function ExcelConnection() As OleDbCommand

        ' Connect to the Excel Spreadsheet .Jet.OLEDB.4.0
        '.ACE.OLEDB.12.0
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Server.MapPath("~/Import/ExcelImport.xls") & ";" & _
                    "Extended Properties=Excel 8.0;" 'HDR=NO;IMEX=1"

        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open()

        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
 
        Dim objCommand As New OleDbCommand("SELECT * FROM [Clients$]", objXConn)
        Return objCommand

    End Function

Answer 1

User-1946294156 posted

First of all, you need to close the connection.  Secondly, why are you connecting to the excel file?  Are you trying to upload it into a database, if so, why not skip this, and use SSIS (SQL Server Intregrated Services).  This is exactly what the tool is designed for.

Answer 2

User709978785 posted

I'm using the express versions of SQL Server, so I don't have access to the SSIS tool. Also the process is done from an asp.net page.