特大EXCEL文件数据导入数据库的问题，高手请进 ( 积分: 50 )

我的目的是将另外一个财务程序导出的excel表的数据导入我的数据库，方法跟论坛中大家经常采用的方法基本一样，如按如下：
1.设置ADOConnection的ConnectionString
构造ConnectionString时，OLE DB的提供者要选择Microsoft Jet 4.0 OLE DB Provider作为ADO的驱动程序。
这本来是用于连接Access数据库的驱动程序，但也可打开Excel文件。
连接的数据库名称当然就是你要打开的Excel文件，注意扩展名是*.xls，而不是*.mdb。
最关键的一点是，还要设置扩展属性Extended Properties为“Excel 8.0”，否则，测试连接时会报告无法
识别数据格式的错误。Extended Properties的属性值在“所有”参数页中输入。
最后，设置完成后的ConnectionString中的各项参数为：
Provider=Microsoft.Jet.OLEDB.4.0
Data Source=MyExcelFile.xls
Extended Properties=Excel 8.0
Persist Security Info=False

　　2.设置ADODataSet或ADOTable
将ADODataSet或ADOTable连接到刚才的ADOConnection。如果不用ADOConnection，也可以参照上面的方法
直接设置ADODataSet或ADOTable的ConnectionString属性。对于ADODataSet，需要将CommandType属性设置
为cmdTableDirect，而对于ADOTable，则将TableDirect属性设置为True。因为，访问Excel文件是直接的
数据文件访问，不是通过SQL语句来操作游标访问的。如果不设置直接访问，则系统会报告SQL语句格式错
误等信息。
然后，当你下拉ADODataSet中的CommandText属性或ADOTable的TableName属性时，就可以选择到要打开的工
作表了。注意，表名后面多了加一个$符号。

3.打开ADODataSet或ADOTable

但我的在连接1.29M的excel文件就出现系统资源不足，连接700K的文件则正常，但我无意中发现，当我将excel文件用excel程序打开后，什么都不用动，再次用我的程序导入该特大文件则正常了，我想，应该是数据缓冲的问题，excel文件打开后，在内存有其影像，故因此后面打开正常。
请高手帮分析下，究竟是什么原因，怎么解决？

我的目的是将另外一个财务程序导出的excel表的数据导入我的数据库，方法跟论坛中大家经常采用的方法基本一样，如按如下：
1.设置ADOConnection的ConnectionString
构造ConnectionString时，OLE DB的提供者要选择Microsoft Jet 4.0 OLE DB Provider作为ADO的驱动程序。
这本来是用于连接Access数据库的驱动程序，但也可打开Excel文件。
连接的数据库名称当然就是你要打开的Excel文件，注意扩展名是*.xls，而不是*.mdb。
最关键的一点是，还要设置扩展属性Extended Properties为“Excel 8.0”，否则，测试连接时会报告无法
识别数据格式的错误。Extended Properties的属性值在“所有”参数页中输入。
最后，设置完成后的ConnectionString中的各项参数为：
Provider=Microsoft.Jet.OLEDB.4.0
Data Source=MyExcelFile.xls
Extended Properties=Excel 8.0
Persist Security Info=False

　　2.设置ADODataSet或ADOTable
将ADODataSet或ADOTable连接到刚才的ADOConnection。如果不用ADOConnection，也可以参照上面的方法
直接设置ADODataSet或ADOTable的ConnectionString属性。对于ADODataSet，需要将CommandType属性设置
为cmdTableDirect，而对于ADOTable，则将TableDirect属性设置为True。因为，访问Excel文件是直接的
数据文件访问，不是通过SQL语句来操作游标访问的。如果不设置直接访问，则系统会报告SQL语句格式错
误等信息。
然后，当你下拉ADODataSet中的CommandText属性或ADOTable的TableName属性时，就可以选择到要打开的工
作表了。注意，表名后面多了加一个$符号。

3.打开ADODataSet或ADOTable

但我的在连接1.29M的excel文件就出现系统资源不足，连接700K的文件则正常，但我无意中发现，当我将excel文件用excel程序打开后，什么都不用动，再次用我的程序导入该特大文件则正常了，我想，应该是数据缓冲的问题，excel文件打开后，在内存有其影像，故因此后面打开正常。
请高手帮分析下，究竟是什么原因，怎么解决？

ith ADOTable1 do
begin
for i:=1 to xlsapp.rowcount do
begin
Insert;
FieldByName('A').AsString:=xlsapp.Cells[i,2];
.......
FieldByName('B').AsString:=xlsapp.Cells[i,14];

end;
showmessage('数据录入成功');
xlsapp.workbooks.close;
大致上这个样子。

你是用ole文件方式来进行，我想以数据库的方式来打开，还有别的好办法吗？
你这个方法必须是已经安装了office才行的，如果用jet ole数据库连接方式，可以把excel当做数据库操作，这样挑选数据就很方便。

unit UitSQLsave;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, ComCtrls;

type
TForm4 = class(TForm)
SBar: TStatusBar;
ADOC1: TADOConnection;
OpenDialog1: TOpenDialog;
ADOC2: TADOConnection;
Button2: TButton;
Label3: TLabel;
Button4: TButton;
ADOQ2: TADOQuery;
ComboBox1: TComboBox;
GroupBox1: TGroupBox;
Label1: TLabel;
Label2: TLabel;
Button1: TButton;
Edit1: TEdit;
comexcel: TComboBox;
Button3: TButton;
procedure Button3Click(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure FindConnectionString( AADOConn: TADOConnection;AConStr: String);
procedure Button2Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form4: TForm4;
ConnString: String;

implementation

{$R *.dfm}

procedure TForm4.Button1Click(Sender: TObject);
var
connstr1:string;
begin
If openDialog1.Execute then
begin
connstr1:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=';
connstr1:=connstr1+OpenDialog1.FileName;
connstr1:=connstr1+';Mode=Read;Extended Properties=Excel 8.0;Persist Security Info=False';
ADOC1.ConnectionString:=connstr1;
ADOC1.Connected:=True;
ADOC1.GetTableNames(comexcel.Items,False);
SBar.Panels[1].Text:=opendialog1.FileName;
end;
end;

procedure TForm4.Button3Click(Sender: TObject);
var
s:string;
I:integer;
begin
if edit1.Text ='' then
begin
showmessage('表名不能為空!');
exit;
end;
if comexcel.Text ='' then
begin
showmessage('請選擇要導入的表名!');
exit;
end;
for i := 0 to combobox1.Items.Count-1 do
if trim(edit1.Text)=combobox1.Items.Strings then
begin
showmessage('表名已存在,請重新輸入');
exit;
end;
ADOC2.Connected :=false;
ADOC2.ConnectionString:=ConnString;
s:='select * into ['+trim(edit1.text)+'] from OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+SBar.Panels[1].Text+'";Extended Properties=Excel 8.0'')...['+trim(comexcel.Text)+']';
try
ADOC2.Execute(s);
ShowMessage('導入成功');
except
ShowMessage('導入失敗！');
end;
end;

procedure TForm4.FindConnectionString(AADOConn: TADOConnection;
AConStr: String);
begin
try
try
ADOC2.Close;
ConnString:=PromptDataSource(Application.Handle,ADOC2.ConnectionString);
ADOC2.ConnectionString:= ConnString;
AConStr:=ConnString;
finally
ADOC2.Open();
SBar.Panels[0].Text:='數據庫連接成功!';
GroupBox1.Visible:=true;
end;
except
on E: Exception do
MessageBox(Application.Handle,PChar('錯誤信息:' + E.Message),'提示',MB_ICONINFORMATION);

end;
end;

procedure TForm4.Button2Click(Sender: TObject);
begin
FindConnectionString(ADOC2,'');
ADOC2.GetTableNames(combobox1.Items);
end;

procedure TForm4.Button4Click(Sender: TObject);
begin
close;
end;

procedure TForm4.FormClose(Sender: TObject; var Action: TCloseAction);
begin
action:=cafree;
form4:=nil;
end;


end.
我自己寫的一個，是把excel導入sql服務器，你自己試試

To 漂流的雲：
首先非常感谢你的支持，但你提供的代码在论坛已有类似的方法和代码，均存在连接时提示“超出系统资源”的问题，哪位能解决本问题的方法？请赐教！

我的excel表中数据大约是2000行，有60列，这数据文件达1.29M大小，用本论坛的数据库连接方法基本都出现“超出系统资源”的提示，用ole文件打开方式倒没有测试。

幫你頂。超大的數據我也沒試過。。。。
你也可以用SQL自帶的工具導入

2000多行不算多吧，2万多行我也导过，不过是用存储过程。
create procedure PROC_IMPORT
@xlsFile varchar(200),
@tblName varchar(200)
as
begin
--将数据写入到tmp_import，要写入你自己的表则再写条SQL语句即可。
declare @sql varchar(8000)
if object_id('tmp_import') is not null
drop table tmp_import
set @sql = 'SELECT * into tmp_import FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'', ''Data Source=
"'+@xlsFile+'"; User ID=Admin;Password =;Extended properties=Excel 5.0'')...' + @tblName
exec(@sql)

end

@tblName是Excel中的sheet,要加$

奉劝你不要用程序导,我以前做过一个试验,从ACCESS数据库导出220万条记录到EXCEL里面,结果时间长到简直无法忍受,你最好装个OFFICE2003,里面好像有个从文件导入数据库和从数据库导入文件的功能,那个快的多的多了!

多谢各位老兄啦，但我这数据是必导的，如果用别的程序导来导去，对不大懂的用户来说是很大的负担，特别是无形中增加工作量和增加出错的机会了，是不允许的。
其实，我已用ole连接方式测试过，先导入临时表，然后从临时表来挑选数据，可以根据excel数据第一行字段来自动导入相应的自动，防止出错，能正常使用了。但我想换种方法，直接用数据库连接方式，这样可以跳过导来导去的麻烦。
哪位高手能出好点子，如能给代码，我另外给他加100分。

