MS Access/VB.Net-如何将Excel工作表数据中的行插入MS Access表中? [英] MS Access/VB.Net - how can I insert rows from an Excel sheet data into MS Access table?
问题描述
我的程序是用VB.NET编写的，并且我有一个Excel工作表，其中只有一列，并且列的类型为文本".现在，我想将Excel工作表的所有行添加到MS Access表中.
My program is written in VB.NET and I have an excel sheet with only one column and type of column is Text. Now I want to add all the rows of excel sheet into MS Access table.
我的Access表有5列，我必须为其余字段提供一些静态数据.我不能让它们为空，因为我的表结构不允许我让它们为空.它们是强制性的.
My Access table has 5 columns, and I have to provide some static data for rest of fields. I can't let them empty as my table structure won't allow me to let them empty. they are mandatory.
- 将Excel工作表数据读取到数据表中
- 循环到DataTable所有行的末尾
-
在每个单元格的文本和一些静态信息上附加一个查询字符串
- Read excel sheet data into a DataTable
- Loop to the end of all rows of DataTable
Append text of each cell and some of my static information to make a query string
INSERT into TableName(c1,c2,c3)
values
(v1,v2,v3),
(v1,v2,v3),
(v1,v2,v3),
(v1,v2,v3)
...
...
(n1,n2,n3);
但是我在此查询中遇到错误，有任何建议吗?
But I got error in this query, Any suggestions?
推荐答案
您可以针对连接运行SQL，以直接与Access和Excel进行交互.例如，使用连接:
You can run SQL against a connection to interact directly with Access and Excel. For example, working with the connection:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Test.xlsm;
Extended Properties="Excel 12.0 xml;HDR=Yes;";
或喷射
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Test.xls;
Extended Properties="Excel 8.0;HDR=Yes;";
您可以运行以下SQL:
You can run the following SQL:
INSERT INTO [;DATABASE=Z:\Docs\Test.accdb].Table1 (ID,Atext)
SELECT ID, AText FROM [Sheet7$]
或喷射
INSERT INTO [;DATABASE=Z:\Docs\Test.mdb].Table1 (ID,Atext)
SELECT ID, AText FROM [Sheet7$]
这篇关于MS Access/VB.Net-如何将Excel工作表数据中的行插入MS Access表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
