This post is a continuation of the first post. In this example we are working with a text file that is not a csv file or a tab-delimited file.

Here is the code from the first stored procedure. The name of the database is BulkInsertNoFormatText. The name of the incoming text file is rpt2.txt. It can be named just about anything you want, but it has a .txt file extension. In this example, the file is located in the incoming folder in the C: drive, as you can see from the code below in the BULK INSERT statement in the next code listing.

[DeleteRowsInReport]

	SET NOCOUNT ON;
	DECLARE @rowsdeleted AS int;
	DELETE FROM dbo.InReport
	SET @rowsdeleted = @@ROWCOUNT;  -- just for debugging reasons
	PRINT cast(@rowsdeleted as varchar) + ' rows were delete here'

[BulkInsertFile]

	BULK INSERT [BulkInsertNoFormatText].[dbo].[InReport]
	FROM 'C:\incoming\rpt2.txt'
	WITH
	(
	DATAFILETYPE = 'char',
	--FIELDTERMINATOR = ',',   Do not need this in un-delimited file (is only here FYI)
	ROWTERMINATOR = '0x0a', 
	FIRSTROW = 2   /* Start at row 2, or whatever row makes sense as long as you
                          are sure to capture the data you need, but it may be best to
                          start at row 1 for completeness.  */
	)

Here is how the table InReport looks after the bulk insert. There is only one column in this table.

[CopyInRepToInRepHist]

	SET NOCOUNT ON;
	-- The columns of table dbo.InReportHistory are:
	-- Id, SystemDateTime, InRow
	-- Id is an Identity and will automatically increment by 1.
	DECLARE @dt as datetime
	SET @dt = GETDATE()

	INSERT INTO dbo.InReportHistory 
	(SystemDateTime, InRows)
	SELECT @dt, irep.InRows
	FROM dbo.InReport AS irep;

[FromInReportToReports]

	SET NOCOUNT ON;
	
	DECLARE @rowtext as varchar(1000)
	DECLARE @filename as varchar(100)
	DECLARE @source as varchar(50)
	DECLARE @codenumber as varchar(4)
	DECLARE @transnumber as varchar(10)
	DECLARE @transamount as varchar(19)

	SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%File name:%');
	SET @filename = RTRIM(SUBSTRING(@rowtext,12,100))
	PRINT @filename
	--
	SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%Source:%');
	SET @source = RTRIM(SUBSTRING(@rowtext,12,100))
	PRINT @filename
	--
	SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%code number:%');
	SET @codenumber = SUBSTRING(@rowtext,25,4)
	PRINT @codenumber

	SELECT * INTO #temptbl
	FROM dbo.InReport
	WHERE InRows LIKE '%Transaction number:%'
	
	WHILE (SELECT COUNT(*) FROM #temptbl) > 0
		BEGIN
			SELECT TOP 1 @rowtext = InRows FROM #temptbl
			SET @transnumber = SUBSTRING(@rowtext,21,5)
			SET @transamount = LTRIM(SUBSTRING(@rowtext,49,8))
			INSERT INTO dbo.Reports (FullFileName, SourceCompany, CodeNumber, TransNumber, TransAmount)
				VALUES (@filename, @source, @codenumber, @transnumber, @transamount);
			DELETE #temptbl Where InRows = @rowtext
		END

Scripts for creating the three tables

CREATE TABLE [dbo].[InReport](
	[InRows] [varchar](1000) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[InReportHistory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SystemDateTime] [datetime] NOT NULL,
	[InRows] [varchar](1000) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Reports](
	[FullFileName] [varchar](200) NULL,
	[SourceCompany] [varchar](100) NULL,
	[CodeNumber] [varchar](4) NULL,
	[TransNumber] [varchar](5) NULL,
	[TransAmount] [varchar](8) NULL
) ON [PRIMARY]