如何在SQL Server表中插入数据Excel文件 [英] how to insert data excel file in sql server table

问题描述

我想通过将Excel文件中的列名与表中的字段名进行匹配来将数据从Excel文件插入到SQL表中.例如，在我的页面(窗体?)中，我有一个下拉列表，其中包含来自SQL DB的表名列表，用户可以从中选择一个作为目标.在此表中，我想从Excel文件中插入数据，并将字段名称与列匹配.请给我指出一些示例.
谢谢.

I want to insert data from an Excel file into an SQL table, by matching the column names in the Excel file with the field names in the table. For instance, in my page (form?), I have a drop-down list containing a list of table names from the SQL DB, from which a user can select one as the destination. Into this table, I want to insert data from an Excel file, matching columns with field names. Please point me to some samples.
Thanks.

推荐答案

这里是怎么做.

Here is how can do so.

private void button1_Click(object sender, EventArgs e)
{
	try {
		DataTable sheetTable = loadSingleSheet("C:\\excelFile.xls", "Sheet1

")； updateDataTable(sheetTable); } 捕获(例外){ System.Response.Write(ex.Message); } } 公共 无效 updateDataTable(DataTable dt) { 使用(SqlConnection connection = 新 SqlConnection(connectionString)){ SqlDataAdapter adapter = 新 SqlDataAdapter(); DataSet ds = 新 DataSet(); ds.Tables.Add(dt); adapter.Update(ds); } } 私有 OleDbConnection returnConnection(字符串 fileName) { 返回 新 OleDbConnection(" Provider = Microsoft.Jet.OLEDB.4.0;数据源=" + fileName + )； } 私有 DataTable loadSingleSheet(字符串 fileName，字符串 sheetName) { DataTable sheetData = 新 DataTable(); 使用(OleDbConnection conn = 此 .returnConnection(fileName)){ conn.Open(); // 使用数据适配器检索数据 OleDbDataAdapter sheetAdapter = 新建 OleDbDataAdapter(" + sheetName + " ，conn)； sheetAdapter.Fill(sheetData); } 返回 sheetData; }

"); updateDataTable(sheetTable); } catch (Exception ex) { System.Response.Write(ex.Message); } } public void updateDataTable(DataTable dt) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); ds.Tables.Add(dt); adapter.Update(ds); } } private OleDbConnection returnConnection(string fileName) { return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\""); } private DataTable loadSingleSheet(string fileName, string sheetName) { DataTable sheetData = new DataTable(); using (OleDbConnection conn = this.returnConnection(fileName)) { conn.Open(); // retrieve the data using data adapter OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn); sheetAdapter.Fill(sheetData); } return sheetData; }

可能需要进行一些调整，但这实际上是您的基本操作.

It might need some tuning but this is how you will do basically.

BULK INSERT MyTable FROM 'D:\myexcel.csv'
WITH
(
	FIRSTROW=2,
	FIELDTERMINATOR=',',
	ROWTERMINATOR='\n' 
)

您的表格必须与Excel中的表格完全匹配.

Your table must match exact column sequence as you have in Excel.

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