Fixed Width Import with Multiple Data Lines

[craigK]

i have a data file that is fixed width with rows of data records split into several rows. The row identifiers are 01,02,03 and 04 and always occur.

i need to import this into a SQL table as one row of data. can this type of file be imported using dts?

0199988777N AT01JOHN BORROWER
02JOHN DOE
035826 N OMAHA NE681544108Y
0401877824PA0007500000041866320020902
0199988777N AT01WILLIAM BORROWER
02WILLIAM JOHNSON
03728 W ESTHERVILLE IA513341244Y
0401825824PA0015000000023621500000000

 

[JayKusch]

Here's my idea ... create 5 working tables and one perm table, lets call them T1,T2,T3,T4,TMain and TDone for simplicity.

Create TMain w/ 2 columns. An ID field set as an integer and also set as an Identity field. This will give us a number we can increment thru. Then a field named RecordText set as a VarChar(500) to get all the data from the file in there. Better to have too much than too little - LOL.

Next task would be to import the fixed length file into the TMain.

Now create tables T1 thru T4. Start w/ an ID field like above, w/ an identity, and then having the correct fields that match the header number we may be processing. SO ... T1 would have and as an example ...

ID Int (= AutoIncrement)
SomeNbr Char(8) (= 99988777)
SomeCode1 Char(1) (= 'N')
SomeCode2 Char(4) (= 'AT01')
Name1 Char(25)(= 'JOHN')
TypePerson Char(25)(= 'BORROWER')

You get the idea. Since it was a fixed length file, you should have all the lengths to fill in the sizes above. Do this for T2-T4 tables based on the '02','03' or '04' header.

You should now create the TDone table, our finalized table, that includes all the fields that are in the text file.

Now with the table loaded we create a couple of working vars and use them in a WHILE loop to identify and then parse our record to the appropriate table.

SO ...

declare @TotRecs Int
declare @NextRecNbr Int
declare @RecordTxt VarChar(500)

select @TotRecs = Count(*) from TMain
set @NextRecNbr = 1
set @RecordTxt = ''

WHILE @NextRecNbr <= @TotRecs

BEGIN

SELECT @RecordTxt = RecordTxt FROM TMain
WHERE ID = @NextRecNbr

IF SUBSTRING(@RecordTxt,1,2) = '01
BEGIN
INSERT INTO T1 ( SomeNbr,
SomeCode1,
SomeCode2,
Name1,
TypePerson )
VALUES
( SUBSTRING(@RecordTxt,3,8),
SUBSTRING(@RecordTxt,12,1),
SUBSTRING(@RecordTxt,14,4),
SUBSTRING(@RecordTxt,19,25),
SUBSTRING(@RecordTxt,35,25) )
END

IF SUBSTRING(@RecordTxt,1,2) = '02
BEGIN
INSERT INTO T2 ( *Fill In fields* )
VALUES
( *File In Substring code* )
END

IF SUBSTRING(@RecordTxt,1,2) = '03
BEGIN
INSERT INTO T3 ( *Fill In fields* )
VALUES
( *File In Substring code* )
END

IF SUBSTRING(@RecordTxt,1,2) = '04
BEGIN
INSERT INTO T4 ( *Fill In fields* )
VALUES
( *File In Substring code* )
END

SET @NextRecNbr = @NextrecNbr + 1
END

The code above uses the SUNSTRING Function to parse out the desired field based on its starting position and length. So you may want to try each of the SUBSTRING commands in Query Analyzer first to get the settings right.

OK ... since your records in the file where in order and should have been loaded that way into the table, the logic follows that the first record read w/ be '01' and put in table T1 w/ and ID of 1. Next record read in the loop will be '02' and put into the T2 table w/ and ID of 1. Same for '03' & '04' going into T3 and T4 w/ an ID of 1. SO ... you may be seeing what is happening. The 5th record read is really the next TRUE record to start and should have an '01' in it. It would then be place in the T1 table w/ ID = 2 and then next 3 records will be '02','03' and '04' which get put in the T2..T4 tables w/ ID of 2

When this is all completed you can then run a query, and use it for your last import into TDone that may look like this...

