Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2003-01-27 : 11:30:40
|
| 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
15732 Posts |
Posted - 2003-01-27 : 13:46:32
|
| 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. |
 |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-01-28 : 06:52:22
|
| 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 basedon "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" |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-01-28 : 10:12:18
|
| 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,0thxEdited by - steelkilt on 01/28/2003 10:20:44 |
|
|
|
|
|
|
|
|
use DTS to import ever-changing Excel file