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.
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'
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.
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;
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]