OpenDataSource打开不同类型的数据表

标题　: 利用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$