What should be a simple engagement turned into a headache this evening. Importing from Excel in Sql 2005 has become more cumbersome due to various 'issues' with the import/export wizard. So here is a good solution:
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
May have to enable Ad Hoc Remote queries in SAC.
May have to place the Excel file on the SQL server's file system.
Reference: http://support.microsoft.com/kb/321686/en-us