I was going through a mountain of pain trying to get an import working from an Excel spreadsheet working into SQL Server 2005 but I finally managed it and here’s how…
Here’s the SQL I’m using (slightly abridged!) :
set transaction isolation level read committed
declare @sql nvarchar(1024)
set @sql = 'select * into ##Temp from opendatasource(''Microsoft.Jet.OLEDB.4.0'', ''Extended Properties=Excel 8.0;Data Source=' + @ExcelFileToImport + ''')...' + @ExcelWorkSheet
exec sp_executesql @sql
Points to note :
I was having trouble executing within a transaction but with the changes made above the sql could be called with the following :
using (TransactionScope scope = new TransactionScope())
{
UploadExcelIntoDatabase(importTicket, importFilename);
scope.Complete();
}
Selecting data from a stored procedure in SQL Server is already a documented feature and here’s an example:
insert into #systables exec sp_executesql N'select * from Northwind.sys.tables'
…problem is this example doesn’t run without first creating the temp table and therefore knowing all the column definitions. When I’m running quick queries this isn’t exactly convenient. I’ve seen blogs posts using a linked server but there’s another way :
select * into #systables
from openrowset(
'sqlncli',
'server=.;trusted_connection=Yes',
'sp_executesql N''select * from Northwind.sys.tables'''
)
I wouldn’t necessarily use this as a day-to-day process on a production environment but for administration or scripting installations I think it fits the bill.
I keep forgetting how simple this little trick is so it’s time to blog it.
I wanted to write some T-SQL that would confirm that the columns just created by an import from an Excel spreadsheet into a temporary table contained the expected columns irrespective of order or other columns that exist in the file. I also wanted to be able to inform the user of the problem instead of just fail with a horrible error. I chose a fairly simple method, I would concatenate the columns from the temporary table (#tempImport in this case) and compare that with an expected string. If they don’t match I can report back to the user that the expected columns do not exist (in this implementation I’m not interested reporting which columns are missing.) The implementation I chose was this :
select * into #tempImport
from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=e:\temp\aSpreadsheet.xls', Sheet1$)
declare @columns varchar(512)
set @columns = ''
select @columns = @columns + ',' + [name]
from tempdb.sys.columns
where [object_id] = object_id('tempdb..#tempImport')
and [name] in ('Store','Store Name','PSA1','PSA2','Mtrg')
order by [name]
if (@columns <> ',Mtrg,PSA1,PSA2,Store,Store Name')
print 'The expected columns in the spreadsheet do not exist'
This is a brute-force type of implementation but I like it because it’s very simple and keeps everything in one place. There will be better ways to implement this but for the moment this fits the bill.
Update:
http://support.microsoft.com/kb/326548 is a useful guide to using the ISAM provider.
