Importing text files - DTS help needed

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • JCC
    #1

    Importing text files - DTS help needed

    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


    Tags: None
    • Dan Guzman
      #2
      Re: Importing text files - DTS help needed

      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.


      --
      Hope this helps.

      Dan Guzman
      SQL Server MVP

      "JCC" <gofyself@wrong .address.comwro te in message
      news:fdE1h.3173 1$w07.17227@new sfe6-win.ntli.net...
      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
      >

        Comment

        • JCC
          #3
          Re: Importing text files - DTS help needed


          "Dan Guzman" <guzmanda@nospa m-online.sbcgloba l.netwrote in message
          news:SYG1h.947$ m54.525@newssvr 14.news.prodigy .com...
          >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.
          >
          >
          --
          Hope this helps.
          >
          Dan Guzman
          SQL Server MVP
          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


            Comment

            Previous template Next
            Working...