Solved

How to Use .NET SqlBulkCopy to Import an Excel File into SQL Server

Posted on 2008-10-16
6
990 Views
Last Modified: 2008-10-28
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.UserID, frmLogin.oCon.UserPassword)
        Dim connectionString As String = sCon


        Dim l_strConn As String = String.Format("{0}{1}{2}{3}", _
                                               "Provider=Microsoft.Jet.OLEDB.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(connectionString)
        ' open the connection
        destinationConnection.Open()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection.ConnectionString)
        bulkCopy.BatchSize = 500
        bulkCopy.NotifyAfter = 1000
        bulkCopy.DestinationTableName = "BulkTest"
        bulkCopy.WriteToServer(reader)
        reader.Close()
    End Sub

Any suggestions or does someone have a good example?
Thanks,
Denise
0
Comment
Question by:DeniseGoodheart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 19

Accepted Solution

by:
elimesika earned 500 total points
ID: 22737390
Try to replace connection string with this
 Dim l_strConn As String = String.Format("                                            Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1};", pFilename, "Excel 8.0;HDR=Yes;IMEX=1");

Open in new window

0
 

Author Comment

by:DeniseGoodheart
ID: 22741842
Hello elimesika:

I tried your suggestion and now I get the following error:
Could not find installable ISAM.

Any Suggestions?
Thanks,
Denise
0
 

Author Comment

by:DeniseGoodheart
ID: 22743675
I also tried to register my msexcl40.dll and still got the error.
0
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 19

Expert Comment

by:elimesika
ID: 22749299
0
 

Author Comment

by:DeniseGoodheart
ID: 22753071
Hello elimesika:

I already tried this process with no success by registering and verifying the msexcl40.dll was in the proper place.
0
 

Author Comment

by:DeniseGoodheart
ID: 22827187
I got it to work by adding additional quotes as follows:

Dim l_strConn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1};", pFilename, """Excel 8.0;HDR=Yes;IMEX=1""")

0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question