By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,183 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,183 IT Pros & Developers. It's quick & easy.

Text file import with DTS

P: n/a
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: +44 131-650-3314
University of Edinburgh Internal ext: 503314
Main Library, George Square FAX: 0131-650-3308
Edinburgh EH8 9LJ
************************************************** **********************
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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: +44 131-650-3314
University of Edinburgh Internal ext: 503314
Main Library, George Square FAX: 0131-650-3308
Edinburgh EH8 9LJ
************************************************** **********************

Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.