Post reply

Copy Excel Data into SQL Server (This Much Complex Thank You Think)

  • April 19, 2005 at 9:18 pm

    #61126

    Hi,

    My problem is this: I have an Excel file which has several sheets (one for each month), each sheet has the same table structure. The number of Excel sheets increase by one every month. I need to periodically copy the data from all of these sheets into one table in SQL Server. I tried everything. Please help. It's kinda urgent. Please remember the number sheets keep increasing.

    Thanks

  • April 19, 2005 at 10:50 pm

    #553636

    In your DTS package you can have a short ActiveX Script task that extracts a list of sheet names and set the connection properties.

    Open the Excel workbook using an ADO connection. Then from memory, I think its the OpenSchema method that returns a resultset that list the sheets in the woorkbook.

    Take a look at the examples over at http://www.sqldts.com. Specifically those that deal with dynamically setting connection properties and looping.

     

    --------------------
    Colt 45 - the original point and click interface

  • April 20, 2005 at 3:07 am

    #553697

    Thanks Phill: I did something similar. It worked...

Viewing 3 posts - 1 through 2 (of 2 total)

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