quote:

---

(besides, I'm not a fan of DTS)

---

1 Flintstone         Bedrock      
2 Fred         Father
2 Wilma        Mother
2 Pebbles      Daughter
2 Dino         Pet
1 Rubble             Bedrock
2 Barney       Father
2 Betty        Mother
2 Bam Bam      Son
1 Slate              Bedrock
2 Mr.          Boss
1 Rockhead           Bedrock
2 Joe          Co-worker

CREATE PROC isp_FixedWidthFileLoader
AS

DECLARE   @FeedName varchar(255)
	, @FileRootName varchar(20)
	, @FileExtvarchar(3)
	, @FilePath varchar(255)
	, @LoadFreq char(1)
	, @FilenameAndPath varchar(278)
	, @CommandString varchar(8000)
	, @BatchId Int
	, @LayoutCD varchar(5)
	, @LayoutName varchar(50)
	, @RecordIdentStart Int
	, @RecordIdentLen Int
	, @RowCount int

DECLARE CSR CURSOR FOR

	SELECT FeedName, FileRootName, FileExt, FilePath, LoadFreq FROM FixedWidthFeed

OPEN CSR

FETCH NEXT CSR INTO @FeedName, @FileRootName, @FileExt, @FilePath, @LoadFreq

WHILE @@FETCH_STATUS = 0
	BEGIN

-- -----------------------------------------------------------------
-- Let's go fishing..does the file even exist?
-- -----------------------------------------------------------------

	SELECT @CommandString = 'DIR '+ @FilePath+@FileRootName, @FileNameAndPath = @FilePath+@FileRootName

	Delete From Load_Folder

	Insert Into Load_Folder exec master..xp_cmdshell @CommandString

	Delete From Load_Folder_Parsed

	Insert Into Load_Folder_Parsed (Create_Time, File_Size, File_Name )
	Select	  Convert(datetime,Substring(dir_output,1,8)
		+ ' ' 
		+ (Substring(dir_output,11,5) 
		+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
		, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size 
		, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
	  From 	  Ledger_Folder
	 Where  Substring(dir_output,1,1) <> ' '
	   And (Substring(dir_output,1,1) <> ' ' 
	   And  Substring(dir_output,25,5) <> '<DIR>')

--Does the file exist?

	IF EXISTS (SELECT 1 FROM Load_Folder_Parsed WHERE File_Name = @FileRootName)

--yup, lets prepare the Batch Control Table

	BEGIN
		INSERT INTO BatchControl (FeedName, FeedDate, FileName, FilesSize, FileDate)
		SELECT @FeedName, GetDate(), File_Name, File_Size, CreateTime
		FROM Load_Folder_Parsed WHERE File_Name = @FileRootName		


-- Some "cheating" here because of I'm running the code in snippets and don't want to redeclare local vars

--		INSERT INTO BatchControl (FeedName, FeedDate, FileName, FileSize, FileDate)
--		SELECT 'Bretts Test Feed', GetDate(), 'Test.txt', 0, '1960-10-24'


-- Let's go get the batchId so we can keep track of things

		SELECT @BatchId = Max(BatchId) FROM BatchControl

-- -------------------------------------------------------------------------------------
-- Let's make some magic
-- -------------------------------------------------------------------------------------

		DELETE FROM FixedWidthLoad

		@CommandString = 'BULK INSERT FROM FixedWidthLoad' + '''' + @FileNameAndPath + ''''
				+ 'WITH (FORMATFILE = '+ ''''+ 'D:\Data\FixedWidthLoad.fmt'+ ''''+ ')'

		EXEC(@CommandString)

--Again the cheating thing

--BULK INSERT FixedWidthLoad FROM 'D:\Data\Test.txt'
--WITH (FORMATFILE = 'D:\Data\FixedWidthLoad.fmt')


-- OK We're loaded...assign the BatchId

		UPDATE FixedWidthLoad 
			SET BatchId = @BatchId 
	  	 WHERE BatchId IS NULL

-- Again with the cheating thing

--	UPDATE FixedWidthLoad 
--		SET BatchId = 1 
--	  WHERE BatchId IS NULL

--SELECT * FROM BATCHControl

-- ------------------------------------------------------------------------------
-- Need to build a loop here for every different record type within the feed...
-- ------------------------------------------------------------------------------


		DECLARE CSR2 CURSOR FOR
		
			SELECT LayoutCD, LayoutName, RecordIdentStart, RecordIdentLen 
			  FROM FixedWidthLayoutType
			 WHERE FeedName = @FeedName
		
		OPEN CSR2
		
		FETCH NEXT CSR2 INTO @LayoutCD, @LayoutName, @RecordIdentStart, @RecordIdentLen 
		
		WHILE @@FETCH_STATUS = 0
			BEGIN

-- ---------------------------------------------------------------------------------------------------
-- Let's perform some Data Integrity Checks
-- ---------------------------------------------------------------------------------------------------

--DECLARE @SQL varchar(8000)
		SELECT @SQL = ''
		SELECT @SQL = @SQL + SQL FROM (
		SELECT 'SELECT * FROM FixedWidthLoad' AS SQL
		UNION ALL 
		SELECT  ' WHERE 1=1'
		UNION ALL
		SELECT	'   AND ISNUMERIC(SUBSTRING(DataRow,'
			+CONVERT(varchar(5),FieldStartPos)+','
			+CONVERT(varchar(5),FieldLength)+'))'
			+' <> 1 AND '
			+'SUBSTRING(Datarow,1,1) = '+''''+'D'+''''
		--	+'SUBSTRING(Datarow,'+@RecordIdentStart+','+@RecordIdentLen+') = '+''''+@LayoutCD+''''
		FROM FixedWidthLayout
		WHERE FieldDataType NOT IN ('char','varchar', 'datetime','smalldatetime')
		AND FeedName = 'Bretts Test Feed'
		AND LayoutCD = 'D'
		) AS XXX
		
		--SELECT @SQL
		
		EXEC(@SQL)
		
		SELECT '@@ROWCOUNT:' = Convert(varchar(25),@@ROWCOUNT)



		DECLARE @SQL varchar(8000)
		SELECT @SQL = ''
		SELECT @SQL = @SQL + SQL FROM (
		SELECT 'SELECT * FROM FixedWidthLoad' AS SQL
		UNION ALL 
		SELECT  ' WHERE 1=1'
		UNION ALL
		SELECT	'   AND ISDATE(SUBSTRING(DataRow,'
			+CONVERT(varchar(5),FieldStartPos)+','
			+CONVERT(varchar(5),FieldLength)+'))'
			+' <> 1 AND '
			+'SUBSTRING(Datarow,1,1) = '+''''+'D'+''''
		--	+'SUBSTRING(Datarow,'+@RecordIdentStart+','+@RecordIdentLen+') = '+''''+@LayoutCD+''''
		FROM FixedWidthLayout
		WHERE FieldDataType IN ('datetime','smalldatetime')
		AND FeedName = 'Bretts Test Feed'
		AND LayoutCD = 'D'
		) AS XXX
		
		SELECT @SQL
		
		EXEC(@SQL)
		
		SELECT '@@ROWCOUNT:' = Convert(varchar(25),@@ROWCOUNT)

-- ------------------------------------------------------------------------------------------------------------
-- Now let's get the data out and in to it's new home
-- -----------------------------------------------------------------------------------------------------------
		
		DECLARE @SQL varchar(8000)
		SELECT @SQL = ''
		SELECT @SQL =  @SQL + SQL 
		FROM (
		SELECT '  INSERT INTO '+REPLACE(FileRootName+'_'+LayoutName,' ','_')+ ' ('+FieldName AS SQL
			, l.FeedName
			, 1 AS SQL_GROUP
			, 1 AS ROW_ORDER
		  FROM FixedWidthLayout l
		INNER JOIN FixedWidthLayoutType r1 
			ON l.FeedName = r1.FeedName
		       AND l.LayoutCd = r1.LayoutCD
		       AND l.FieldOrdPos = 1
		       AND l.LayoutCD = 'D' 
		INNER JOIN FixedWidthFeed r2 
			ON l.FeedName = r2.FeedName
		     WHERE l.FeedName = 'Bretts Test Feed'
		UNION ALL
		SELECT ', ' + FieldName
			, FeedName
			, 2 AS SQL_GROUP
			, FieldOrdPos
		  FROM FixedWidthLayout
		 WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		AND FieldOrdPos <> 1
		UNION ALL
		SELECT ')'
			, FeedName
			, 3 AS SQL_GROUP
			, 1 AS FieldOrdPos
		  FROM FixedWidthLayout
		 WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		AND FieldOrdPos = 1
		UNION ALL
		SELECT '  SELECT SUBSTRING(DataRow,'
			+Convert(varchar(4),FieldStartPos)+','
			+Convert(varchar(4),FieldLength)+') AS ' + FieldName AS SQL
			, FeedName
			, 4 AS SQL_GROUP
			, 1 AS FieldOrdPos
		  FROM FixedWidthLayout
		 WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		AND FieldOrdPos = 1
		UNION ALL
		SELECT ', SUBSTRING(DataRow,'
			+Convert(varchar(4),FieldStartPos)+','
			+Convert(varchar(4),FieldLength)+')  AS ' + FieldName AS SQL 
			, FeedName
			, 5 AS SQL_GROUP
			, FieldOrdPos
		  FROM FixedWidthLayout
		 WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		AND FieldOrdPos <> 1
		UNION ALL
		SELECT ' FROM FixedWidthLoad'
			, FeedName
			, 6 AS SQL_GROUP
			, 1 AS FieldOrdPos
		  FROM FixedWidthLayout
		 WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		AND FieldOrdPos = 1
		UNION ALL
		SELECT ' WHERE SUBSTRING(DataRow,'+Convert(varchar(4),RecordIdentStart)+','+Convert(varchar(4),RecordIdentLen)+') = '
			+ '''' + LayoutCD + '''' 
			, FeedName
			, 7 AS SQL_GROUP
			, 1 AS FieldOrdPos
		FROM FixedWidthLayoutType
		WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		UNION ALL
		SELECT ' AND BatchId = ' + Convert(varchar(15),1)
			, FeedName
			, 8 AS SQL_GROUP
			, 1 AS FieldOrdPos
		FROM FixedWidthLayoutType
		WHERE LayoutCD = 'D'
		   AND FeedName = 'Bretts Test Feed'
		) AS XXX
		--ORDER BY FeedName, SQL_GROUP, ROW_ORDER
		
		--Produces:
		
		SELECT LEN(@SQL), @SQL
		
		EXEC(@SQL)


--Produces:

--INSERT INTO Test_Detail_Record ( Record_Type
--       , Row_Date
--       , Produce
--       , Produce_Amt
--)
--  SELECT SUBSTRING(DataRow,1,1) AS Record_Type
--       , SUBSTRING(DataRow,2,10)  AS Row_Date
--       , SUBSTRING(DataRow,13,8)  AS Produce
--       , SUBSTRING(DataRow,21,11)  AS Produce_Amt
--    FROM FixedWidthLoad
--WHERE SUBSTRING(DataRow,1,1) = 'D'
--AND BatchId = 1

-- ------------------------------------------------------------------------------------------------------
-- End of CRS2 WHILE LOOP
-- ------------------------------------------------------------------------------------------------------
		END
-- ------------------------------------------------------------------------------------------------------
-- End of File Existance Check 
-- ------------------------------------------------------------------------------------------------------
	END

-- --------------------------------------------------------------------------------------------------------
-- Lets Audit the Data - So far only set up for Record Counts 
-- --------------------------------------------------------------------------------------------------------

SELECT SQL FROM (

SELECT '  SELECT COUNT(*) AS '+REPLACE(LayoutName,' ','_')+'_COUNT FROM '
	+REPLACE(FileRootName+'_'+LayoutName,' ','_')  AS SQL
	, l.FeedName
	, 1 AS SQL_GROUP
	, 1 AS ROW_ORDER
  FROM FixedWidthLayout l
INNER JOIN FixedWidthLayoutType r1 
	ON l.FeedName = r1.FeedName
       AND l.LayoutCd = r1.LayoutCD
       AND l.FieldOrdPos = 1
--     AND l.LayoutCD = 'D' 
INNER JOIN FixedWidthFeed r2 
	ON l.FeedName = r2.FeedName
     WHERE l.FeedName = 'Bretts Test Feed'

--Produces

SELECT COUNT(*) AS Detail_Record_COUNT FROM Test_Detail_Record 


-- ------------------------------------------------------------------------------------------------------
-- This should be the end of the loop for CSR1...go on an process the nect file.
-- ------------------------------------------------------------------------------------------------------

END
GO

CREATE TABLE BatchControl (
	  BatchId 	Int IDENTITY(1,1)
	, FeedName 	varchar(255)
	, FeedDate 	datetime
	, FileName 	varchar(255)
	, FileSize 	int
	, FileDate 	datetime
)
GO

CREATE TABLE FixedWidthLoad (
	  DataRow 	varchar(7500)
	, BatchId 	Int
	, RowId 	int IDENTITY (1,1)
)
GO

CREATE TABLE FixedWidthFeed (
	  FeedName	varchar(255)
	, FileRootName	Varchar(20)
	, FileExt	varchar(3)
	, FilePath	varchar(255)
	, LoadFreq	Char(1)
)
GO 

CREATE TABLE FixedWidthLayoutType (
	  FeedName		varchar(255)
	, LayoutCD		varchar(5)
	, LayoutName		Varchar(50)
	, RecordIdentStart 	Int
	, RecordIdentLen	Int
	, AuditInd		Char(1)
	, AuditStart		int
	, AuditLength		int
	, InclideInCountsInd	int
)
GO 

CREATE TABLE FixedWidthLayout (
	  FeedName	varchar(255)
	, LayoutCD	varchar(5)
	, FieldName 	varchar(50)
	, FieldOrdPos	int
	, FieldDataType	varchar(20)
	, FieldStartPos	int
	, FieldLength	int
)
GO

CREATE TABLE FixedWidthConstr (
	  FeedName	varchar(255)
	, FieldName 	varchar(50)
	, FieldConstr	varchar(255)
)
GO

CREATE TABLE [dbo].[Load_Folder_Parsed] (
	[Create_Time] [datetime] NULL ,
	[File_Size] [int] NULL ,
	[File_Name] [varchar] (255) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[load_folder] (
	[dir_output] [varchar] (255) NULL 
) ON [PRIMARY]
GO

-- Need to add all contraints, FK's PK's...sorry lazy me again


INSERT INTO FixedWidthFeed (FeedName, FileRootName, FileExt, FilePath, LoadFreq)
SELECT 'Bretts Test Feed', 'Test', 'txt', 'D:\Data\', 'D'
UNION ALL
SELECT 'Bretts Test Feed 2', 'Test2', 'txt', 'D:\Data\', 'D'
GO

INSERT INTO FixedWidthLayoutType (FeedName, LayoutCD, LayoutName, RecordIdentStart, RecordIdentLen
				, AuditInd, AuditStart, AuditLength, IncludeInCountsInd)
SELECT 'Bretts Test Feed', 'H','Header Record',1,1, 'N', 0, 0, 'N'
UNION ALL
SELECT 'Bretts Test Feed', 'D','Detail Record',1,1, 'N', 0, 0, 'Y'
UNION ALL
SELECT 'Bretts Test Feed', 'T','Trailer Record',1,1, 'Y', 48, 11, 'N'
UNION ALL
SELECT 'Bretts Test Feed 2', 'H','Header Record',1,1, 'N', 0, 0, 'N'
UNION ALL
SELECT 'Bretts Test Feed 2', 'D','Detail Record',1,1, 'N', 0, 0, 'Y'
UNION ALL
SELECT 'Bretts Test Feed 2', 'T','Trailer Record',1,1, 'Y', 48, 11, 'N'
GO

DELETE FROM FixedWidthLayout
GO

-- CREATE FILE INPUT DEF #1

INSERT INTO FixedWidthLayout (FeedName, LayoutCD, FieldName, FieldOrdPos, FieldDataType, FieldStartPos, FieldLength)
SELECT 'Bretts Test Feed', 'H','Record_Type', 1, 'char', 1, 1
UNION ALL
SELECT 'Bretts Test Feed', 'H','Feed_Date', 2, 'DateTime', 10, 10
UNION ALL
SELECT 'Bretts Test Feed', 'H','Descr', 3, 'varchar', 20, 30
UNION ALL
SELECT 'Bretts Test Feed', 'D','Record_Type', 1, 'char', 1, 1
UNION ALL
SELECT 'Bretts Test Feed', 'D','Row_Date', 2, 'datetime', 2, 10
UNION ALL
SELECT 'Bretts Test Feed', 'D','Produce', 3, 'varchar', 13, 8
UNION ALL
SELECT 'Bretts Test Feed', 'D','Produce_Amt', 4, 'decimal(10,2)', 21, 11
UNION ALL
SELECT 'Bretts Test Feed', 'T','Record_Type', 1, 'char', 1, 1
UNION ALL
SELECT 'Bretts Test Feed', 'T','Feed_Date', 2, 'DateTime', 10, 10
UNION ALL
SELECT 'Bretts Test Feed', 'T','Descr', 3, 'varchar', 20, 30
UNION ALL
SELECT 'Bretts Test Feed', 'T','Record_Count', 4, 'Int', 50, 11
GO

-- CREATE FILE INPUT DEF #2

INSERT INTO FixedWidthLayout (FeedName, LayoutCD, FieldName, FieldOrdPos, FieldDataType, FieldStartPos, FieldLength)
SELECT 'Bretts Test Feed 2', 'H','Record_Type', 1, 'char', 1, 1
UNION ALL
SELECT 'Bretts Test Feed 2', 'H','Feed_Date', 2, 'DateTime', 10, 10
UNION ALL
SELECT 'Bretts Test Feed 2', 'H','Descr', 3, 'varchar', 20, 30
UNION ALL
SELECT 'Bretts Test Feed 2', 'D','Record_Type', 1, 'char', 1, 1
UNION ALL
SELECT 'Bretts Test Feed 2', 'D','Row_Date', 2, 'datetime', 2, 10
UNION ALL
SELECT 'Bretts Test Feed 2', 'D','Produce', 3, 'varchar', 13, 8
UNION ALL
SELECT 'Bretts Test Feed 2', 'D','Produce_Amt', 4, 'decimal(10,2)', 21, 11
UNION ALL
SELECT 'Bretts Test Feed 2', 'T','Record_Type', 1, 'char', 1, 1
UNION ALL
SELECT 'Bretts Test Feed 2', 'T','Feed_Date', 2, 'DateTime', 10, 10
UNION ALL
SELECT 'Bretts Test Feed 2', 'T','Descr', 3, 'varchar', 20, 30
UNION ALL
SELECT 'Bretts Test Feed 2', 'T','Record_Count', 4, 'Int', 50, 11
GO


-- Create the Tables from your definitions

DECLARE @SQL varchar(8000)

--not that you would but you could
--SELECT @SQL = SQL FROM (

SELECT SQL FROM (
    SELECT 'CREATE TABLE ' + REPLACE(FileRootName+'_'+LayoutName,' ','_') + '( ' AS SQL
	, l.FeedName, LayoutCD, 1 AS SQL_GROUP, 1 AS ROW_ORDER 
      FROM FixedWidthFeed l
INNER JOIN FixedWidthLayoutType r
	ON l.FeedName = r.FeedName
UNION ALL
    SELECT '     ' + FieldName + ' ' + 
	+ CASE WHEN FieldDatatype In ('char', 'varchar') 
	       THEN FieldDataType + '(' + CONVERT(varchar(4),FieldLength) +  ')'
	       ELSE FieldDataType
	  END AS SQL
	, FeedName, LayoutCD, 2 AS SQL_GROUP, 1 AS ROW_ORDER     
     FROM FixedWidthLayout l  
    WHERE FieldOrdPos = 1
UNION ALL
    SELECT '   , ' + FieldName + ' ' + 
	+ CASE WHEN FieldDatatype In ('char', 'varchar') 
	       THEN FieldDataType + '(' + CONVERT(varchar(4),FieldLength) +  ')'
	       ELSE FieldDataType
	  END AS SQL
	, FeedName, LayoutCD, 2 AS SQL_GROUP, FieldOrdPos AS ROW_ORDER     
     FROM FixedWidthLayout l  
    WHERE FieldOrdPos <> 1
UNION ALL
    SELECT ')' AS SQL
	, FeedName, LayoutCD, 3 AS SQL_GROUP, 1 AS ROW_ORDER 
      FROM FixedWidthLayoutType
UNION ALL
    SELECT 'GO' AS SQL
	, FeedName, LayoutCD, 4 AS SQL_GROUP, 1 AS ROW_ORDER 
      FROM FixedWidthLayoutType
) AS XXX
Order by FeedName, LayoutCD, SQL_GROUP, ROW_ORDER     

-- Which Produces:

CREATE TABLE Test_Detail_Record( 
     Record_Type char(1)
   , Row_Date datetime
   , Produce varchar(8)
   , Produce_Amt decimal(10,2)
)
GO
CREATE TABLE Test_Header_Record( 
     Record_Type char(1)
   , Feed_Date DateTime
   , Descr varchar(30)
)
GO
CREATE TABLE Test_Trailer_Record( 
     Record_Type char(1)
   , Feed_Date DateTime
   , Descr varchar(30)
   , Record_Count Int
)
GO
CREATE TABLE Test2_Detail_Record( 
     Record_Type char(1)
   , Row_Date datetime
   , Produce varchar(8)
   , Produce_Amt decimal(10,2)
)
GO
CREATE TABLE Test2_Header_Record( 
     Record_Type char(1)
   , Feed_Date DateTime
   , Descr varchar(30)
)
GO
CREATE TABLE Test2_Trailer_Record( 
     Record_Type char(1)
   , Feed_Date DateTime
   , Descr varchar(30)
   , Record_Count Int
)
GO

-- FixedWidthLoad.fmt

--8.0
--3
--1	SQLCHAR	0	7500	"\r\n"	1	DataRow	SQL_Latin1_General_CP1_CI_AS
--2	SQLCHAR	0	0	""	0	BatchId	SQL_Latin1_General_CP1_CI_AS
--3	SQLCHAR	0	0	""	0	RowId	SQL_Latin1_General_CP1_CI_AS

-- Test.txt

--H	 07/04/2003Header Record For Test File
--D07/04/2003 APPLES         1.00
--D07/04/2003 PEACHES 10000000.00
--D07/04/2003 PEACHES 10000000.00
--T	 07/04/2003Trailer Record For Test File  00000000003
--Dont include these last three lines
--123456789012345678901234567890123456789012345678901234567890
--         1         2         3         4         5         6



SELECT * FROM Test_Detail_Record
SELECT * FROM FixedWidthLayout
SELECT * FROM FixedWidthLayoutType
SELECT * FROM FixedWidthFeed
SELECT * FROM FixedWidthLoad
SELECT * FROM BatchControl




DROP TABLE FixedWidthLoad
GO
DROP TABLE FixedWidthFeed
GO
DROP TABLE FixedWidthLayout
GO
DROP TABLE FixedWidthLayoutType
GO
DROP TABLE BatchControl
GO
DROP TABLE FixedWidthConstr
GO
DROP TABLE Load_Folder_Parsed
GO
DROP TABLE Load_Folder
GO
DROP TABLE Test_Detail_Record
GO
DROP TABLE Test_Header_Record
GO
DROP TABLE Test_Trailer_Record
GO
DROP TABLE Test2_Detail_Record
GO
DROP TABLE Test2_Header_Record
GO
DROP TABLE Test2_Trailer_Record
GO