Reading Excel Data into SQL Server 7

Question

Reading Excel Data into SQL Server 7

Stewart Joslyn

SSCertifiable

Points: 6131
More actions
July 14, 2005 at 5:12 am

#165416

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?!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

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.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

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.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

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.