文件导入（File import）.docVIP

文件导入（File import） * * * * * * * 导出到excel Exec master..xp _ cmdshell BCP settledb.dbo.shanghu out C: \ temp1.xls - C - Q - s gnetdata / gnetdata - U Sa - P / * * * * * * * * * * * 导入excel Select * From opendatasource (microsoft.jet.oledb.4.0 , data source = C: \ test.xls; user id = admin; password =; extended properties = excel 5.0)...Xactions / * 动态文件名 Declare @ FN varchar (20), @ s varchar (1000) Set @ FN = C: \ test.xls Set @ s = microsoft.jet.oledb.4.0 , Data source = + @ FN +; user id = admin; password =; extended properties = excel 5.0 Set @ s = select * from opendatasource (+ @ s +)...Sheet1 $ Exec (@ s) * / Select cast (cast (科目编号 the numeric (10.2) nvarchar (255)) + 转换后的别名 From opendatasource (microsoft.jet.oledb.4.0 , data source = C: \ test.xls; user id = admin; password =; extended properties = excel 5.0)...Xactions / * * * * * * * * * * * * * * * * * * * * * * excel导到远程sql Insert opendatasource ( sqloledb, data source = 远程ip; user id = Sa; password = 密码 ).库名.dbo.表名 (列名1, 列名2) Select 列名1, 列名2 From opendatasource (microsoft.jet.oledb.4.0 , data source = C: \ test.xls; user id = admin; password =; extended properties = excel 5.0)...Xactions / * * 导入文本文件 Exec master..xp _ cmdshell BCP dbname..tablename in C: \ dt.txt - C - sservername - USA - ppassword 注意 s中的s一定要大写: - / * * 导出文本文件 Exec master..xp _ cmdshell BCP dbname..tablename out C: \ dt.txt - C - sservername - USA - ppassword 或 Exec master..xp _ cmdshell BCP select * from dbname..tablename queryout C: \ dt.txt - C - sservername - USA - ppassword 导出到txt文本, 用逗号分开 Exec master..xp _ cmdshell BCP 库名..表名 out D: \ tt.txt - C - t, - U SA - P password Bulk insert 库名..表名 From C: \ test.txt With ( Fieldterminator = ;, Rowterminator = \ n ) - / * dBASE iv文件 Select * from Openrowset (microsoft.jet.oledb.4.0 dBASE IV; HDR =; IMEX = 2; database = C: \ , select * from [客户资料 4.dbf]) - * / - / * dBASE iii文件 Select * from Openrowset (microsoft.jet.oledb.4.0 dBASE III; HDR =; IMEX = 2; database = C: \ , select * f