mssqltips logo

Import Excel data with SQL Server Integration Services

By:   |   Updated: 2010-05-11   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Microsoft Excel Integration

Problem

Our company recently pushed out Office 2007 to all workstations. I'm trying to import an Excel 2007 spreadsheet into SQL Server using the Import/Export Wizard but just can't make it work. I don't even see the driver for Excel. Do you all have any advice? Check out this tip for the solution.

Solution

As you've likely noticed some things have changed with the Import/Export Wizard, and even more so with the available drivers. In previous versions of Excel, a driver specific to Excel was available. With Excel 2007 you use the Microsoft Access 12.0 Database Engine OLE DB Provider:

 import an Excel 2007 spreadsheet into SQL Server

One thing you will notice is that the driver is not available with the 64-bit Import/Export Wizard. To access the driver on a 64-bit system choose "Import and Export Data (32-bit), located in the SQL Server 2008 folder:

Import and Export Data (32-bit), located in the SQL Server 2008 folder

Using the Microsoft Access 12.0 Database Engine OLE DB Provider in Import/Export Wizard

To import Excel 2007 spreadsheets into SQL Server perform the following steps:

Start the 32-bit Import and Export Data (32-bit) program, choose the Microsoft Access 12.0 Database Engine OLE DB Provider, and click Properties:

Selecting the Microsoft Access 12.0 Database Engine OLE DB Provider driver

Select the All tab and double-click Data Source:

Select the All tab and double-click Data Source

Enter the file location and name and click OK:

Enter the file location and name and click OK

Double-click Extended Properties, enter Excel 12.0, and click OK:

click Extended Properties, enter Excel 12.0

Click OK twice and proceed through the wizard. Once on the screen used to map the source and destination you have the option to Preview the data:

 Once on the screen used to map the source and destination you have the option to Preview the data

Using the Microsoft Access 12.0 Database Engine OLE DB Provider in SSIS When setting up an OLE DB connection for an SSIS package right the Connection Manager, choose New OLE DB Connection, then choose Microsoft Access 12.0 Database Engine OLE DB Provider:

Using the Microsoft Access 12.0 Database Engine OLE DB Provider in SSIS

Enter Excel 12.0 in the Extended Properties section and the file location and name in the Data Source section:

Enter Excel 12.0 in the Extended Properties section

Once complete you can preview the data by adding a Data Flow Task, adding the OLE DB Connection to the OLE DB source.

There are a few things to consider when using this driver. First, the driver will work for both Excel 2003 and 2007 spreadsheets so if you are setting up an SSIS package to import data you don't need to switch drivers for each file type. Second, if you plan on having the SQL Server Agent execute SSIS package then you will likely need to install the Office 2007 System Driver: Data Connectivity components.

Next Steps


Last Updated: 2010-05-11


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Saturday, October 09, 2010 - 1:14:58 PM - Tim Cullen Back To Top

I want to add additional information to this tip, in particular another use for the Microsoft Office 12.0 Access Database Engine OLE DB Provider.  I was attempting to import a DBF file by using the Microsoft Jet 4.0 Engine in an SSIS package and received the following error:

Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available

After some trial and error I set up an OLE DB connection using  the Microsoft Office 12.0 Access Database Engine OLE DB Provider.  I entered "dBase IV" in the Extended Properties section and specified the folder location as the Data Source.  This setup allowed me to import the DBF file.  Keep in mind that you have to specify the particular version of dBase that the file was created with.




Thursday, May 13, 2010 - 10:17:10 AM - timothyrcullen Back To Top

Very interesting...I can't find the answer.  The other interesting part is that on my workstation (32-bit) at work there is no driver for Excel 2007 in the Import/Export Wizard, but on my workstation at home (64-bit) when I open the 32-bit Import/Export Wizard there IS a driver for Excel 2007.  Very squirrelly, indeed.  I'll have to continue my spelunking!


Wednesday, May 12, 2010 - 8:51:11 AM - timothyrcullen Back To Top
Good question-I'll work on the answer today!

Tuesday, May 11, 2010 - 8:39:29 AM - JohnLia Back To Top

Thanks for the tip, but is there a property value somewhere to keep from having the first row be interpreted as headers?

John



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools