將復雜文本文件中的批量插入到表中

我想使用批量插入將文本文件載入到表中，但我的編碼只能載入一半的文本文件，因為我的文本文件有兩個部分：

• （ID、公司名稱和日期）作為一部分；和
• （姓名部門、狀態、年齡和性別）

（ID、公司名稱和日期）部分在文件中只出現一次。我希望它在插入表格時在行中重複。

我的編碼只能載入一種格式的文本文件。

下面是我的文本文件範例：

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

如何使用批量插入將上述文本文件載入到如下例所示的表中？

A1242 CompanyName 2019-11-28 18:58:04 John R&D Active 23 男

A1242 CompanyName 2019-11-28 18:58:04 James R&D Active 21 男

下面是我的編碼：

-- 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'

我無法更改文件格式，外部應用程序是我的備份計劃。

SQL ServerBULK INSERT可以導入的文件類型非常有限，並且您的文件類型超出了它可以執行的範圍。

最好的方法是更改​​文件格式。

其次是讓外部應用程序解析文件並將數據上傳到表。

如果以上都不可能，那麼您應該使用以下 SQL Server 解決方案：

1. 假設“header”部分在一個文件中只出現一次，分兩部分載入這個文件。將第一部分載入到單獨的單列臨時表中（使用LASTROW參數）。第二部分將使用您目前的方法載入。然後，您將使用 tSQL 連接數據。
2. 創建一個具有 ID（將充當行號）和單列的臨時表VARCHAR( MAX)。將文件按原樣載入到此表中，而不進行列拆分。然後，使用 SQL 查詢，將數據處理成您需要的格式。程式碼會相當難看。

一些有用的資訊：Splitting delimited strings

更新

SQL Server 實施選項 1 將遵循以下步驟。注意：這是虛擬碼，以避免程式碼過長，我無法測試。

create table ##ImportHeader (RowID INT IDENTITY, RowData VARCHAR( 3000 ))
-- Load the first 3 rows into a separate table.
bulk insert ##ImportHeader from [YourFile] with( LASTROW = 3, FIELDTERMINATOR='\t' ROWTERMINATOR = '\n' )

-- Load the rest of the data
-- Your import table needs to have the same number of columns as the data you are loading
create table ##Import (RowID INT IDENTITY, Name VACRHAR( 200 ), Department VARCHAR( 200 ), Status VARCHAR( 10 ), Age INT, Gender VARCHAR( 10 ))
bulk insert ##Import from [YourFile] with( FIRSTROW = 5, FIELDTERMINATOR='\t' ROWTERMINATOR = '\n' )

-- Join data
SELECT CompanyID.RowData, CompanyName.RowData, CAST( SUBSTRING( CompanyDate.RowDate, 5, 50 ) AS DATETIME ),
  D.*
FROM ##Import AS D
   INNER JOIN ##ImportHeader AS CompanyID ON CompanyID.RowID = 1
   INNER JOIN ##ImportHeader AS CompanyName ON CompanyName.RowID = 2
   INNER JOIN ##ImportHeader AS CompanyDate ON CompanyDate.RowID = 3

您正在做的是兩次載入同一個文件。第一次載入前 3 行，第二次載入其餘的結構化數據。

批量插入（在我看來）是載入文件最難的方法之一。

您可以嘗試使用此處描述的導入導出嚮導：

https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-ver15

右鍵點擊數據庫，轉到任務，導入，然後執行專業人員的操作：點擊並嘗試各種方式，直到載入良好，然後保存包。

如果您的數據是從兩個單獨的文本文件中載入的 - 您如何加入記錄？您是否依賴行號？

引用自：https://dba.stackexchange.com/questions/254616