txt文件导入存储过程

CREATE PROC [dbo].[Sp_InputExcel2003toSQL]
@dir NVARCHAR(100),--EXCEL文件存放路径：如D:\ExcelFiles
@tabname NVARCHAR(50) --定义导入到数据库中的表名
AS
SET NOCOUNT ON
CREATE TABLE #t([filename] NVARCHAR(1000))
IF RIGHT(@dir,1)<>'\'
    SET @dir=@dir+'\'   
DECLARE @cmd NVARCHAR(1000)
declare @str varchar(30)
declare @code varchar(30)
declare @riqi varchar(30)
  
SET @cmd = N'dir "' + @dir + '*.xls*" /B'

INSERT #t EXEC master..xp_cmdshell @cmd
DELETE #t WHERE [filename] IS NULL

--开始导入目录下的文件
SET @S = ''
SELECT @S = @S + 'INSERT '+@tabname+' SELECT '''+parsename(replace([filename],'_成交明细_','.'),3)
+''','''+parsename(replace([filename],'_成交明细_','.'),2)
+''',成交时间,成交价,价格变动,[成交量(手)],[成交额(元)],性质
FROM  OPENROWSET( ''microsoft.jet.oledb.4.0'', ''Excel 5.0;HDR=YES;IMEX=1;DATABASE='+@DIR+[filename]+'''
            ,''SELECT * FROM [sheet1$]'') '
#t     
EXEC(@S)
SET NOCOUNT OFF

转载于:https://www.cnblogs.com/mingdep/archive/2011/10/18/2216364.html