OpenDataSource打开不同类型的数据表

来源：互联网发布：u盘格式化的数据恢复编辑：程序博客网时间：2024/05/16 19:09

标题　: 利用OpenDataSource打开不同类型的数据表(SQL Server)
关键字: OpenDataSource

Extended properties
DBase 5.0
DBASE IV
FoxPro 3.0
Paradox 7.X
Excel 8.0
text;HDR=YES;FMT=Delimited---分隔符


-------------------------------Acess----------------------------------
SELECT * 
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="d:/eatsun/test.mdb";
Jet OLEDB:Database Password=dgv;User ID=Admin;Password=;')...info

EXEC master..xp_cmdshell 'bcp gfmis.dbo.sgd out d:/sgd.xls -c -q -S"LUONAN" -U"sa" -P"1314"' 


-------------------------------Dbase-----------------------------------
Select '' as PSno, sNo, Cname, Type, 0, Floor, Price1, Price2, price3 
,Server From opendatasource('Microsoft.jet.oledb.4.0',
'Data Source="D:/eatsun/pos/sys/";
User ID=Admin;Password=;Extended properties=dbase 5.0')... ComRooms


------------------------把*.txt导入SQL中------------------------
1.
SELECT * into aaa FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/temp/";User ID=Admin;Password=;Extended properties=Text')...[bb#txt] 
2.
select * into aa from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=d:/eatsun;','select * from aa.txt')
3.
BULK INSERT Area FROM 'd:/test/crm/executable/全国邮编区号.txt'
 with (FieldTerminator=',',RowTerminator='/n')

ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/yourtablename;Persist Security Info=False';
ADOConnection1.Connected:=True;
ADOConnection1.Execute('SELECT * into t1 FROM t2 IN [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=;Server=(LOCAL);DataBase=x;]');


--------------------execel导入SQL中------------------
select * from opendatasource('Microsoft.jet.oledb.4.0',
'Data Source="D:/book1.xls"; Extended Properties="Excel 5.0;
HDR=Yes;";Persist Security Info=False')...sheet1$