我用这个为什么是说 找不到输出文件 openrowset 呀,
insert into OPENROWSET('microsoft.jet.oledb.4.0','excel 8.0;hdr=yes;database=c:\aaa.xls;',[sheet$]) select * from wtb
insert into OPENROWSET('microsoft.jet.oledb.4.0','excel 8.0;hdr=yes;database=c:\aaa.xls;',[sheet$]) select * from wtb
var j,k,l:integer;yyy:boolean;
eclApp1,WorkBook1:Variant;//声明为OLE Automation 对象
xlsFileName1:string;wdh:integer;
begin
if savedialog1.Execute then
xlsFileName1:=savedialog1.FileName
else
exit; try//创建OLE对象Excel Application与 WorkBook
eclApp1:=CreateOleObject('Excel.Application');
WorkBook1:=CreateOleobject('Excel.Sheet');
except
ShowMessage('You Computer not install Microsoft Excel。');
Exit;
end;
try
ShowMessage('System will be input data proceedure,it maybe takes some time!');
workBook1:=eclApp1.workBooks.Add;ADOQuery1.Close;
ADOQuery1.SQL.clear;
ADOQuery1.SQL.add('select dch from dcwtb where wjh=:wjh and sj between :dtp1 and :dtp2 group by dch');
ADOQuery1.Parameters[0].Value:=wjh;
ADOQuery1.Parameters[1].Value:=strtodate(copy(datetimetostr(dtp1.datetime),1,pos(' ',datetimetostr(dtp1.DateTime))-1));
ADOQuery1.Parameters[2].Value:=strtodate(copy(datetimetostr(dtp2.datetime),1,pos(' ',datetimetostr(dtp2.DateTime))-1));
ADOQuery1.Open;
j:=2;k:=1;
ADOQuery4.Close;
ADOQuery4.sql.clear;
ADOQuery4.SQL.add('select * from wtb where wjh=:wjh order by wth');
ADOQuery4.Parameters[0].Value:=wjh;
ADOQuery4.Open;
while not ADOQuery4.Eof do
begin
eclapp1.cells(1,k):=ADOQuery4.FieldByName('wt').asstring;
ADOQuery4.next; k:=k+1;
end;while not ADOQuery1.eof do
begin
dch:=ADOQuery1.fieldbyname('dch').asstring;
ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.sql.add('select * from dcwtb where dch=:dch and wjh=:wjh order by wth');
ADOQuery2.Parameters[0].Value:=dch;
ADOQuery2.Parameters[1].Value:=wjh;
ADOQuery2.Open;
wth:=ADOQuery2.fieldbyname('wth').asinteger;
k:=1;
while not ADOQuery2.Eof do
begin
wth:=ADOQuery2.fieldbyname('wth').asinteger;
if (wth-wdh)>1 then k:=k+(wth-wdh)-1;
ADOQuery3.Close;
ADOQuery3.sql.clear;
ADOQuery3.SQL.add('select * from dcdab where dch=:dch and wjh=:wjh and wth=:wth order by wth');
ADOQuery3.Parameters[0].Value:=dch;
ADOQuery3.Parameters[1].Value:=wjh;
ADOQuery3.Parameters[2].Value:=wth;
ADOQuery3.Open;
while not ADOQuery3.Eof do
begin
daochu.cursor:=crHourGlass;
if copy(ADOQuery3.FieldByName('da').AsString,1,2)='**' then
dan:=dan+'['+copy(ADOQuery3.fieldbyname('da').AsString,3,length(ADOQuery3.fieldbyname('da').AsString))+']'+', '
else
if ADOQuery3.FieldByName('da').AsString<>'Null' then
dan:=dan+'['+ADOQuery3.fieldbyname('da').asstring+']'+', ';
ADOQuery3.next;
end;
dan:=copy(dan,1,length(dan)-2); eclapp1.cells(j,k):=dan;
k:=k+1;dan:='';
wdh:=wth;
ADOQuery2.next;
end;
j:=j+1;
ADOQuery1.Next;
end;
daochu.cursor:=crdefault;
WorkBook1.saveas(xlsFileName1);
WorkBook1.close;
eclApp1.Quit;
Application.MessageBox('Succeed','Attention',MB_OK);
// eclApp:=Unassigned;
except
ShowMessage('System can not handle the EXCEL file,due to the file opened in wrong software or system errors');
WorkBook1.close;
eclApp1.Quit;
// eclApp:=Unassigned;
end;
end;
在SQL SERVER 里运行:SELECT *INTO newtableFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名------------------------------------------------------------------------------------------------- 3. 将SQL SERVER表里的数据插入到Access表中-- ======================================================在SQL SERVER 里运行:insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名 (列名1,列名2)select 列名1,列名2 from sql表实例:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\db.mdb';'admin';'', Test) select id,name from TestINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)SELECT *FROM sqltablename-------------------------------------------------------------------------------------------------二、 SQL SERVER 和EXCEL的数据导入导出1、在SQL SERVER里查询Excel数据:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件-- ======================================================T-SQL代码:EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'参数:S 是SQL服务器名;U是用户;P是密码说明:还可以导出文本文件等多种格式实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"' EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'在VB6中应用ADO导出EXCEL文件代码: Dim cn As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"-------------------------------------------------------------------------------------------------
4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码:
INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$] (bestand, produkt) VALUES (20, 'Test') -------------------------------------------------------------------------------------------------总结:利用以上语句,我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换,为我们提供了极大方便!把文本文件导入到ACCESS
在Form上放一个ADOConnection,连结指向目标Access库
比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute('Select * Into abcd From [Text;Database=c:\temp].aaaa.txt');=============================================================================================把DBF(Foxpro数据库)导入到SQLSERVER
告诉你一个最快的方法,用SQLServer连接DBF(Foxpro数据库)
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0,
'Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk
2人 .............................
n人..............................我的数据库表结构为
问题表
编号 问卷号 问题
调查表
问卷号,问题号,答案号,答安其中还有一个调查问卷表,因为客户在做问卷的时候会有跳转,也就是说有的题是不做的