Business users love Excel. It is there prefered format of data.
If you can get report data into Excel then they rarely require other reports.
The problem comes when you want to take data the other way.
I have an application that has it’s business logic implemented as a set of stored procedures. It is a data transformation application so this is the best place to do this.
We need to source certain data from Excel. OPENDATASOURCE is the recommended solution as follows:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
Aside from the drive mapping problems that you can get when the data is not on a local drive on the server there are the following issues:
Data types are implied from the data. When you let Excel guess it has a better than 50% chance of being wrong.
If there are empty columns for a few rows it assumes the entire row is NULL.
These two can be complete showstoppers.
The only solution was to move to feeding the data from access…
Developer Rants 