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

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:Excel2007ImportSampleData.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:


Pages: 1 2


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |