Greetings,
I've posted this question in other news groups (msdn and a few others) without any response.
I hope I don't get into any trouble for posting this question here, but I reach out for your help.
I have an excel file stored in a data base table as an image data type. I would like to somehow read the data from the excel file and extract its data into a data base table using t-sql.
I've used sql statements such as the following but this requires the excel file to reside as a physical file on the sql servers file system.
insert into SpreadsheetDataDump (f1,f2,f3,f4,f5,f6)
Select f1,f2,f3,f4,f5,f6
FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source="D:\spreadsheets\test.xls";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"')
...WorksheetName$
In my case, I do not want the excel file to reside on the file system because I require many different users to submit excel files. I just cannot afford to expose the file system to so many users, so I've created a web interface for the users to upload the excel file into the database.
As I mentioned, the excel file gets inserted into a table as an image data type. Now I want to select this image data using t-sql and somehow extract the excel spreadsheet data. If I can't do this, can I at least parse the image data into a physical file somewhere on the SQL server? I don't mind parsing it to disk at this point because by now the process is handled by SQL and the end user is out of picture.
Can this be done? Or do I have to parse out the image data as an excel file somewhere on the sql servers file system?
If I have to parse the image data to the file system, can this be done in t-sql? i.e. does t-sql have the ability to stream image data to the file system either natively or by extended stored procedures? (I want to avoid writing a dll wrapper to do this.)
I'm using SQL Server 2000 on Windows 2000 server.
Thanks for any help you can provide.
-Mike
