如何将批量数据插入现有数据库？ [英] How to insert bulk data into an existing data base?

问题描述

我是新手，我曾经在Oracle和Mysql工作过。


我想方便我填充我在

访问中设计的数据库，其中包含大量可以在Excel工作表中排序的数据，


所有数据的来源都非常大（100 * 2000）excel传播

sheet其中一些数据类型是无关紧要的访问数据

类型在我的数据库之前我把它改成了一些excel表

与我的DB有关。


有没有任何已知的解决方案？


非常感谢

Hi,

I am new to access I usedto work in Oracle and Mysql.

I am after a way that enables me to populate a database I designed in
access with lots of data which can be sorted in excel sheets,

The source of all data is a very large size (100*2000) excel spread
sheet In which some data types are absulotely Irelevant to access data
types in my DB there fore I changed It to a number of excel sheets
which are relevant to my DB.

Is there any known solution for this?

Many thanks

推荐答案

我将向您展示两种方法。第一种方法只从一张Excel工作表中检索

数据到一个Access表。第二种方法使用

数组，以便您可以从多个Excel表格中检索数据，并且

填充多个Access表格。

----- -----------------------------------------------

'' - 方法1 - 一张Excel表到一个访问表

Sub GetDataFromExcel（）

Dim dbsTemp作为数据库

Dim tdfLinked As TableDef


设置dbsTemp = CurrentDb

设置tdfLinked = dbsTemp.CreateTableDef（" tblSheet1"）

tdfLinked.Connect = _

" Excel 5.0; HDR = YES; IMEX = 2; DATABASE = C：\ Dir1 \ Book1.xls"

tdfLinked.SourceTableName =" Sheet1

I will show you two methods for this. The first methods only retrieves
data from one Excel sheet to one Access table. The second method uses
array so that you can retrieve data from multiple Excel Sheets and
populate multiple Access tables.
----------------------------------------------------
''--method 1 -- one Excel Sheet to one Access Table
Sub GetDataFromExcel()
Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("tblSheet1")
tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"
tdfLinked.SourceTableName = "Sheet1

"

dbsTemp.TableDefs.Append tdfLinked

DoCmd.RunSql" select * into tbl1 from tblSheet1" ;

End Sub

------------------------------- ---------------------


基本上你使用
$ b连接到相应的Excel表格$ b连接字符串（将C：\Dir1 \ Book1.xls替换为实际路径

到Excel工作簿）。哦，HDR = YES表示

Excel表格中的第一行是标题行。


Excel 5.0; HDR = YES; IMEX = 2; DATABASE = C：\Dir1 \ Book1.xls"


此相同的连接字符串适用于Excel97或Excel2000或

Excel2002。注意：当您引用Excel工作表名称（

我的示例中的Sheet1）时，您必须向其附加

"
dbsTemp.TableDefs.Append tdfLinked
DoCmd.RunSql "Select * Into tbl1 From tblSheet1"
End Sub
----------------------------------------------------

Basically you are connecting to a respective Excel sheet using the
connection string here (replace C:\Dir1\Book1.xls with the actual path
to your Excel Workbook). Oh, and HDR=YES means the first row in the
Excel sheet is a header row.

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"

This same connection string works with Excel97 or Excel2000 or
Excel2002. Note: when you reference your Excel Sheet name ("Sheet1" in
my example) you have to append a

符号。实际的工作表名称是

" Sheet1" （或者你的工作表名称）。您附加

sign to it. The actual sheet name is
"Sheet1" (or whatever your sheet name is). You append the

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