Solutions:
- HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows was set to 1. You can change it to a larger value and it now work.
- You might consider using ODBC instead of OLEDB.
Sample code:
Imports System.Data.OleDb
Imports System.Data.Odbc
Module ImportExcel
Sub Main()
DoJob1()
End Sub
'Private Function GetData() As OleDbDataReader
' Dim cnAdo As New OleDbConnection()
' Dim cmCommand As New OleDbCommand()
' Try
' cnAdo.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES';Data Source=E:\\test.xls;"
' cnAdo.Open()
' cmCommand.Connection = cnAdo
' cmCommand.CommandText = "SELECT * FROM [Sheet1$]"
' cmCommand.CommandType = CommandType.Text
' Return cmCommand.ExecuteReader(CommandBehavior.CloseConnection)
' Catch ex As Exception
' If cnAdo.State = ConnectionState.Open Then
' cnAdo.Close()
' End If
' Throw ex
' End Try
'End Function
'Private Sub DoJob()
' Dim drExcel As OleDbDataReader = GetData()
' Try
' Dim dc As New Enware_eStoreDataContext()
' Dim count As Integer = 0
' While drExcel.Read()
' If drExcel("ProductID") IsNot DBNull.Value Then
' Dim p As eStore_Product = dc.eStore_Products.FirstOrDefault(Function(s As eStore_Product) s.ID = CInt(drExcel("ProductID")))
' If p IsNot Nothing Then
' p.Name = drExcel("Name").ToString()
' p.Description = drExcel("Description").ToString()
' p.Image = drExcel("Image").ToString()
' p.Keyword = drExcel("Keyword").ToString()
' p.GroupCode = drExcel("GroupCode").ToString()
' p.Brochure = drExcel("Brochure").ToString()
' dc.SubmitChanges()
' Else
' Exit While
' End If
' Else
' Exit While
' End If
' count = count + 1
' End While
' Console.WriteLine("finished! - " + count.ToString() + " rows processed!")
' Console.ReadLine()
' Catch ex As Exception
' Console.WriteLine(ex)
' Console.ReadLine()
' Finally
' If drExcel IsNot Nothing Then
' drExcel.Close()
' End If
' End Try
'End Sub
Private Function GetOdbcDataReader() As OdbcDataReader
Dim con As New OdbcConnection()
Dim cmd As New OdbcCommand()
Try
con.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=E:\\test.xls;"
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT * FROM [Sheet1$]"
cmd.CommandType = CommandType.Text
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
If con.State = ConnectionState.Open Then
con.Close()
End If
Throw ex
End Try
End Function
Private Sub DoJob1()
Dim drExcel As OdbcDataReader = GetOdbcDataReader()
Try
Dim dc As New Enware_eStoreDataContext()
Dim count As Integer = 0
While drExcel.Read()
If drExcel("ProductID") IsNot DBNull.Value Then
Dim p As eStore_Product = dc.eStore_Products.FirstOrDefault(Function(s As eStore_Product) s.ID = CInt(drExcel("ProductID")))
If p IsNot Nothing Then
p.Name = drExcel("Name").ToString()
p.Description = drExcel("Description").ToString()
p.Image = drExcel("Image").ToString()
p.Keyword = drExcel("Keyword").ToString()
p.GroupCode = drExcel("GroupCode").ToString()
p.Brochure = drExcel("Brochure").ToString()
dc.SubmitChanges()
Else
Exit While
End If
Else
Exit While
End If
count = count + 1
End While
Console.WriteLine("finished! - " + count.ToString() + " rows processed!")
Console.ReadLine()
Catch ex As Exception
Console.WriteLine(ex)
Console.ReadLine()
Finally
If drExcel IsNot Nothing Then
drExcel.Close()
End If
End Try
End Sub
End Module
Reference:
