Text file import with DTS

[Dave Stone]

Does anyone know if it's possible to use the wizard or DTS Designer to
accept a source file with the following simplified format:

<field1label>: <record1field1value>
<field2label>: <record1field2value>
- - - - - - -
<fieldNlabel>: <record1fieldNvalue>

<field1label>: <record2field1value>
<field2label>: <record2field2value>
etc.

i.e. each input record is delimited by {LF}{LF}, and each column by {LF}. Or
will it be necessary to write a Perl script (say) to convert it first into a
..csv file?

Thanks,

Dave

--
************************************************** **********************
Dave Stone e-mail: D.*****@ed.ac.uk
Computing Services Telephone: [phone]
University of Edinburgh Internal ext: 503314
Main Library, George Square FAX: [phone]
Edinburgh EH8 9LJ
************************************************** **********************

[Chuck Conover]

Dave,
I'm not sure if your problem is related to the delimiter or the file
format. If the problem is the delimiter and you can't get the file to read
in at all, then not sure I can help without an actual sample of the file and
some testing.
If the problem is the file format, then I would suggest first reading
your file into a temp table and then running a procedure to massage and make
any data corrections, and insert the massaged data into your production
tables.
I use this for all my DTS packages, even if they are very similar to my
production tables just so I can do any data checking first.

Best regards,
Chuck Conover
www.TechnicalVideos.NET


"Dave Stone" <D.*****@ed.ac.uk> wrote in message
news:c1**********@scotsman.ed.ac.uk...

Does anyone know if it's possible to use the wizard or DTS Designer to
accept a source file with the following simplified format:

<field1label>: <record1field1value>
<field2label>: <record1field2value>
- - - - - - -
<fieldNlabel>: <record1fieldNvalue>

<field1label>: <record2field1value>
<field2label>: <record2field2value>
etc.

i.e. each input record is delimited by {LF}{LF}, and each column by {LF}. Or will it be necessary to write a Perl script (say) to convert it first into a .csv file?

Thanks,

Dave

--
************************************************** **********************
Dave Stone e-mail: D.*****@ed.ac.uk
Computing Services Telephone: [phone]
University of Edinburgh Internal ext: 503314
Main Library, George Square FAX: [phone]
Edinburgh EH8 9LJ
************************************************** **********************

[Dave Stone]

Thanks for your reply, Chuck. I am assuming that it's essentially the
delimiter(s) that is giving the problem. When I define the row delimiter as
{LF}{LF} and hit 'Next', the Text File Properties dialog returns the message
about not finding the row delimiter within 8K. I've checked a dump of the
file, and there certainly is a {LF}{LF}sequence much sooner than that.

Because the individual fields of a record each have a separate line in the
input file, I would set the column delimiter to a single {LF}, but it's not
even giving me the chance to do that. So I reckon it just don't like my row
delimiter.

When I've loaded data before using DTS, the records have been transformed by
a Perl script into a one-long-line-per-record format, with commas as field
separators, and vertical bars as text qualifiers. The process is untidy
though, and I had hoped to find a bulk input route which would handle text
input files in one-column-per-input-line format.

I'm wondering if the similarity of my preferred format to the way XML
documents are normally laid out is a hint?

Dave

"Chuck Conover" <cc******@commspeed.net> wrote in message
news:10**************@news.commspeed.net...

Dave,
I'm not sure if your problem is related to the delimiter or the file
format. If the problem is the delimiter and you can't get the file to read in at all, then not sure I can help without an actual sample of the file and some testing.
If the problem is the file format, then I would suggest first reading
your file into a temp table and then running a procedure to massage and make any data corrections, and insert the massaged data into your production
tables.
I use this for all my DTS packages, even if they are very similar to my production tables just so I can do any data checking first.

Best regards,
Chuck Conover
www.TechnicalVideos.NET