现在有这么一个问题,我把EXECL导入到SQL SERVER的一个表里,但是很慢,我一个一万多条的EXECL表要20分种左右。有什么方法可以让他快一点吗?谁有这方面的代码?我的代码是:
procedure Tfrm_month.SpeedButton2Click(Sender: TObject);
var ExcelApp,MyWorkBook: OLEVariant;
i: Integer;
begin
try
messageDlg('在导入前请关闭所有EXCEL表!',mtWarning,[mbok],0);
ExcelApp:=CreateOleObject('Excel.Application');
MyWorkBook:=CreateOleobject('Excel.Sheet');
except
application.Messagebox('无法打开Xls文件请确认已经安装EXCEL.','',mb_OK+mb_IconStop);
Exit;
end;
if OpenDialog1.Execute then
MyworkBook:= ExcelApp.workBooks.Open(OpenDialog1.FileName);
i:=2;
while trim(string(MyWorkBook.WorkSheets[1].Cells[i,1]))<>''do begin
adotable1.Append;
adotable1.FieldByName('Name').AsString := MyWorkBook.WorkSheets[1].Cells[i,1].Value;
adotable1.FieldByName('type').AsString := MyWorkBook.WorkSheets[1].Cells[i,2].Value;
adotable1.FieldByName('min').AsString := MyWorkBook.WorkSheets[1].Cells[i,3].Value;
adotable1.FieldByName('fee').AsString := MyWorkBook.WorkSheets[1].Cells[i,4].Value;
adotable1.Post;
inc(i);
end;
ExcelApp.WorkBooks.Close;
ExcelApp.quit;
ExcelApp:=Unassigned;
end;
end;
procedure Tfrm_month.SpeedButton2Click(Sender: TObject);
var ExcelApp,MyWorkBook: OLEVariant;
i: Integer;
begin
try
messageDlg('在导入前请关闭所有EXCEL表!',mtWarning,[mbok],0);
ExcelApp:=CreateOleObject('Excel.Application');
MyWorkBook:=CreateOleobject('Excel.Sheet');
except
application.Messagebox('无法打开Xls文件请确认已经安装EXCEL.','',mb_OK+mb_IconStop);
Exit;
end;
if OpenDialog1.Execute then
MyworkBook:= ExcelApp.workBooks.Open(OpenDialog1.FileName);
i:=2;
while trim(string(MyWorkBook.WorkSheets[1].Cells[i,1]))<>''do begin
adotable1.Append;
adotable1.FieldByName('Name').AsString := MyWorkBook.WorkSheets[1].Cells[i,1].Value;
adotable1.FieldByName('type').AsString := MyWorkBook.WorkSheets[1].Cells[i,2].Value;
adotable1.FieldByName('min').AsString := MyWorkBook.WorkSheets[1].Cells[i,3].Value;
adotable1.FieldByName('fee').AsString := MyWorkBook.WorkSheets[1].Cells[i,4].Value;
adotable1.Post;
inc(i);
end;
ExcelApp.WorkBooks.Close;
ExcelApp.quit;
ExcelApp:=Unassigned;
end;
end;
解决方案 »
- 兄弟们,将字节数组转换成字符串用什么函数啊!?
- shellcombobox如何随窗口改变大小
- 求助!数据库存储过程中删除问题!急!各位高手帮帮忙!小第在此先谢过了!
- 我以前没有写过WINDOWS服务,想写一个WINDOWS服务,计算机启动后,服务会自动启动我的鼠标、键盘钩子,请问各位高手该怎样写啊?
- 菜鸟问题---急!!!在线等待!!!
- 与sql server 2000连接问题
- 判断dbgrid里的一行是否选中,请问怎么编写代码?
- 主 题:我是数据库的新手,向大虾请较!
- 在CSDN上是否有离线数据包供下载?能否和大富翁论坛一样提供相应的功能?
- ******** 200 分解决我的老问题****** ,关于STRINGGRID的输入问题
- 救命啊!Xp下delphi5为什么不能调试dll么??
- 对象问题
execute master..xp_cmdshell 'bcp........',但EXCEL的格式存在一些问题,必须用BCP从SQL导出一EXCEL,然后以此EXCLE为模板作为源才可以导入。
导入:
CREATE procedure ExcelToSQL2
AS
begin
delete temp_SZ
exec master..xp_cmdshell 'bcp MarkDB.dbo.temp_SZ in \\192.168.0.3\partno\sbook.xls -c -q -S"ERPSERVER" -U"sa" -P"songerppass"'
end;
GO
导出:
CREATE procedure ExcelToSQL
@start char(8),
@end char(8)
as
begin
if exists(select * from sysobjects where name='temp1')
drop table temp1
select A.TG003 AS A01,A.TG001 AS A02, A.TG002 AS A03,A.TG020 AS A04,A.TG015 AS A05,B.TH004 AS A06,
B.TH009 AS A07,B.TH008 AS A08,B.TH012 AS A09 ,A.TG012 AS A10 into MarkDB.dbo.temp1 FROM MarkDB.dbo.COPTG A
, MarkDB.dbo.COPTH B WHERE A.TG001=B.TH001 AND A.TG002=B.TH002 and TG003>=@start and TG003<=@end and A.TG023='Y'
execute master..xp_cmdshell 'bcp MarkDB.dbo.temp1 out \\192.168.0.3\partno\sbook.xls -c -q -S"ERPSERVER" -U"sa" -P"songerppass"'
end
drop table temp1
GO
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\a.xls;Extended
Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False
Extended Properties参数属性这样写 Excel 5.0;HDR=YES;IMEX=1
IMEX=1就是指混合型转换为文本
SQLServer从Excel取数
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False')...sheet1$