How to Import an Excel Spreadsheet into SQL Server 2008R2 Database

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

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

Import Excel to SQL Server 2008

If you're limited solely to TSQL, the above two answers will show you some ideas. If you have access to either Data Tools or Business Intelligence, with SSIS, you can automate it with the assumption that each sheet in the Excel workbook matches each time. With SSIS, you'll use a Data Flow task and each sheet will be imported into the table that you want. When you're ready for the file the next week, you'll drop it into the folder and run the SSIS package.

However, if the sheet names change, (for instance, one week sheets are called Cats, Dogs, Rain and the next week it's Sulfur, Fire, Hell) then this would cause the package to break. Otherwise, if only the data within the worksheet change, then this can be completely automated with SSIS.

Example article: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/

how to import an excel file into sqlserver 2008

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

Import multiple Excel files into SQL Server 2008 R2 using SSIS Packages?

since you have only 2 excel files and only two tables I don't see why use a foreach loop. Just follow this link and do it directly

If you need to insert from both excel files to both databases you can use a multicast component to create a copy of a dataset

---

Related Topics