帮你顶！

唉，在线等高手指点，只好先自己顶一下了。

to protossgffnxk:
我对目前大富翁论坛所有采用数据库连接方式的方法进行测试，均出现以下的错误提示：

OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。
[OLE/DB provider returned message: 超出系统资源。]
OLE DB 错误跟踪［OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaRowset::GetRowset returned 0x80004005: ］。

用ole打开文件逐个读取的方式正正常。请问还有别的用数据库连接的好方法吗？

请高手指点！

录宏不就是了
accApp:= CreateOleObject('Access.application');
accApp.Application.OpenCurrentDatabase(ExtractFilePath(Application.exeName) + '数据库名.mdb');
accApp.docmd.TransferSpreadsheet(0, 8, '表名', '导入的Excel文件' , False, SheetNames + '!A1:O'+SheetEndNos);
AccApp.Application.CloseCurrentDatabase;
office2003中TransferSpreadsheet的帮助内容
在 Visual Basic 中，TransferSpreadsheet 方法执行 TransferSpreadsheet 操作。

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
expression 必需。返回“应用于”列表中的一个对象的表达式。

TransferType 可选 AcDataTransferType。

AcDataTransferType 可以是下列 AcDataTransferType 常量之一：
acExport
acImport 默认
acLink
如果将该参数留空，将采用默认常量 (acImport)。


SpreadsheetType 可选 AcSpreadSheetType。

AcSpreadSheetType 可以是这些 AcSpreadSheetType 常量之一：
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 默认
acSpreadsheetTypeExcel9 默认
acSpreadsheetTypeLotusWJ2 - 仅适用于日文版
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
注释 可以链接 Lotus 1-2-3 电子表文件中的数据，但是这些数据在 Microsoft Access 中是只读的。可以导入和链接 Lotus .WK4 文件，但是不能将 Microsoft Access 数据导出到此电子表格式中。而且，Microsoft Access 不再支持使用此方法导入、导出或链接 Lotus .WKS 或 Microsoft Excel 2.0 电子表中的数据。

如果将该参数留空，将采用默认常量 (acSpreadsheetTypeExcel8)。


TableName 可选 Variant 型。字符串表达式，表示要向其中导入电子表格数据、从中导出电子表格数据或链接电子表格数据的 Microsoft Access 表的名称，或要将其结果导出到电子表格的 Microsoft Access 选择查询的名称。

FileName 可选 Variant 型。字符串表达式，表示要从其中导入、导出到或链接到的电子表格的名称与路径。

HasFieldNames 可选 Variant 型。使用 True (-1) 可以在导入或链接时，将电子表格中的第一行用作字段名。使用 False (0) 可以将数据表第一行看成普通数据。如果将该参数留空，则采用默认值 (False)。在将 Microsoft Access 表或选择查询数据导出到电子表格中时，无论为该参数输入了何值，字段名称都将插入到电子表格的第一行中。

Range 可选 Variant 型。字符串表达式，表示电子表格中单元格的有效范围或范围名称。该参数仅可用于导入。若要导入整个电子表格，请将该参数留空。导出到电子表格时，必须将该参数留空。如果输入了一个范围，导出将失败。

UseOA 可选 Variant 型。

说明
有关该操作及其参数如何使用的详细信息，请参阅该操作的主题。

语法中的可选参数允许留空，但是必须包含参数的逗号。如果将位于末端的参数留空，则在指定的最后一个参数后面不需使用逗号。


注释 还可以通过使用 Recordset 对象的 ActiveConnection 属性，来使用 ActiveX 数据对象 (ADO) 创建链接。


示例
下面的示例在 Lotus 电子表格 Newemps.wk3 的指定范围内将表导入到 Microsoft Access 的“Employees”表，并用电子表格中的第一行作为字段名。

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:/Lotus/Newemps.wk3", True, "A1:G12"

delphi调用com调用Cells[row,col]方法读excel文件，如果文件数兆的话，慢死了。

我做过试验，一个4M的excel数据用delphi做程序遍历所有单元格至少需要10分钟。而用java的poi读取才4秒。
效率天壤之别啊。

那是当然，循环是要尽量要避免的。如上我的做法，10M的Excel加字符串的处理1分钟。
同时注意最好不要直接用客户的原始数据来做导入的处理，大于2M的Excel文件是不合理的，损坏的可能性不小。

to siasa:
我需要的是将客户考盘的数据xls文件用程序直接导入sql server 2000中，程序也是有不大懂电脑的客户来使用，我肯定不可能在用上述方法的了，在大富翁论坛中有不少的朋友用存储过程来实现，但我测试后发现大文件（1.5M）多字段（80个字段）都会出错。
请问你还有其他好的方法吗？

哪位高手能帮我解决此问题呀？再没人解答我就只好结贴了。