September 20, 2007 at 12:51 am
I have encountered the following problem: I wish to extract data from several very similar excel files. They each contain data for a single year, and have the same structure and several worksheets. However, the worksheet containing the data has different names in every excel file (the name of the year).
The Excel Data Source object has no expressions property so I cannot set at runtime which Worksheet i want to use, and the connection manager only links to the excel file, not to the worksheets themselves.
Neither can I set this property from configuration files.
I found a solution where i would open the xls files from SQL with open rowset, but we dont want to touch the SQL server.
(in the end we simply added named fields to the worksheets but I got really curious) Have any of you encountered a similar problem? Do you know any other solution to set which worksheet I want to use at runtime?
Thanks for any ideas in advance!
September 20, 2007 at 1:46 am
This is a common situation and easily solvable.
The simpliest way would be to loop over the sheets in the file. You DF would be inside a for each loop.
The best way would be to use something like DataDefractor. http://www.datadefractor.com
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply