Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-16
6
Medium Priority
?
996 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 1500 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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 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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

579 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