Solved

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

Posted on 2008-10-16
6
986 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

770 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