xls import

select * INTO #tmpMark
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\******(import on 20101124).xls;HDR=YES', ' SELECT * FROM  [*****$]') AS T
 INNER JOIN AS S ON COLLATE Chinese_PRC_90_CI_AS = 
 INNER JOIN AS C ON COLLATE Chinese_PRC_90_CI_AS =

 

 

 IF OBJECT_ID(N'tempdb..#) IS NOT NULL DROP TABLE #

 

 

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

 

 

 

 

 

--run in master database
--drop table EvalRes
USE master
Create table EvalRes (expVal real)
go

--drop Function dbo.Eval
USE master
Create Function dbo.Eval (@exp varchar(5000))
 returns real
as
begin

   declare @ans real
   declare @osqlStmt varchar(5000)

   set @osqlStmt = 'osql.exe -E -q' + '" Insert master..EvalRes select ' + @exp + '"'
   exec master..xp_cmdShell @osqlStmt
   select @ans = expVal From EvalRes
   set @osqlStmt = 'osql.exe -E -q' + '" delete master..EvalRes ' + '"'
   exec master..xp_cmdShell @osqlStmt
   return @ans
end
go
---example call : select master.dbo.Eval ('((4.5)+5)*6-1-(3*7)+3.512')

--To enable xp_cmdshell extended stored procedure, you can run the below code as the sa account or another administrator account;
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
----

 

转载于:https://www.cnblogs.com/sandy_liao/archive/2010/11/25/1887322.html