Import data from Excel to SQL database where column data are equal

Question

0.00/5 (No votes)

See more:

Hi guys! Can somebody help me please because I currently have this code for importing Excel files to SQL Server database which work perfectly:

VB

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myTextBox.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        Dim expr As String = "SELECT * FROM [Sheet1$]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader
        Dim SQLconn As New SqlConnection()

 ExcelConnection.Open()

                    SQLconn.ConnectionString = conString
                    SQLconn.Open()

                    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
                        bulkCopy.DestinationTableName = "myTable"

                        Try
                            objDR = objCmdSelect.ExecuteReader
                            bulkCopy.WriteToServer(objDR)
                            objDR.Close()

                            SQLconn.Close()

                            MsgBox("Uploaded.", MsgBoxStyle.Information)

                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try

                    End Using

The output table in SQL database will be:

EMP. NO. / NAME / DEPT.
1234 / John Adams / NULL
4567 / Ryan King / NULL

Since in the uploaded table the DEPT. is NULL, what I need to do now is to import an Excel File with 2 columns only (EMP. NO. / DEPT.) where the existing EMP. NO. is defined already (1234), and the DEPT. will be inputted (say Accounting) which will be inserted in the table which MATCHES the employee numbers.

EMP. NO. / NAME / DEPT.
1234 / John Adams / Accounting
4567 / Ryan King / NULL

Can somebody help me how to revise my code for this? Thanks very much in advance! :)

Posted 22-Jan-13 22:38pm

awmantonio

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-02-11T21:04:00

Steps to do:
1) get the data from Excel into DataTable object (SELECT * FROM [Sheet1$] WHERE NOT [Dept.] IS NULL).
2) update data (you've got 2 ways)
a) (NOT recommended) go through the collection of DataTable.Rows and build command-string to update your data, for example:

VB

"UPDATE [YourTable] SET [Dept] = '" & DataTable.Rows(i).Item("Dept.").Value.ToString() & "'" & vbCr & _
"WHERE [Emp No.] = " & DataTable.Rows(i).Item("Emp No.").Value.ToString

b) (recomended) create a stored procedure to update data, using Dept. and Emp No. as an input parameters, for example:

SQL

CREATE PROCEDURE UpdateDept
    @DeptName NVARCHAR(30),
    @EmpNo INT
AS
    UPDATE [YourTable] SET [Dept.] = @DeptName
    WHERE [Emp No.] = @EmpNo
GO