How do I import an Excel spreadsheet into SQL Server 2008R2 database?

Question

I have got huge list of contact information in an Excel sheet that I would like to turn into table in the database so that I can maintain them in the database.

I tried following the import/export flat file import from the SQL Server 2008 R2, but it did not work. Can anyone tell me how do I successfully import the spreadsheet into a table in the database?

Thanks

Solution

There is a microsoft knowledge base article that lays out all the ways this is possible.

http://support.microsoft.com/kb/321686

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Manually

Right click on the database name/go to task and then select import data, as a source select an excel file that you created before and choose it's path on the next page select sql server as destination