Importing data to SQL Server from Excel file

After a very long gap of a month and half, I had some space for me to write some technical blog.

This is all about exporting the Excel sheet to SQL Data source. You will need this in many instances to manipulate datas present in the sheet. These were the steps to be followed.

Step 1:

You have to check some basic configurations in your Surface Area Configuration of the SQL Server.

Please refer the following link to do these configurations:
http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Step 2:

Install Microsoft.ACE.OLEDB.12.0 component from this following link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Step 3:

The DB script for importing datas from .xlsx(Excel) file to SQL Server 2005 is mentioned below.

Simple Importing:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TestExcel.xlsx', 'SELECT * FROM [Sheet1$]');

Importing Data to Table:

SELECT * INTO [dbo].[ExcelTempTable] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TestExcel.xlsx', 'SELECT * FROM [Sheet1$]');

Note: This doesn't mean that the [ExcelTempTable] should be predefined. Its a table created dynamic with the columns from Excel sheet.

This would suffice for exporting data from .xlsx file to SQL Server. Happy coding.