June 24, 2008 at 3:51 pm
I have multiple worksheets all formatted the same in one excel workbook. Is there a way to query, and select and import records from more than one sheet with one command? I can use linked server or openquery to import data currently, but can only do one sheet at a time.
my basic code for importing records from one excel worksheet:
insert into dbo.welltest1
select * from ODBTEST...COY2$
where measure_date between 01/01/95 and 12/31/95
My excel workbook has many sheets of this same format - I would like to batch import them. Any ideas? Thanks
- Charlie D
July 15, 2008 at 6:59 am
I stumbled over your post, and wonder if you have found a solution yet?
My hunch would be to use a SSIS package, containing of a script that gets the names of all worksheets in the workbook (this would mean installing Excel, or the Excel PIA on your server), and then use a loop container with a dataflow to get the data to your table.
(For Excel 2007, it might also be considered to try to read/load the actual xml from the xlsx-file)
I'm very interested to hear what you came up with though.
Peter Rijs
BI Consultant, The Netherlands
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply