如何比较Excel文件中的每个记录并将其保存到数据库表. [英] How to Compare every record in Excel file and save to Database Table.

问题描述

我在VS2008中有我的应用程序，并编码为Vb.net.

我的查询是我想将Excel工作表中的记录保存到数据库表中.

但是我不想整体插入Excel工作表，而是想将记录插入数据库表Row wise中.插入记录不是唯一的事情，但是我的主要问题是我想检查并比较我的表格和Excel中的列.

假设Table1是我的Sql数据库表，而RegistrationNo是主键列.类似地，我的Excel工作表中也有一个Registration Number列.

现在在插入每条记录之前，应该先比较RegistrationNo列.如果excel工作表中的registrationNo表示表中已经存在RegistrationNo 112，则它不应插入记录，如果不存在则应插入记录.

另外另一个问题是数据库表中的列数大于excel工作表中的列数，因此在将记录插入表中时，excel工作表中不存在的列应设置为NULL. br/>
请通过类似示例的链接或任何有用的代码来帮助我.

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.

推荐答案

首先，您需要了解如何创建

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:

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 位置 [RegistrationNo] 不 IN ( SELECT 注册号 FROM A_TEST.[dbo].[Regs]) ) AS PT

') AS DT WHERE [RegistrationNo] NOT IN (SELECT RegistrationNo FROM A_TEST.[dbo].[Regs]) ) AS PT

在我的示例中:
-数据库:A_TEST
-表格:Regs
-列:RegistrationNo，Column_1，Column_2，...，Column_8
如您所见，我使用4次SELECT和1次INSERT命令.第一个SELECT命令用于从PT(别名)获取数据并设置列标题的名称(如果在目标表中，它们与源表不同).第二个SELECT命令用于从MS Excel工作表中获取数据，而第三个(在WHERE子句中)用于排除现有的RegistrationNo.

在开始之前，您需要阅读有关 OPENROWSET [

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.

这篇关于如何比较Excel文件中的每个记录并将其保存到数据库表.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！