如何将Excel数据导入SQL数据库表 [英] How to import excel data to SQL database table

问题描述

先生您好，

我有一张Excel工作表，它包含名称字段和ID字段.在我的数据库名称为Personal中，它具有名称和ID字段.现在，我需要从excel工作表中导入数据，并将所有数据插入数据库中的Personal表中.请给我打电话.下面以excel表格中的数据为例
请帮助我.....

Hello sir,

I have excel sheet and it contain Name Field and ID field. In my database name as Personal which has Name and ID fields.Now I need to import data from that excel sheet and insert that all data into Personal table in my database.Please tel me how i do this.Data in excel sheet is given below as example
Plz help me.....

Name    ID
a   1
b   2

推荐答案

您可以将excel数据复制并粘贴到文本框中，然后在保存"按钮上单击，执行遵循以下步骤(请记住，excel文件中的数据将以分隔的字符串复制，行之间用"\ r \ n"或"\ n"分隔，列通过"\ t"分隔):

You can copy and paste the excel data into a text box and on the "Save" button click, do as follows (remember that data in excel file gets copied as a delimited string, lines separated by "\r\n" or "\n" and columns by "\t"):

//split out the rows first
string[] rows = textBox1.Text.Trim().Split(new char[]{'\r\n', '\n'}, StringSplitOptions.None);

//loop starts from 1 to skip the header row that contains captions i.e. Name, ID etc
for(int i=1;i<rows.length;i++)>
{
   //for each row we need to split out each individual column
   string[] columns = rows[i].Split(new char[]{'\t'}, StringSplitOptions.None);
    
   //this bit is pseudocode which you would replace with your SqlCommand etc
   //assign each column value to its corresponding table column
   tablecolumn[0].value = columns[0];
   tablecolumn[1].value = columns[1];
   tablecolumn[2].value = columns[2];
   ...
   //finally save this record
   SaveThisRow();
}

这或多或少是我导入Excel的方式，简单，简单且相当快.

This is more or less the way I do my Excel imports, easy, simple and reasonably fast.

在这里查看:http://support.microsoft.com/kb/321686 [ ^ ]

我创建了一个Excel AddIn框架，该框架非常易于自定义为任何导出.您仍然必须实现从该框架放入数据的类和数据库中移动数据的导出. 在Excel加载项框架中使用属性进行验证和导出数据 [ ^ ]

顺便说一句，如果您要投反对票，为什么要说为什么要投反对票.是的，它不能提供完整的解决方案，但是我希望您提供解决方案的第一部分，它更好.如何连接数据库有很多解决方案，并且不依赖于excel，它可以是任何数据库解决方案，并且有很多解决方法可以应用于任何应用程序.

I have created a Excel AddIn framework that is very easy to customize to any export. You will still have to implement the export move the data from the class which this framework puts the data, and the database. Using Attributes in Excel Add-in Framework for Validating and Exporting Data[^]

Incidently if you are going to vote down, why say why you are voting down. Yes it does not provide a complete solution, but I would like to see you provide the first part of the solution that is better. How you connect to the database has many solutions, and is not dependent on being excel, it can be any database solution, and there are a lot of ways to solve that would apply to any application.

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