Importing text files - DTS help needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JCC - 31 Oct 2006 09:56 GMT

Hi all

Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.

File is >1m rows and >200 columns and is defined by a proprietory (i.e. not
bcp produced) format specification of the form

Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53

and so on.

Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so I
don't have to click 200 times (with all the ensuing errors I could make).

Any help greatly appreciated.

Cheers
Rob

Reply to this Message

Dan Guzman - 31 Oct 2006 13:04 GMT

> but surely there's a way to get DTS to read from a format file so I don't
> have to click 200 times (with all the ensuing errors I could make).

Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Hi all
>
[quoted text clipped - 23 lines]
> Cheers
> Rob

Reply to this Message

JCC - 31 Oct 2006 16:42 GMT

>> but surely there's a way to get DTS to read from a format file so I don't
>> have to click 200 times (with all the ensuing errors I could make).
>
> Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
> Both can read a standard BCP format file.

I have looked into this - but as I said, I don't want to mess around with
bcp if I can avoid it - I had no end of problems with bcp a while back.

It looks like I can't avoid it though!

Cheers
Rob

Reply to this Message