Help me find the leak in this excel import code

Question

The following method of course works, but after a certain number of uploads (this is not constant) my client gets the dreaded error: "System.Data.OleDb.OleDbException: Unspecified error"

Steps:

1. Client uploads an excel file via a File upload control
2. File is saved to the file system
3. File is opened via the oledb provider and read into a dataset

My only guess is that the provider is somehow not releasing resources.

The only way to clear this up (temporarily) is to reset IIS. For that reason, I'm inclined to think that the provider can get locked up by other websites on this server. We do host one site for a client (we did not build) that makes use of this provider, so it's possible that there is an issue on their end. Can anyone comment on this?

Please take a look at the method below and help me get rid of this problem!

Public Shared Function GetExcelData(ByVal excelFile As String, ByVal sheetNumber As Integer) As DataSet

    Dim connString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFile)
    Dim excelDataSet As New DataSet()

    Using conn As New OleDbConnection(connString)
        conn.Open()
        Using dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            Dim excelSheets(dt.Rows.Count) As String
            Dim i As Integer = 0
            For Each row As DataRow In dt.Rows
                excelSheets(i) = row("TABLE_NAME").ToString
                i += 1
                If i = sheetNumber Then
                    Exit For
                End If
            Next

            Using excelCommand As New OleDbCommand("Select * from [" & excelSheets(sheetNumber - 1) & "]", conn)
                Using excelAdapter As OleDbDataAdapter = New OleDbDataAdapter(excelCommand)
                    excelAdapter.Fill(excelDataSet)
                End Using
            End Using

        End Using
        conn.Close()
    End Using

    Return excelDataSet

End Function

Answer 1

I don't know what's causing your error, but I do know that given the code you've shown there's no point looping through all the sheet records in your datatable. Also, you can group Using statements to avoid deep nesting and the .Fill() method has overloads that allow you to specify the name of the table or even fill to a table directly rather than using a whole dataset. Either of those would seem a better choice than just returning a dataset with a single unnamed table.

Dim sheetName As String = dt.Rows(sheetNumber)("TABLE_NAME")

Using excelCommand As New OleDbCommand( _
   String.Format("Select * from [{0}]", sheetName), conn), _
  excelAdapter As OleDbDataAdapter = New OleDbDataAdapter(excelCommand)

    excelAdapter.Fill(excelDataSet, sheetName)

End Using

As for your specific problem, my best guess is that there's some invalid character in the file name that's causing problems with the connection string.

Answer 2

Just after comparing with my connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelDataFile + ";Extended Properties=\"Excel 8.0;ImportMixedTypes=Text;HDR=YES;IMEX=1;\"

although I'd guess the \" are not necessary.

Answer 3

Is this SO post any use: C#/ASP.NET Oledb - MS Excel read “Unspecified error”

Answer 4

It seems that there is no real solution using the oledb provider.

So, I'm going to have the client convert their spreadsheet into a CSV (it's possible in this case as it's a single sheet tabular import).

Then, I found a great CSV parser here:

http://www.codeproject.com/KB/database/CsvReader.aspx