导入并新建表的SQL是:SELECT * into [创建的表名] FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="F:\bbb.xls";Extended Properties=Excel 8.0')...[sheet1$] 请问:EXCEL导入SQL SERVER一个已存在的表中的SQL语句怎么写?在网上搜了很多没有成功,望高手不吝赐教
我这有个例子: Function ExcelToADOQuery: TADOQuery; var eclApp,WorkBook:Variant; tempOpenDialog: TOpenDialog; tempADOQuery: TADOQuery; sFileName:string; sSheetName:string; i:integer; begin Result:=nil; tempOpenDialog:=TOpenDialog.Create(nil); tempOpenDialog.Filter:='Excel File Format(*.xls)|*.xls'; // Sj if NOT tempOpenDialog.Execute then exit; sFileName:=tempOpenDialog.FileName; If tempADOQuery<>nil Then tempADOQuery.Free; tempADOQuery:=TADOQuery.Create(nil); try if VarIsEmpty(eclApp) then eclApp:=CreateOleObject('Excel.Application'); //打开文件 WorkBook:=eclApp.Workbooks.Open(sFileName); Except on e: exception do begin MShow('MESFunction.ErrNoFile','您的机器里未安装Microsoft Excel或所选择的文件格式出错',2,#13#10 + e.message); Exit; end; end; sSheetName:=Workbook.Sheets[1].Name; //生成ADOQuery WorkBook.saved:=true; WorkBook.close; eclApp.Quit; WorkBook := Unassigned; eclApp := Unassigned; try with tempADOQuery do begin ConnectionString:='Provider = Microsoft.Jet.OLEDB.4.0;Data Source='+sFileName+';Extended Properties="Excel 8.0;HDR=YES;IMEX=1";Persist Security Info=False'; SQL.Clear; SQL.Add('select * from ['+sSheetName+'$]'); Open; end; For i:=0 To tempADOQuery.Fields.Count-1 Do tempADOQuery.Fields.Fields[i].DisplayWidth :=10; except MShow('MESFunction.ErrOpenExecel','打开Excel文件失败!'); Abort; end; Result:=tempADOQuery; tempOpenDialog.Free; end;
我测试了没有成功!
insert into [创建的表名] FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="F:\bbb.xls";Extended Properties=Excel 8.0')...[sheet1$]
按你的方法改了也不行!错误在“from”附近。
怎么下载不了呢?
Function ExcelToADOQuery: TADOQuery;
var
eclApp,WorkBook:Variant;
tempOpenDialog: TOpenDialog;
tempADOQuery: TADOQuery;
sFileName:string;
sSheetName:string;
i:integer;
begin
Result:=nil;
tempOpenDialog:=TOpenDialog.Create(nil);
tempOpenDialog.Filter:='Excel File Format(*.xls)|*.xls'; // Sj
if NOT tempOpenDialog.Execute then exit;
sFileName:=tempOpenDialog.FileName;
If tempADOQuery<>nil Then tempADOQuery.Free;
tempADOQuery:=TADOQuery.Create(nil);
try
if VarIsEmpty(eclApp) then
eclApp:=CreateOleObject('Excel.Application');
//打开文件
WorkBook:=eclApp.Workbooks.Open(sFileName);
Except on e: exception do
begin
MShow('MESFunction.ErrNoFile','您的机器里未安装Microsoft Excel或所选择的文件格式出错',2,#13#10 + e.message);
Exit;
end;
end;
sSheetName:=Workbook.Sheets[1].Name;
//生成ADOQuery WorkBook.saved:=true;
WorkBook.close;
eclApp.Quit;
WorkBook := Unassigned;
eclApp := Unassigned;
try
with tempADOQuery do
begin
ConnectionString:='Provider = Microsoft.Jet.OLEDB.4.0;Data Source='+sFileName+';Extended Properties="Excel 8.0;HDR=YES;IMEX=1";Persist Security Info=False';
SQL.Clear;
SQL.Add('select * from ['+sSheetName+'$]');
Open;
end;
For i:=0 To tempADOQuery.Fields.Count-1 Do
tempADOQuery.Fields.Fields[i].DisplayWidth :=10;
except
MShow('MESFunction.ErrOpenExecel','打开Excel文件失败!');
Abort;
end;
Result:=tempADOQuery;
tempOpenDialog.Free;
end;