本实例实现遍历文件夹下的Excel文件，实现导入功能
CREATE proc [dbo].[pr_遍历_文件夹_建立文件和出库单关系] @Path nvarchar(260),@日期 char(8),@库房 varchar(30)='廊坊'
as
/*
遍历某天（8位日期做文件夹名）的文件夹的出库单列表，和excel文件，
建立文件关系，统计司机工作量
*/
--exec pr_遍历_文件夹_建立文件和出库单关系 'E:\ftp\ChuKuDan\BeiJing\','20190926'
--exec pr_遍历_文件夹_建立文件和出库单关系 'E:\ftp\ChuKuDan\BeiJing\','20190927'
--select * from 出库单扫描件 where 日期='20190925'
--select * from 出库单扫描件 where 日期='20190926'
--select * from 出库单扫描件 where 日期='20190927'
--DECLARE @Path nvarchar(260)
--SET @Path = 'E:\ftp\ChuKuDan\BeiJing\'
--declare @日期 char(8)
--set @日期='20220704'
DECLARE @Path_DT nvarchar(260)
IF OBJECT_ID('tempdb..#pr_遍历_文件夹_建立文件和出库单关系_File') IS NOT NULL DROP TABLE #pr_遍历_文件夹_建立文件和出库单关系_File
CREATE TABLE #pr_遍历_文件夹_建立文件和出库单关系_File
(
id int IDENTITY, --编号
directory nvarchar(260), --路径
depth int, --深度，相对与@path
IsFile bit --0文件夹1文件名成
)
--1 先判断是否存在 这个日期的文件夹 如果不存在就不处理 begin
INSERT #pr_遍历_文件夹_建立文件和出库单关系_File EXEC master.dbo.xp_dirtree @path = @path,@depth = 1,@file = 1
if not exists(select * from #pr_遍历_文件夹_建立文件和出库单关系_File where directory=@日期)
begin
if exists(select * from 出库单读取记录 where 日期=@日期 and isnull(库房,'廊坊')=@库房)
update 出库单读取记录 set 读取次数=读取次数+1 where 日期=@日期 and isnull(库房,'廊坊')=@库房
else
insert 出库单读取记录(日期,读取次数,读取成功,时间,库房) select @日期,读取次数=1,读取成功=0,时间=GETDATE(),@库房
select error='没有['+@日期+']这天的文件夹！',ID=0
return
end
--1 先判断是否存在 这个日期的文件夹 如果不存在就不处理 end
--2 读取 @日期 的文件夹，并建立 文件目录 和 出库单和文件对应关系 begin
delete #pr_遍历_文件夹_建立文件和出库单关系_File
SET @Path_DT = @Path+@日期+'\'
INSERT #pr_遍历_文件夹_建立文件和出库单关系_File EXEC master.dbo.xp_dirtree @path = @Path_DT,@depth = 0,@file = 1
alter table #pr_遍历_文件夹_建立文件和出库单关系_File add AllDirectory nvarchar(1000) --全路径
alter table #pr_遍历_文件夹_建立文件和出库单关系_File add 出库单号 varchar(200)
alter table #pr_遍历_文件夹_建立文件和出库单关系_File add 文件名称 nvarchar(520)
update #pr_遍历_文件夹_建立文件和出库单关系_File set AllDirectory=directory
DECLARE @depth int, @depthMax int
UPDATE #pr_遍历_文件夹_建立文件和出库单关系_File SET AllDirectory = @Path_DT + directory WHERE depth = 1
SELECT @depth = 2,@depthMax = MAX(depth) FROM #pr_遍历_文件夹_建立文件和出库单关系_File
WHILE @depth <= @depthMax
BEGIN
UPDATE A SET
AllDirectory = (
SELECT TOP 1
AllDirectory
FROM #pr_遍历_文件夹_建立文件和出库单关系_File
WHERE depth = @depth - 1
AND IsFile = 0
AND id < A.id
ORDER BY id DESC
) + N'\' + AllDirectory
FROM #pr_遍历_文件夹_建立文件和出库单关系_File A
WHERE depth = @depth
SET @depth= @depth + 1
END
--2 读取 @日期 的文件夹，并建立 文件目录 和 出库单和文件对应关系 begin
--declare @日期 char(8)
--set @日期='20190927'
declare @email varchar(100)
set @email= '12@126.com'
declare @sql varchar(8000)
set @sql=
'select
''
-----------------------------------------------------------------------
begin try
delete 出库单_excel_内容 where 文件名=''''''+directory+''''''
declare @sql varchar(8000)
set @sql=''''
insert 出库单_excel_内容
select 文件名,图片,出库单号 from (
SELECT ''''''''''+directory+'''''''''' as ''''''''文件名'''''''',*--图片,出库单号
FROM OPENDATASOURCE(''''''''Microsoft.ACE.OLEDB.12.0'''''''',
''''''''Data Source=''+AllDirectory+'';Extended Properties=Excel 8.0'''''''')...[''''''''''+REPLACE(directory,''.xls'','''')+''$'''''''']
) t
''''
exec(@sql)
end try
begin catch
insert inno_Data.dbo.短信邮件(发送人,提醒方式,提醒时间,发送时间,标题,提醒内容,发送目标)
select
发送人=''''系统'''',
提醒方式=''''邮件'''',
提醒时间=GETDATE(),发送时间=null,
标题='''''+@日期+' ''+directory+'' excel文件 访问错误'''',
提醒内容=''''''+AllDirectory+'''''',
发送目标='''''+@email+'''''
end catch
-----------------------------------------------------------------------
''
from #pr_遍历_文件夹_建立文件和出库单关系_File where directory like ''%.xls'''
--print @sql
exec Pr_ExecSQL2 @sql
select id=IDENTITY(int,1,1),a.*,文件名称=CAST('' as nvarchar(520))
into #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系
from 出库单_excel_内容 a join #pr_遍历_文件夹_建立文件和出库单关系_File b on a.文件名=b.directory
where b.directory like '%.xls'
--当出现图片目录时，需要把目录去掉，只留下文件名 例如 图片：20190926\img-925164015-0036.jpg 应该修改为：img-925164015-0036.jpg
--select REVERSE(left(REVERSE('20190926\img-925164015-0036.jpg'),CHARindex('\',REVERSE('20190926\img-925164015-0036.jpg'))-1))
update #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系 set 图片=REVERSE(left(REVERSE(图片),CHARindex('\',REVERSE(图片))-1)) where 图片 like '%\%'
--alter table 出库单扫描件 add 文件名称 nvarchar(520)
--文件名称 按照 出库单号 重新命名文件，当一个 出库单号 出现多个文件时，要按照文件排序
update a
set
文件名称=ltrim(rtrim(a.出库单号))+'-'+cast(b.排序 as varchar)+RIGHT(a.图片,LEN(a.图片)-charindex('.',a.图片)+1)
from
#pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系 a
join
(
select *,排序=ROW_NUMBER() OVER (PARTITION BY 出库单号 ORDER BY 文件名)
from #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系
where 出库单号 in (select 出库单号 from #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系 group by 出库单号 having COUNT(*)>1)
) b on a.id=b.id
update #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系 set 文件名称=ltrim(rtrim(出库单号))+RIGHT(图片,LEN(图片)-charindex('.',图片)+1) where 文件名称=''
update b set b.出库单号=a.出库单号,文件名称=a.文件名称 from #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系 a join #pr_遍历_文件夹_建立文件和出库单关系_File b on a.图片=b.directory
if exists(select * from #pr_遍历_文件夹_建立文件和出库单关系_File where IsFile=1 and directory not like '%.xls' and directory not like '%.csv' and 出库单号 is null)
begin
declare @内容 varchar(8000)=''
select @内容=@内容+replace(AllDirectory,@Path+@日期+'\','')+'<BR>'
from #pr_遍历_文件夹_建立文件和出库单关系_File
where IsFile=1 and depth=2 and 出库单号 is null
insert inno_Data.dbo.短信邮件(发送人,提醒方式,提醒时间,发送时间,标题,提醒内容,发送目标)
select
发送人='系统',
提醒方式='邮件',
提醒时间=GETDATE(),发送时间=null,
标题=@日期+' 有图片没有出库单号',
提醒内容=@内容,
发送目标=@email
end
else
begin
--保存文件和出库单号对应关系
delete 出库单扫描件 where 日期=@日期
and AllDirectory like'%'+(case when @库房='廊坊' then '\BeiJing\' when @库房='廊坊' then '金盏' when @库房='\BJjinzhan\' then '' when @库房='上海' then '\ShangHai\' end)+'%'
insert 出库单扫描件(日期,directory,depth,IsFile,AllDirectory,时间,出库单号,文件名称)
select 日期=@日期,directory,depth,IsFile,AllDirectory,时间=GETDATE(),出库单号,文件名称
from #pr_遍历_文件夹_建立文件和出库单关系_File
where IsFile=1
and directory not like '%.xls'
and directory not like '%.csv'
if exists(select * from 出库单读取记录 where 日期=@日期 and isnull(库房,'廊坊')=@库房)
update 出库单读取记录 set 读取次数=读取次数+1,读取成功=1 where 日期=@日期 and isnull(库房,'廊坊')=@库房
else
insert 出库单读取记录(日期,读取次数,读取成功,时间,库房) select @日期,读取次数=1,读取成功=0,时间=GETDATE(),@库房
end
drop table #pr_遍历_文件夹_建立文件和出库单关系_File
drop table #pr_遍历_文件夹_建立文件和出库单关系_出库单和图片对应关系
1964
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
