Solved

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

Posted on 2008-10-16
6
987 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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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