Database Discussion - SQL Server

Print Page | Close Window

SQL Server - Import from Excel

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=2630
Printed Date: 29 March 2026 at 12:52pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Server - Import from Excel

Posted By: Gary
Subject: SQL Server - Import from Excel
Date Posted: 12 May 2003 at 5:23am

Is there any way that I can get SQL Server to import data from the first sheet within an Excel Spreadsheet.....

I have a DTS package which points to a specifically named sheet (eg Sheet1 as default), but what if Sheet1 doesn't exist (ie named as something else such as mySheet)??? Is there a way to get the DTS to import the first sheet, regardless of its name ???

Thx

Replies:

Posted By: Bunce
Date Posted: 12 May 2003 at 8:54pm

Well you can write the DTS code in VBScript in which case you may be able to access a collection object from within Excel.

Something like:

mySpreadsheet.Sheets(1)

Not sure though.. Try searching google - might come up with some options..

Cheers,
Andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.

Posted By: Flamewave
Date Posted: 19 May 2003 at 10:39pm

You can also use the Enterprise manager to import data from any excel worksheet. Open up the server in Enterprise manager, right-click on it, and goto all-tasks -> import data. When it asks for the Data Source, choose Microsoft Excel 97-200 (or the appropiate version) from the list, and then browse to the excel file on your computer. Then select where you want the data to goto on the next screen. Eventually you will come to a screen where it will ask you what "tables," or sheets you want to import, select the ones you want, and continue on with the process. Its fairly simple and straight forward. Hope that helps you out.

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: Bunce
Date Posted: 19 May 2003 at 11:07pm

I think Gary knows this.

He wants to know how he can refer the the worksheet within a spreadhseet by its ordinal (number) rather than its name.

Cheers,
Andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.

Posted By: Gary
Date Posted: 20 May 2003 at 2:36am

Andrew is right - I am fully aware and capable of design dts packages to suit my needs.

However, if you want to import from Excel, you have to choose which sheet you want to import from (pretty much in the same way that you choose a table from a db). The problem is that the Excel spreadsheet may not contain 'Sheet1' and therefore this cause the DTS to fail is I implicity define the named sheet.

So, the solution that I am after is a way of importing from the first sheet, regardless of its actual name. I want it to work along the lines of what you can do with a recordset. In other words, if I have a recordset (rs) then I can reference the contained columns by name or by their sequence (0,1,2,3,etc - rs(1)).