SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 use DTS to import ever-changing Excel file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 01/27/2003 :  11:30:40  Show Profile  Reply with Quote
I'm needing to import an Excel spreadsheet to SQL Server 7.0 on a regular interval and I'll be using DTS to do so. SQLDTS.com has a nice article on importing from excel using ranges, and my problem sort of falls into this category.

The spreadsheet I need to import will expand and contract from time-to-time, depending on the whims of the folks who manage it. So, importing via Excel ranges wouldn't seem to work for me b/c the range will always change as rows are added/removed.

Taking this problem into account, my current plan is to require the data provider to deliver a clean extract to the server sharepoint at a regular interval. I will then schedule a DTS package to check for a new file and do the pump as necessary.

My questions:

1. I will be either overwriting or archiving the existing SQL table each time with the new update. How do I automate the process within the DTS code to dynamically create the new SQL table based on the newly-added/newly-removed columns on the Excel side?

2. Is it possible to use Excel ranges to extract only the rows/columns I need from the Excel spreadsheet, even if this spreadsheet is expanding/contracting all the time? If so, I can amend the current plan and allow the Excel data managers to work off of a single XLS, the same XLS I'll access via DTS.

The only thing I could think of here would be to designate a specific range in the excel spreadsheet, i.e. from A1 to R900, that would provide MORE than enough space to hold the data I want and then import this range every time, cleaning up empty rows and columns that are unused. If the Excel manager needs to exceed the outer bound of the range, we need to talk and then adjust the DTS code accordingly. This seems like a messy solution, which is why I'm considering asking the data provider to give me a clean extract every time -- a hassle for him.

I would appreciate any links you can provide to additional DTS resources.

thx

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 01/27/2003 :  13:46:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
I would shy away from dynamically creating a new table to fit the structure of the Excel file. That just sounds really messy to me. I would prefer the last suggestion you had, of having more columns and more rows than necessary, if it would mean that the table structure could be (relatively) fixed.

I have used named ranges with varying rows and columns, it required a little bit of VB code to select the range on the spreadsheet. There are some methods of the Cell and Range object that allow you to select/move to the last row and column of a range, and then define a named range based on that. You'd have to have them use the same Excel file and add this as an AutoClose macro, or create an Excel template with that code and require them to create a new one using the template. Either way it might be a little bit more of a burden on them, but you'd be surprised how quickly people get into a good habit. Once the range is named then DTS should have no problem picking it up.

The transformations are another story though, especially if columns are added or dropped. If you can fix the number of columns and stick with that number, even if it rarely gets that high, you'll make the DTS part A LOT simpler.

http://www.sqldts.com/ is a good source for some more advanced DTS stuff, they might have a better solution.

Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 01/28/2003 :  06:52:22  Show Profile  Reply with Quote
Consider careful handling of this in Excel.
The idea being you create a "near" workbook that references the editable workbooks that your folks work in, then the import is based
on "your" workbook which links to the relevent areas in the other workbooks. This way you can protect the workbook that the DTS package uses. I think you can use just a single sheet in a lone workbook as well but I believe you loose the ability to protect it.

Voted best SQL forum nickname...."Tutorial-D"
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 01/28/2003 :  10:12:18  Show Profile  Reply with Quote
Thanks for the tips. I have found a nice piece of code at sqldts.com that does the named range import.

Being new to scheduling DTS packages, I'm fuzzy on the actual import of the excel data to the SQL Server table. I name the range and hit that same range every time with my DTS package, but, since the people editing the XLS are adding new columns within the range all the time, how do I tell SQL Server to auto-create the new table every time, adding the new columns?

The sqldts.com article at link below shows how to do the basics with named ranges, but, as you can see, the table structure seems hardwired into the package. Can I tell the package to recreate the table each time, making sure the new columns are added, or does this happen automatically?

http://sqldts.com/default.aspx?6,101,243,0,0

thx



Edited by - steelkilt on 01/28/2003 10:20:44
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000