Fixed width text import

Question

Fixed width text import

Vidya-389933

SSC-Addicted

Points: 439
More actions
December 21, 2006 at 11:22 am

#68121

Hi,
I'm having problems importing a text file. There are no column delimiters in the file. It has the following data elements:
AcctNo
Name
Street1
Street2
Street3
City
State
Zip
ZipExt
The ZipExt may or may not exist. Each row ends with a CRLF (I checked in a hex editor).
I used the fixed width way of importing the data. However, when there is no ZipExt, DTS considers the CRLF and the first two characters of the next line as the ZipExt. It then skips the next line and doesn't import it. Hence, in a file which has 877 rows, I only get 530 rows of data.
I used a CopyColumn transformation for all columns except the ZipExt and then used an ActiveX transformation for the ZipExt as follows:
If IsNull(DTSSource("Col009")) Then
  DTSDestination("ZipExt") = 0000
Elseif Left(DTSSource("Col009"),2) = Chr(13) & Chr(10) Then
  DTSDestination("ZipExt") = 0000
Else
  DTSDestination("ZipExt") = DTSSource("Col009")
End If
However, even with this, I do not get those missing rows. Please help.

Thanks,
Vidya.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 6964 More actions · Answer 1

I have had this problem getting fixed lenght from a legacy machine where the final field was not always populated. I wrote this vbs script to pad the rows and and add a character to the end which I ignore on the import:

set fso=createobject("scripting.filesystemobject")

set fl= fso.OpenTextFile("originalfile.txt")

set nfl = fso.createtextfile("newfile.txt")

do while not fl.atendofstream

line=fl.readline

do while len(line)<566 ' adjust to the record length you need

line = line & " "

loop

line=line & "X"

nfl.writeline line

loop

Greg Baethke Default port Points: 1442 More actions · Answer 2

As an alternative, you may also just take from the zip field to the end of the line and look at the length of that field. Then your logic could be

If Len(DTSSource("Col008")) = 5 Then

DTSDestination("Zip") = DTSSource("Col008")

DTSDestination("ZipExt") = 0000

Elseif Len(DTSSource("Col008")) = 9 Then

DTSDestination("Zip") = Left(DTSSource("Col008"), 5)

DTSDestination("ZipExt") = Right(DTSSource("Col008"), 4)

Elseif insert other cases for scrubbing or error handling

HTH,

Greg

Thomas Evers Valued Member Points: 72 More actions · Answer 3

Just pull it all in to a table as one field defined Varchar(MaxLen)

Then run a query against that table to parse it into the fields you want.

SQL is much quicker to process the file than a ActiveX script will be.