如何使用Excel更新数据库表值？ [英] How can i update database Table values using Excel?

问题描述

大家好，

i在sql server management中有一个表...名称为Product ..

产品表包含pid，pname，description，rate属性..

i在Product Table中有100条记录..在插入属性值时没有给出描述部分...

所以，现在我要插入那个描述属性价值..

i有100个产品的pid和描述的Excel ..

现在我需要一个脚本代码来使用Excel更新产品表中的描述，如果产品表PID等于Excel PID .....

解决方案

在Google中搜索将数据从Excel导入数据库到C＃。 />


您只需要阅读Excel文件，为每一行Excel运行查询。

在该查询中，检查表中是否存在该ID或者，如果找到，则更新描述字段。

由于这似乎是一次性更新，我建议你st将Excel工作表导出到文本文件或xml文件。要创建XML文件，请按照此处的说明操作： http://office.microsoft.com/en-us/excel-help/create-an-xml-data-file-and-xml-schema-file -from-worksheet-data-HA010263509.aspx＃BM1 [ ^ ]



您可以使用以下内容读取XML文件：

  Dim  doc 正如 新 Xml.XmlDocument 
 ' 设置XML数据文件的路径 
  Dim  docpath  As  < span class =code-keyword> String  = IO.Path.Combine（Environm ent.GetFolderPath（Environment.SpecialFolder.MyDocuments），  Data.xml）
 doc .Load（docpath）
 
 对于 每个行 As  Xml.XmlNode 在 doc.GetElementsByTagName（ 行） 行是Excel XML导出实用程序创建的标记 
  Dim  pid  As  Xml.XmlElement = row（  pid）
  Dim  pid_value  As   Int32  =  Int32  .Parse（pid.InnerText） ' 假设这是一个整数 
  Dim  desc  As  Xml.XmlElement = row（  description）
 ' 使用desc.innertext更新此pid值的数据库 
 下一步 

Hi to all,
i have a table in sql server management...with name Product..
that Product Table Contains pid,pname,description,rate attributes..
i have 100 records in Product Table..i didn't give description part while inserting attribute values...
so, now i want to insert that description attribute value..
i have a Excel with pid and description for 100 products..
now i need a script code to update description in Product Table using Excel, if Product Table PID is equals to Excel PID.....

解决方案

Search in Google "Import data from Excel to Database in C#".

You just need to read the Excel file, run query for each row of Excel.
In that query, check if that ID exists in Table or not, if found, then update the Description field.

Since this appears to be a one time update, I would suggest that you just export your Excel worksheet to either a text file or xml file. To create an XML File, follow the instructions here: http://office.microsoft.com/en-us/excel-help/create-an-xml-data-file-and-xml-schema-file-from-worksheet-data-HA010263509.aspx#BM1[^]

You could read an XML file in using something like this:

Dim doc As New Xml.XmlDocument
' set the path to the XML data file
Dim docpath As String = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Data.xml")
doc.Load(docpath)

For Each row As Xml.XmlNode In doc.GetElementsByTagName("Row") ' Row is the the tag created by the Excel XML export utility
      Dim pid As Xml.XmlElement = row("pid")
      Dim pid_value As Int32 = Int32.Parse(pid.InnerText) ' assumed that this is an integer
      Dim desc As Xml.XmlElement = row("description")
      ' update the database for this pid value with desc.innertext
Next

这篇关于如何使用Excel更新数据库表值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！