How to Compare every record in Excel file and save to Database Table.

Question

0.00/5 (No votes)

See more:

Hi,

I have my application in VS2008 and coded Vb.net.

My query is i want to save record from an excel sheet to my Database Table.

But i dont want to insert the Excel sheet as a whole rather i want to insert the record in my database table Row wise.Inserting the records is not the only thing but my major issue is i want to check and compare the Primary Key column of my Table with the Column in my Excel.

Suppose Table1 is my Sql Database Table,and RegistrationNo is the Primary Key Column.Similarly there is also a column of Registration Number in my Excel Sheet.

Now before inserting each record the RegistrationNo column should be compared.If the a registrationNo in the excel sheet say RegistrationNo 112 is already present in the table then it should not Insert the record and if its not present then it should insert.

Also the other issue is the number of columns in my Database table are more than the number of columns in my excel Sheet.So the columns that are not present in the excel sheet should be set to NULL while inserting the record in the Table.

Please help me with a link of similar example or any helpful code to do this.

Posted 31-Oct-11 21:34pm

Member 8121187

Add a Solution

Comments

Sergey Alexandrovich Kryukov 1-Nov-11 11:45am

What do you mean by "record" in Excel?
--SA

Maciej Los 1-Nov-11 13:26pm

How do you want do this: manually (query in SSMSE) or programmatic?

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 · 2011-11-03T08:07:00

First of all you need to know how to create OleDb.Connection[^] for SQL Server OLE DB Provider.

The second one is to know how to create query:

SQL

INSERT INTO [A_TEST].[dbo].[Regs] (RegistrationNo, Column_2, Column_3, Column_4, Column_5, Column_6, Column_7, Column_8)
SELECT PT.RegistrationNo, PT.[Column 2] AS Column_2, PT.[Column 3] AS Column_3, PT.[Column 4] AS Column_4,
        PT.[Column 5] AS Column_5, PT.[Column 6] AS Column_6, PT.[Column 7] AS Column_7, PT.[Column 8] AS Column_8
FROM (
    SELECT *
    FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\Regs.xls', 'SELECT * FROM [Sheet1$]') AS DT
    WHERE [RegistrationNo] NOT IN (SELECT RegistrationNo FROM A_TEST.[dbo].[Regs])
    ) AS PT

In my example:
- database: A_TEST
- table: Regs
- columns: RegistrationNo, Column_1, Column_2,..., Column_8
As you see i use 4 times SELECT and 1 times INSERT command. First SELECT command is used to get data from PT (alias) and to set names for column-headers (if in the destination table they are different from source table). The second SELECT command is used to get data from MS Excel sheet and the third (in WHERE clause) is used to exclude existing RegistrationNo.

Before you start you need to read more about OPENROWSET[^].

Your question is not enough clear, so i can't help you more.