RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

How to Import Data from Microsoft Office Excel 2007 to SQL Server 2005

By : Ashish Kumar Mehta
Jun 24, 2008

SSIS Developers often come across a scenario where they need to import data from a Microsoft Office Excel 2007 Spreadsheet. This article provides a step by step guide on how to import data from a Microsoft Office Excel 2007 Spreadsheet to a SQL Server 2005 database.

The first step is to go to Start and select Run and type CMD and then type DTSWIZARD in the command prompt. The Welcome to SQL Server Import and Export Wizard screen will pop up on your screen as shown in the image below:

Once you click the Next button it will take you to the Choose a Data Source wizard screen. One needs to select the Data Source as Microsoft Office 12.0 Access Database Engine OLE DB Provider and then click the Properties… button on the wizard screen which will pop up the Data Link Properties screen. Under the All tab, double click the Data Source property valve and enter the location of the Spreadsheet eg. “C:\Excel2007\Import\SampleData.xlsx” as the name and path of the Microsoft Office Excel 2007 file from where the data needs to be imported from. Then double click on Extended Properties and select Excel 12.0 as the property value.

The connection to the Microsoft Office Excel 2007 can be tested by clicking on the Test Connection button as shown in the image below:

Next Page>>