--首先将excel表内容导入到一个全局临时表
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into '+@tbname+' from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
)...[Sheet1$]'
exec(@sql)
--然后用bcp从全局临时表导出到文本文件
set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql
我用
select *
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=d:\yhzz.xls'
--,Sheet1$)
)...[Sheet1$]
提示:
服务器: 消息 7314,级别 16,状态 1,行 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table.