Each day I download perhaps 20 text files from a mainframe. I wrote an
ASP script that opens the files, reads the contents, and extracts data
to be imported into SQL Server. There's not a huge amount of data in
those files, perhaps 500 kb daily, but the process takes some time.
I'm looking for ways to make it more efficient.

DTS allows efficient importing of fixed-width text files. But I'm not
sure how to use it with the files I download. Instead of having the
usual one-row-one-record format, these have up to three rows per
record. Each row has a "header," if you will, that specifies what's to
follow. For example, a file might look like this:

Dog1Spot0000
Dog2Mastiff0
Dog3Purebred

My script goes through the file line by line, extracts the dog's name
from row 1, the breed from row 2, and pedigree from row 3, and finally
constructs the SQL statement.

I'd sure love to be able to grab a chunk of data using DTS and just
plunk it into SQL Server. Any ideas of how to do this?

--Brent

If your already using VBScript to parse the text files that could be easily
ported over to an ActiveX DTS task.

Jim

"Brent Bigler" <bbigler (AT) yahoo (DOT) com> wrote

You could do this through DTS using the text file driver. Set your total
line length to 40. This is your three line lengths plus 4 chars for 2x
Cr+Lf. The DTS parser ignores line delimiters until it has counted the
correct number of fixed width characters, so you can import data with
Cr+Lf's embedded. You can use this your advantage and then perhaps using a
script transform strip out the padding 0's and Cr+Lf's.


--
Darren Green
http://www.sqldts.com

"Brent Bigler" <bbigler (AT) yahoo (DOT) com> wrote

Thanks for your tips. Specifying the total length of the data,
including the control returns, seems to work, except that I have run
into another snag that I forgot about when describing the data
structure. Each text file comes with additional header trailer lines.
The resulting file looks something like this:

Head01Westminster
Dog1Spot0000
Dog2Mastiff0
Dog3Purebred
Dog1Buddy000
Dog2Corgi000
Dog3Purebred
Trail8lines

I would need DTS -- or something -- to strip out the first and last
lines. I guess I could write a script to do this, and then pass
control over to DTS, but is there any easier way?

Thanks again!

--Brent

In message <b07890ce.0403030910.7490e57c (AT) posting (DOT) google.com>, Brent
Bigler <bbigler (AT) yahoo (DOT) com> writes
You can skip the first line by setting the option in the text file
properties source, but the last line is a problem. You can let it error,
and just ignore it.

Or

You can write a script to parse the entire file and strip of the header
and trailer rows. You could of course make the current three line format
into a single line as well.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org