DeniseGoodheart
asked on
How to Use .NET SqlBulkCopy to Import an Excel File into SQL Server
Good Day:
I am using VB.NET 2005 with SQL Server 2000 to create a WinForms application. I need to automate the process of importing an Excel file into an SQL Server database table for many users. I created a test Excel file called test1.xls and created a range name for my Excel data called Quote. I get an error that reads: Login failed for user 'Denise'. I am confused because I can successfully log into my VB.NET/SQL Server application and perform all functions except for the following code:
Private Sub PerformBulkCopy()
Dim pRangeName As String = "Quote"
Dim pFilename As String = "C:\test1.xls"
sCon = oCon.SqlConString(frmLogin .oCon.User ID, frmLogin.oCon.UserPassword )
Dim connectionString As String = sCon
Dim l_strConn As String = String.Format("{0}{1}{2}{3 }", _
"Provider=Microsoft.Jet.OL EDB.4.0;", _
"Data Source=", _
pFilename, _
";Extended Properties=Excel 8.0;")
Dim l_Conn As New OleDbConnection(l_strConn)
l_Conn.Open()
'Create Objects and grab data
Dim Cmd As New OleDbCommand("SELECT * FROM " & pRangeName, l_Conn)
Dim reader As OleDbDataReader = Cmd.ExecuteReader
' open the destination data
Dim destinationConnection As SqlConnection = New SqlConnection(connectionSt ring)
' open the connection
destinationConnection.Open ()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationCon nection.Co nnectionSt ring)
bulkCopy.BatchSize = 500
bulkCopy.NotifyAfter = 1000
bulkCopy.DestinationTableN ame = "BulkTest"
bulkCopy.WriteToServer(rea der)
reader.Close()
End Sub
Any suggestions or does someone have a good example?
Thanks,
Denise
I am using VB.NET 2005 with SQL Server 2000 to create a WinForms application. I need to automate the process of importing an Excel file into an SQL Server database table for many users. I created a test Excel file called test1.xls and created a range name for my Excel data called Quote. I get an error that reads: Login failed for user 'Denise'. I am confused because I can successfully log into my VB.NET/SQL Server application and perform all functions except for the following code:
Private Sub PerformBulkCopy()
Dim pRangeName As String = "Quote"
Dim pFilename As String = "C:\test1.xls"
sCon = oCon.SqlConString(frmLogin
Dim connectionString As String = sCon
Dim l_strConn As String = String.Format("{0}{1}{2}{3
"Provider=Microsoft.Jet.OL
"Data Source=", _
pFilename, _
";Extended Properties=Excel 8.0;")
Dim l_Conn As New OleDbConnection(l_strConn)
l_Conn.Open()
'Create Objects and grab data
Dim Cmd As New OleDbCommand("SELECT * FROM " & pRangeName, l_Conn)
Dim reader As OleDbDataReader = Cmd.ExecuteReader
' open the destination data
Dim destinationConnection As SqlConnection = New SqlConnection(connectionSt
' open the connection
destinationConnection.Open
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationCon
bulkCopy.BatchSize = 500
bulkCopy.NotifyAfter = 1000
bulkCopy.DestinationTableN
bulkCopy.WriteToServer(rea
reader.Close()
End Sub
Any suggestions or does someone have a good example?
Thanks,
Denise
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I also tried to register my msexcl40.dll and still got the error.
ASKER
Hello elimesika:
I already tried this process with no success by registering and verifying the msexcl40.dll was in the proper place.
I already tried this process with no success by registering and verifying the msexcl40.dll was in the proper place.
ASKER
I got it to work by adding additional quotes as follows:
Dim l_strConn As String = String.Format("Provider=Mi crosoft.Je t.OLEDB.4. 0;Data Source={0};Extended Properties={1};", pFilename, """Excel 8.0;HDR=Yes;IMEX=1""")
Dim l_strConn As String = String.Format("Provider=Mi
ASKER
I tried your suggestion and now I get the following error:
Could not find installable ISAM.
Any Suggestions?
Thanks,
Denise