Introduction and Question re: Row Delimiter probs

[Jennifer Kenney]

Hi, I'm Jennifer Kenney, I'm mostly SQL/TSQL, but I'm using DTS for obvious
DTS things, and I've got an issue with DTS respecting the Row Delimiters in
a text file (source) connection.

The fixed-width text file has a CRLF row delimiter, visible when I read it
in Word with the view options set to view all, and when I checked to see if
I could import it into Excel, it read the CRLF just fine BUT!!!

The individual rows are sometimes shorter than they ought to be; the last
field in the row isn't always present, and my data pump, instead of seeing
the Row Delimiter and moving the data down to the next row, is filling data
into the trailing field from the next record, then cutting off record 2
after the 30 chars that field allows and starting the next row with record
3.

Any advice?

JBK

[Ilya Margolin]

Jennifer,

Then the file is not fixed-width in a strict sense. If you opened the file
and filled up missing width it would be working.

Ilya

Post by Jennifer Kenney
Hi, I'm Jennifer Kenney, I'm mostly SQL/TSQL, but I'm using DTS for obvious
DTS things, and I've got an issue with DTS respecting the Row Delimiters in
a text file (source) connection.
The fixed-width text file has a CRLF row delimiter, visible when I read it
in Word with the view options set to view all, and when I checked to see if
I could import it into Excel, it read the CRLF just fine BUT!!!
The individual rows are sometimes shorter than they ought to be; the last
field in the row isn't always present, and my data pump, instead of seeing
the Row Delimiter and moving the data down to the next row, is filling data
into the trailing field from the next record, then cutting off record 2
after the 30 chars that field allows and starting the next row with record
3.
Any advice?
JBK

[Jennifer Kenney]

Any ideas about how to fill that in automatically?

Post by Ilya Margolin
Jennifer,
Then the file is not fixed-width in a strict sense. If you opened the file
and filled up missing width it would be working.
Ilya

Post by Jennifer Kenney
Hi, I'm Jennifer Kenney, I'm mostly SQL/TSQL, but I'm using DTS for

obvious

Post by Jennifer Kenney
DTS things, and I've got an issue with DTS respecting the Row Delimiters

in

Post by Jennifer Kenney
a text file (source) connection.
The fixed-width text file has a CRLF row delimiter, visible when I read it
in Word with the view options set to view all, and when I checked to see

if

Post by Jennifer Kenney
I could import it into Excel, it read the CRLF just fine BUT!!!
The individual rows are sometimes shorter than they ought to be; the last
field in the row isn't always present, and my data pump, instead of seeing
the Row Delimiter and moving the data down to the next row, is filling

data

Post by Jennifer Kenney
into the trailing field from the next record, then cutting off record 2
after the 30 chars that field allows and starting the next row with record
3.
Any advice?
JBK

[s***@cs.fsu.edu]

I handle things like this with ActiveX scripts. My preference for text
manipulations is PERL. If you have ever programmed in C or even C++,
then PERL isn't to terribly difficult and is well suited for text
manipulation.

I think you'd want to read the file line by line, each time into a
local string variable, and check the length of the string, then output
that string with additional spaces to ensure it is the proper length.

[Jennifer Kenney]

Post by s***@cs.fsu.edu
I handle things like this with ActiveX scripts. My preference for text
manipulations is PERL. If you have ever programmed in C or even C++,
then PERL isn't to terribly difficult and is well suited for text
manipulation.
I think you'd want to read the file line by line, each time into a
local string variable, and check the length of the string, then output
that string with additional spaces to ensure it is the proper length.

Ugh. So do a connection before the data pump and adjust the text file
there?

[Ilya Margolin]

Jennifer,

That is the idea. Plus to what ***@cs.fsu.edu suggested you can do it
in an ActiveX task in the same DTS package prior to reading the file by data
pump. Look up FileSystemObject documentation.

Ilya

Post by Jennifer Kenney

Post by s***@cs.fsu.edu
I handle things like this with ActiveX scripts. My preference for text
manipulations is PERL. If you have ever programmed in C or even C++,
then PERL isn't to terribly difficult and is well suited for text
manipulation.
I think you'd want to read the file line by line, each time into a
local string variable, and check the length of the string, then output
that string with additional spaces to ensure it is the proper length.

Ugh. So do a connection before the data pump and adjust the text file
there?

[Jennifer Kenney]

Thanks, everybody who wrote in on this. This morning I put the text file
through an intermediary data pump to a holding text file, setting the
properties on the original file to character delimiter (using a character
that does not appear in the file), and setting the transformation to

DTSDestination("Col001") = DTSSource("Col001") + Space(
1321-Len(DTSSource("Col001")) )

where '1321' is the desired record length. Setting the field delimiter to a
non-existent character forced it into recognizing the row delimiter, the
data pump does its thing almost instantaneously, and DTS reads the resulting
file flawlessly.

Many thanks!

JBK

Post by Jennifer Kenney
Hi, I'm Jennifer Kenney, I'm mostly SQL/TSQL, but I'm using DTS for
obvious DTS things, and I've got an issue with DTS respecting the Row
Delimiters in a text file (source) connection.
The fixed-width text file has a CRLF row delimiter, visible when I read it
in Word with the view options set to view all, and when I checked to see
if I could import it into Excel, it read the CRLF just fine BUT!!!
The individual rows are sometimes shorter than they ought to be; the last
field in the row isn't always present, and my data pump, instead of seeing
the Row Delimiter and moving the data down to the next row, is filling
data into the trailing field from the next record, then cutting off record
2 after the 30 chars that field allows and starting the next row with
record 3.
Any advice?
JBK

[Shankar]

Hi Jennifer,

I too have the same issue. Can you please explain me in detail wt to be done.

Thanks, everybody who wrote in on this. This morning I put the text file
through an intermediary data pump to a holding text file, setting the
properties on the original file to character delimiter (using a character
that does not appear in the file), and setting the transformation to

DTSDestination("Col001") = DTSSource("Col001") + Space(
1321-Len(DTSSource("Col001")) )

where '1321' is the desired record length. Setting the field delimiter to a
non-existent character forced it into recognizing the row delimiter, the
data pump does its thing almost instantaneously, and DTS reads the resulting
file flawlessly.

Many thanks!

JBK

From http://www.developmentnow.com/g/103_2005_4_0_0_510950/Introduction-and-Question-re-Row-Delimiter-probs.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/