批量处理大量TXT格式的数据导入到数据库中

--最终的存储表
CREATE TABLE [dbo].[tab](filename varchar(100),
[列 0] [varchar](50) NULL, [列 1] [varchar](50) NULL, [列 2] [varchar](50) NULL,
[列 3] [varchar](50) NULL, [列 4] [varchar](50) NULL, [列 5] [varchar](50) NULL,
[列 6] [varchar](50) NULL, [列 7] [varchar](50) NULL, [列 8] [varchar](50) NULL,
[列 9] [varchar](50) NULL, [列 10] [varchar](50) NULL, [列 11] [varchar](50) NULL,
[列 12] [varchar](50) NULL
) ON [PRIMARY]


GO
--存放文件内容的中间表
Create table tab1(a varchar(5000))
GO
--存放目录中的所有文件名
Create table tab2(a varchar(5000))




--先把待处理的文件导入到临时表中
insert into tab2
EXEC xp_cmdshell 'dir d:\*.txt /b'




--用游标依次把文件导入到数据库的中间表中
declare @fn varchar(100),@sql varchar(100)
declare mc cursor scroll for
select * from tab2 where a is not null
 
--打开游标 
open mc


-- 
fetch next from mc into @fn
while @@FETCH_STATUS=0
begin
--
truncate table tab1

--把文件信息导入到中间表中
set @sql = 'bcp test..tab1 in d:\' + @fn + ' -T -c'
exec master..xp_cmdshell @sql


--转换中间表的格式再存入到指定的表中
insert into tab
select @fn,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13] 
from (
select ID, row_number() over(partition by id order by new_no ) new_no,col=substring(t.a,b.number,charindex('|',t.a+'|',b.number)-b.number)
from (select ROW_NUMBER() over(order by a ) ID,GETDATE() new_no ,* from tab1)  t
join master..spt_values b on b.type='P'
where charindex('|','|'+t.a,b.number)=b.number and PATINDEX('%|%',t.a) > 0
)tt pivot (max(col) for new_no in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])) as t

--
fetch next from mc into @fn
end


-- 销毁游标
close mc
deallocate mc




-- 查看结果
select * from tab


--清除数据
truncate table tab
truncate table tab1

truncate table tab2

---------------------------------------问题描述-----------------------------------------------

在同一目录下，有几千个txt文件。
1、每个文件名都包含有重要信息，需要利用；
2、每个txt文件的结构是一样的：以|分列，以；分行保存的一些数据。
像这样的
3、每个txt文件的最后一个；后面还有一个guid需要删除。
求助：
1、把所有文件名写入其自身，并复制多条，每行数据前加上文件名。
2、把每个txt文件末尾的guid删除。

如下：
文件名：cdg_431106_002_000599_20171106161301.txt
文件内容：
4311062017110031|431106|431106019002004036|4400102130000231373011|张三|302828197612026724|0599|类型1|360.00|20171106|37527|0000|O.K.;
4311062017110031|431106|431106030019007050|4400102130009582652011|李四|30282819540920061X|0599|类型2|360.00|20171106|37527|6218|未知原因。;
e532f6b37dd5a22b14dd87d237d6ef4e