I want to use bulk insert to load a text file into a table, but my coding is only able to load half of the text file because my text file has two parts:
- (ID, CompanyName and Date) as one part; and
- (Name Dept, Status, Age and Gender)
The (ID, CompanyName and Date) section appears only once in the file. I want it repeated in the rows when inserted to the table.
My coding is only able to load with one format text file.
Below is my text file example:
A1242
CompanyName
Date 2019-11-28 18:58:04
Name Department Status Age Gender
John R&D Active 23 Male
James R&D Active 21 Male
How can I use bulk insert to load the above text file into a table looking like the below example?
A1242 CompanyName 2019-11-28 18:58:04 John R&D Active 23 Male
A1242 CompanyName 2019-11-28 18:58:04 James R&D Active 21 Male
Below is my coding:
-- Procedure Load Text File
ALTER procedure [dbo].[ImportFiles]
@FilePath varchar(1000) = 'c:\Transfer\' ,
@ArchivePath varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask varchar(1000) = 'bcp*.txt' ,
@MergeProc varchar(128) = 'MergeBCPData'
AS
set nocount on
declare @ImportDate datetime
select @ImportDate = getdate()
declare @FileName varchar(1000) ,
@File varchar(1000),
declare @cmd varchar(2000)
create table ##Import (s varchar(8000), sd varchar(8000))
create table #Dir (s varchar(8000), sd varchar(8000))
/*****************************************************************/
-- Import file
/*****************************************************************/
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd
delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR=''\t'''
select @cmd = @cmd + ',FIRSTROW = 5 '
select @cmd = @cmd + ',ROWTERMINATOR = ''\n'')'
truncate table ##Import
-- import the data
exec (@cmd)
-- remove filename just imported
delete #Dir where s = @FileName
exec @MergeProc
-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath +
@FileName
exec master..xp_cmdshell @cmd
end
drop table ##Import
drop table #Dir
--Procedure Insert
/****** Object: StoredProcedure [dbo].[MergeBCPData] Script Date:
11/28/2019
8:32:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[MergeBCPData]
AS
set nocount on
-- insert data to production table
insert BCPData
(
fld1 ,
fld2 ,
fld3 ,
fld5
--fld4
)
select
fld1 = substring(s,1,12) , --testing
fld2 = substring(s,15,4) , --testing
fld3 = s ,
fld5 = s
--fld4 = convert(datetime,substring(s,26,19))
from ##Import
--Exetuce the stored procedur
exec ImportFiles 'D:\Piccolo\' , 'D:\Piccolo\Archive\' , 'bcp*.txt',
'MergeBCPData'
I cannot change the file format and external application is my backup plan.