Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Import wizard to import Microsoft Excel 2007 file to SQL Server 2005

Currently I have been using Microsoft Excel 2003 at my work and I am importing the data from Excel file to SQL Server 2005 database by using the import wizard where I choose Excel as the data source. However, I am planning to upgrade the Excel to 2007 version. How would I import Excel 2007 into SQL Server? Can someone walk me through.

Secondly, would all my SSIS break (in case the Excel files are upgraded to 2007, i.e .doc to .docx) where I am using the script task and VB.NET to access the Excel with a code something like this:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')

Thanks.
0
skaleem1
Asked:
skaleem1
  • 3
  • 3
1 Solution
 
joshbulaCommented:
You might be able to just change "Excel 8.0" to "Excel 12.0"

You might also need to switch from using Jet to ACE... I don't belive Jet supports Excel 2007.  
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en


http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/e94d7f78-82d0-411c-b538-009fe188544b

0
 
skaleem1Author Commented:
In SQL Sever 2005 Import wizard, the only option is for Excel 2003 or older versions.
0
 
lcohanDatabase AnalystCommented:
Did you tried to inport the EXCEL into SQL by scrip instead?

Is pretty easy to do: Q306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries

http://support.microsoft.com/kb/306397


0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
lcohanDatabase AnalystCommented:
For XLSX you should be able to use the process described at:
http://bensullins.com/using-excel-2007-files-as-a-source-in-ssis-2005/

worked for me...
0
 
skaleem1Author Commented:
Icohan,

I appreciate your help and the links are helpful however I want to know how to import data from Excel  2007 file to SQL Server 2005 database by using the import wizard manually. Can you shed light please.

Thanks
0
 
lcohanDatabase AnalystCommented:
Well good luck with that....just because it is not quite possible by using default SQL 2005 installs I gave an option. You must upgrade to SQL 2008 if you really need to do that via import task or SSIS.

http://dineshasanka.spaces.live.com/Blog/cns!22A79FCE82651673!588.entry
0
 
skaleem1Author Commented:
Thanks, I have a workaround and I am saving the spreadsheet in the compatibility mode with the previous version (97-2003) until I change all my queries to use the Excel 12 driver.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now