I'm importing excel spreadsheets into a Sql Server database. There are two different types of spreadsheets --lets call them type A and type B -- with spreadsheets of type B having some column names which are different than those of type A. Unfortunately, I have no way of knowing which type of spreadsheet is being read before actually issuing a SELECT call on the spreadsheet.
Because of this, when I attempt to select a column which is not in spreadsheet, OpenDataSource errors out and I don't know how to trap the error to issue another SELECT for the other spreadsheet type.
So: 1) is there a way to trap OpenDataSource results and, 2) if not, is there a way to determine column names (say with sp_columns) on a data source opened with OpenDataSource?
Here is a working example of my OpenDataSource call:
set @SQLString = N'SELECT @foo = Due, @boo = Comments
FROM OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + @FileName + '";User ID=Admin;Password=;Extended properties=Excel 8.0'+'''' + ')...importRange'
SET @ParmDefinition = N'@foo varchar(30) OUTPUT, @boo varchar(100) OUTPUT'
which fails when the spreadsheet doesn't have a 'comments' column.