July 14, 2005 at 5:12 am
I have about 1000 Excel spreadsheets in a network folder.
I need to extract data from 6 cells of each spreadsheet into a SQL Server 7 table (one record per spreadsheet).
I need to control the process from a stored procedure (a scheduled DTS job is not allowed for this - beyond my control). It would be possible to create and run a DTS job 'on the fly' but my DTS experience is VERY limited.
It would be preferable not to have to set up an extended procedure.
It is acceptable for the job to run for a few minutes.
Any ideas?!
July 14, 2005 at 6:00 am
How about coming at this from another angle. Could you write some script, or maybe an executable would be better, that creates a single spreadsheet containing the ~1000 records and then pumps this data straight into SQL Server? Your stored proc could then just run the exe.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 14, 2005 at 6:04 am
My original design was a separate exe that could read the Excel with VBA, get a bit of extra data from SQL with ADO and an SP and generate a required XML file, but there is an architectural desire to do it all within SQL Server.
July 14, 2005 at 7:25 am
Depends on whether this a one off, all the spreadsheets the same structure and where the six cells are in the spreadsheet.
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\book1.xls', [sheet1$])
will get all the rows from a spreadsheet and depending on where the cells are you could process the rows accordingly.
Not sure that performance will make this work 'in a few minutes' though
Far away is close at hand in the images of elsewhere.
Anon.
July 14, 2005 at 7:38 am
Thanks, good thinking. I share your reservations over performance but there's only one way to find out ...
I suspect that I am going to end up insisting on a dll to get the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply