Extracting MS Excel Data in MSSQL Server 2005 Express

Is there a way of extracting data from MS Excel using MS SQL Server 2005 Express?

hi,

you can set up a linked server (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) to the xls file and get relevant data into your SQLExpress table...
consider having a d:\Students.xls file with 2 columns, FirstName and LastName ...

SET NOCOUNT ON;

CREATE TABLE dbo.Students (

FirstName VARCHAR(10) ,

LastName VARCHAR(10)

);

GO

EXEC sp_addlinkedserver 'ExcelSource',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'd:\Students.xls',

NULL,

'Excel 5.0';

GO

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL ;

GO

SELECT * FROM ExcelSource...[Sheet1$];

-- import into your base table...

INSERT INTO dbo.Students SELECT * FROM ExcelSource...[Sheet1$]; /* [Sheet1$] is for English version :D as it is localized in the Excel language localization */

SELECT * FROM dbo.Students;

GO

-- final clean up, dropping the linked server and the base table

EXEC sp_dropserver 'ExcelSource', 'droplogins';

GO

DROP TABLE dbo.Students;

regards