Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Unanswered: How?-- SQL read/import excel worksheets within one workbook???

    Hi all,
    I have a huge data set captured in excel file which contains 90 worksheets.
    There are total 3 worksheets I need to work on.

    I need to load/transfer them into a table. How do I import them into SQL?
    Can SQL read/load excel by reading each worksheet (sheet1, sheet2...sheet90)?

    Any suggestions are welcome and highly appreciated!

    shiparsons

  2. #2
    Join Date
    Feb 2005
    Posts
    88
    Hi all,
    I have posted this question yesterday and have not been responded by anyone. Is this issue very difficult to figure out or this is something SQL can not handle?

    Any comments are highly appreciated!
    shiparsons

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    You could data transformation services (DTS). This might be the easiest but you are going to need a seperate package for each sheet.

    I would import them seperately into new tables then use T-SQL to push them to your target table.

    The path is frought with peril.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2005
    Posts
    88
    I am not sure if I understood what you mean...

    I need to prepare 270 packages ( I have 3*90=270 worksheets) in order to accomplish what I am trying to do?

    shiparsons

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Life is tough.

    If you use DTS you can designate only one excel workbook at a time. However you can import each sheet in a workbook at the same time. So it would be 90 packages.

    Or if each workbook is exactly the same you can reuse the same package 90 times by swapping the files in and out of the same location.

    Or you can create an uber package that fires off ninety other packages.

    Or if all of your worksheets are the same and you have less than 65,000 (the max for excel?) you can cut and paste them all together and do one biggie.

    Or you can use bulk insert or bcp.

    happy hunting.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •