have data in excel to import to ms sql 2000??


    How do I get this infomation into a MS sql server2000. Where i want no month in the D2-D5 colum for example 06/1986 would just be 1986 once in the database. Should i convert this to a .CSV file? or will DTS let my import this data the way it is? Also there are more than 5rows, this was just an example. THX for the help.

  • The picture did not come through but..

    Take a look at this site and see if it helps you any.



  • Hi.

    Its hard to say without seeing your example: maybe type it?

    DTS allows scripting using ActiveX, so for instance if you have a text field MM/YYYY you can easily extract the year with right(field, 4) and insert this into the required field.

    If i have missed the point, let me know


  • That's weird when i review the image i inserted i can see it and even when i pull up the thread just now. Anyway let me know if this helps...


  • Heres an example of how to get excel data from a SQL task

    INSERT INTO customer

    SELECT F4 AS Cust

    , F5 AS LVL

    , CONVERT(decimal(4,2),F6) AS CoYS

    FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0; HDR=NO; IMEX=1;Database=\\NTsvr12\MIS\Rebate\customerrebate.xls'

    , 'SELECT * FROM [Rebate Index$] where F4 is not null and F4 not like ''par%'''


    by bastardising this you should be able to get the data in the format you want.

  • Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply