Import Excel 2007 into SQL Server 2005

Did you try DTS?

Another suggestion is to use WinSQL (http://synametrics.com/winsql).

You will have to create an ODBC data source for excel, write a SELECT
query against your Excel spread sheet and export data to SQL Server.



raghu kurra wrote:
> Hi Pooja,
>
> I need to import a xlsx to DB 2005 so i thought by now you would have figured it out how to do it so thought of mailing yu as it would save me a lot of time...if you have any solution to process it please do reply...
>
> Thanks
> Raghu
>
>
>
> Pooja Prasad wrote:
>
> Error when importing excel 2007 to SQL 2005 using Access 12.0
> 26-Nov-08
>
> 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
>
> Previous Posts In This Thread:
>
> On Monday, July 14, 2008 5:35 PM
> tom wrote:
>
> Import Excel 2007 into SQL Server 2005
> 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
>
> On Tuesday, July 15, 2008 4:57 AM
> changli wrote:
>
> RE: Import Excel 2007 into SQL Server 2005
> 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.co...rt_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: msdnmgatmicrosoftdotcom.
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
> On Tuesday, July 15, 2008 11:46 PM
> Tom Tucker wrote:
>
> Re: Import Excel 2007 into SQL Server 2005
> 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.....
>
> ""Charles Wang [MSFT]"" wrote in message
> news:bfrOgjl5IHA.3320atTK2MSFTNGHUB02dotphx.gbl...
>
> On Wednesday, July 16, 2008 10:20 PM
> changli wrote:
>
> Re: Import Excel 2007 into SQL Server 2005
> 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: msdnmgatmicrosoftdotcom.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
> On Wednesday, July 16, 2008 10:20 PM
> changli wrote:
>
> Re: Import Excel 2007 into SQL Server 2005
> 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: msdnmgatmicrosoftdotcom.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
> On Thursday, July 17, 2008 3:10 PM
> tom wrote:
>
> Re: Import Excel 2007 into SQL Server 2005
> Thanks Charles, I appreciate your help.
>
> ""Charles Wang [MSFT]"" wrote:
>
> On Wednesday, November 26, 2008 8:20 AM
> Pooja Prasad wrote:
>
> Error when importing excel 2007 to SQL 2005 using Access 12.0
> 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
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> The ASP.NET Codebehind vs. Inline Code Debate
> http://www.eggheadcafe.com/tutorials...behind-vs.aspx