SELECT *
FROM T1
JOIN T2
ON (T1.ID = T2.ID)
JOIN T3
ON (T1.ID = T3.ID)
JOIN T4
ON (T1.ID = T4.ID)

WaLa ... You are there. Hope this helps and if you need some more tips or explaination ... post again

Thanks

J. Kusch

 

[craigK]

This is great. Thanks! I'm almost there. When i import my text file into Tmain i get an error. " Cannnot insert null values into column ID, table Tmain, column does not allow nulls, insert fails.

i cant see any nulls values in the import file, there must be something at the end of the file i cant see. is there a way not to use the identity seed in Tmain?

 

[JayKusch]

The ID field should be defined as as Identity Field when the table is designed. It must also have the "Allow Nulls" check box UNCHECKED. By DEFAULT, when you are in design mode for the table creation, you will set the ID field to be an Int (Integer). In the section below called "Columns" you will see an item named "Identity" that is set to NO by default. Change it to YES and SQL Server will automatically change the "Allow Nulls" check box to be blank as it should be.

You will not be importing any info into this field directly from the text file. As you import records, SQL Server will take care of incrementing the ID field for you. This is one reason why you will NOT list ID as on of the fields in INSERT INTO statements above.

Thanks

J. Kusch

 

[craigK]

Thats just it. I can't understand why it thinks i'm trying to insert into the ID field. i've mapped my dts import to the recordtxt field and am ingnoring the ID field. The import reads entire file, then looks like it errors on the last record.

 

[JayKusch]

Lets try another trick ... Lets run this command in QA ...

INSERT INTO TMain 
  EXEC Master.dbo.xp_cmdshell 'type C:\DelimitedFile.txt'

Of course you need to change the path and filename to match your situation.

Just as a clarification, we are importing the whole record in the file into the RecordTxt field of TMain. We are not mapping out the fields yet ... I am sure you are aware of this but I am just double checking - LOL.

Thanks

J. Kusch

 

[craigK]

yes everthing from the text file was going into recordtxt.
the Insert into Tmain code above worked fine? Must be something with the dts i have configured wrong. Could i just use the Insert code above? How best should it incorporated into the other code.

thanks again!

 

[JayKusch]

Sure ... the code above will work just fine as is. I use the same method in several of my packages. Just have to be sure that if this is a scheduled import, that the path and filename remains the same. If not, you will have to add additional code to handle the various events.

Thanks

J. Kusch

 

[craigK]

Yes, the filename changes every day. How would it work?

 

[JayKusch]

Does it use a date part as the filename. As in for today it might look like ... 060404.txt? Describe what the file naming convention is. I have had to do many conversion w/ varying file conventions, so I have a few tricks we might try to make it work.

Thanks

J. Kusch

 

[craigK]

File is sent to us daily as 2004-03-20.001

i've use the activex part of dts to change the output filename on daily exports, but i'm not sure it the cmdshell code should be part of the DTS package.

 

[JayKusch]

Slam Dunk!
Lets do this ...

DECLARE @SQL_Command VarChar(1000)
DECLARE @FileDate    Char(14)

SET @FileDate = 
(SELECT SUBSTRING(CONVERT(Char,GetDate(),120),1,10) + '.001')

SET @SQL_Command = '
INSERT INTO TMain 
  EXEC Master.dbo.xp_cmdshell ' + '''' + 'type C:\' + @FileDate + ''''

EXEC (@SQL_Command)

Thanks

J. Kusch

 

[craigK]

Thanks for all your help. i really appreciate it!

Craig.

 

[JayKusch]

FYI ... If the file is sent to you with yesterdays data and the file name represents the data within, ie you recieved the file today and the filename is 2004-06-03.001 ... the above code needs to be modified w/ the use of the DateAdd function which will step the date back, in this case a day, or more depending on your needs. It would look like ...

DECLARE @SQL_Command VarChar(1000)
DECLARE @FileDate    Char(14)

SET @FileDate = 
(SELECT SUBSTRING(CONVERT(Char,[b]DateAdd(dd,-1,GetDate())[/b],120),1,10) + '.001')

SET @SQL_Command = '
INSERT INTO TMain 
  EXEC Master.dbo.xp_cmdshell ' + '''' + 'type C:\' + @FileDate + ''''

EXEC (@SQL_Command)

Thanks

J. Kusch