Visual Basic Import Excel Sheet to DataTable

Question

I'm currently working with a huge amount of data in Excel, and I want uploaded into a DataTable in VB.Net (70,000 rows per 30 columns) with mixed datatypes.

I'm using the next code to import the information:

Public Function mc_ExcelTableToDataTable(ByRef ExcelApp As Excel.Application, _
                                          ByVal SheetName As String, _
                                          ByVal TableName As String, _
                                          Optional ByVal FilePath As String = "", _
                                          Optional ByVal SQLsentence As String = "") As DataTable


        Dim vPath As String
        Dim vCloseExcelWorkbook As Boolean = False

        If ExcelApp.ActiveWorkbook IsNot Nothing Then
            vPath = IIf(FilePath = "", ExcelApp.ActiveWorkbook.FullName, FilePath)
        Else
            vPath = FilePath
        End If

        If SQLsentence = "" And ExcelApp.ActiveWorkbook Is Nothing Then
            vCloseExcelWorkbook = True
            ExcelApp.Workbooks.Open(vPath)
        End If

        Dim vRange As String = ExcelApp.Sheets(SheetName).ListObjects(TableName).Range.AddressLocal

        vRange = vRange.Replace("$", "")

        Dim vCNNstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                    "Data Source= " & vPath & ";" & _
                                    "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

        Dim ExcelCNN As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(vCNNstring)

        Dim vSQL As String = IIf(SQLsentence = "", _
                                 "SELECT * FROM [" + SheetName + "$" & vRange & "]", _
                                 SQLsentence)

        Dim ExcelCMD As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(vSQL, ExcelCNN)

        ExcelCNN.Open()
        Dim ExcelDS As System.Data.DataSet = New DataSet
        ExcelCMD.Fill(ExcelDS)

        mc_ExcelTableToDataTable = ExcelDS.Tables(0).Copy

        ExcelCMD = Nothing
        ExcelCNN.Close()

        ExcelDS.mc_Dispose()

        If vCloseExcelWorkbook Then ExcelApp.ActiveWorkbook.Close(False)

        GCcleaner()
    End Function

But, VB.Net give me the following error:

err

Somebody knows what is the maximum capacity of the provider ACE.OLEDB.12.0? Or how fix this issue?

FYI, this is the SQL select sentence:

SELECT * FROM [Workflow data$A1:AC70276]

It's important mention that if I limit the rows to 20000 (SELECT * FROM [Workflow data$A1:AC20000]), the process works fine!

David W David W · Accepted Answer · 2012-09-17T01:09:29

Hmmm....have heard that there may, in some cases, be an old, artifact-style bug in the ACE.OLEDB.12.0 driver such that queries to Excel spreadsheets in excess of 60000 rows can generate strange errors like this...and while I cannot attest to this myself, I can't help but wonder what would happen if you modified your range size to the ol' 65536 row limit in old Excel, or something +/- say 5%, if the error would go away. Its particularly curious that you tried it with 20000 rows and it apparently worked.

EDIT I have thrown together some comparable C# code to test this scenario, and can only offer that queries approaching this size are creating inconsistent errors, ranging from "System resource exceeded" to hangs to "OutOfMemoryExceptions." While I can't say I've seen your precise error, I would say this behavior is starting to tax the importing process such that undesirable if not unpredictable behavior may ensue. I'd suggest breaking the data up into smaller chunks if at all possible.

Good luck.

Visual Basic Import Excel Sheet to DataTable

2 Answers