Import Excel 2007 into SQL Server 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TomT - 14 Jul 2008 22:35 GMT

Are there any updates or add-ins to provide the xlsx file type when using the
SQL Server Import and Export Wizard (from SSMS)? The highest version is
97-2005. I realize I can do a save as in Excel to get an earlier file
format....

Thanks for any suggestions

Reply to this Message

Charles Wang [MSFT] - 15 Jul 2008 09:57 GMT

Hi Tomt,
Yes, you can choose "Microsoft Office 12.0 Access Database Engine OLE DB
Provider" as the data source in SQL Server Import and Export Wizard, then
click Properties, switch to the All tab, input your excel file path to the
Data source field and input "Excel 12.0" to the Extended Properties field
and then click OK to follow the wizard.

For more information, you may refer to:
How to Import Data from Microsoft Office Excel 2007 to SQL Server 2005
http://www.sql-server-performance.com/articles/biz/How_to_Import_Data_From_E
xcel_2007_p1.aspx

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

Tom Tucker - 16 Jul 2008 04:46 GMT

Thanks Charles, that works. Is there any way to get Excel 2007 into the
initial list of data sources - it would save on a bunch of steps.....

> Hi Tomt,
> Yes, you can choose "Microsoft Office 12.0 Access Database Engine OLE DB
[quoted text clipped - 41 lines]
> rights.
> =========================================================

Reply to this Message

Charles Wang [MSFT] - 17 Jul 2008 03:20 GMT

Hi Tom,
Thank you for your response. I am very glad to hear that it helps.
Currently there is no direct way to get Excel 2007 into the initial list,
you may give Microsoft your feedback at https://connect.microsoft.com/sql .
Actually the current wizard is also simple and does not have many steps. If
you compare it with the prior version Excel data source, you can find that
the steps are actually not increased, just some UI differences. Anyway if
it is a direct Excel data source, the UI will be more intuitive.

If you have other questions or concerns, please feel free to let me know.
Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

TomT - 17 Jul 2008 20:10 GMT

Thanks Charles, I appreciate your help.

> Hi Tom,
> Thank you for your response. I am very glad to hear that it helps.
[quoted text clipped - 20 lines]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================

Reply to this Message

Charles Wang [MSFT] - 17 Jul 2008 03:20 GMT

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

Pooja Prasad - 26 Nov 2008 13:20 GMT

Hi
I am using SQL Server 2005 and trying to import data from an Excel 2007 file into my SQL Server database. Previously, when importing from Excel 2003 it was very simple (just specify Microsoft Excel as the Data Source and Excel Version of "Microsoft Excel 97-2005". Since there is not an option for version 2007, I have tried to import from Excel 2007 by specifying the following:

Data Source: Microsoft Office 12.0 Access Database Engine OLE DB Provider
clicking on Properties and entering the file path into the "Data Source" textbox on the "Connection" tab for Data Link Properties and then typing in a value of "Excel 12.0" for the Extended Properties on the "All" tab.

The test connection succeeds and I continue in the wizard and specify the database destination etc. I can even view the data using "Preview" but when I run the package, I get the following error:

Opening a rowset for "`'March Files$'`" failed. Check that the object exists in the database.

"March Files" is the name of the worksheet in the Excel file I am trying to import data from.

I need to know what I must do to successfully import data from an Excel 2007 file into SQL Server 2005 using the SQL Server Import and Export Wizard either by fixing the above error or finding some sort of plug in or work around to easily import. I am assuming that Microsoft hasn't updated the Wizard to more easily import from Excel 2007 files?

Thanks in advance
Pooja

Reply to this Message