How can import xlsx file to Sql server 2000

Question

Recently i moved to office 2007. im trying to import excel file to sql server 2000 using import and export data tool ,the tool gave data source as excel 97 - 2000 but while importing xlsx file ,im getting error "Error source Microsoft Jet databse Engine", external table is not in the expected format. Can you tell me how can i import office excel file xlsx to sql server 2000 using import and export data tool.

Appreciate your prompt comments

Answer 1

You could always just save it to a 97-2000 format XLS file

Answer 2

I agree with "schooner", the easiest way to import Excel 2007 into SQL 2K is to save the excel document into a 97-2003 format.

Answer 3

Saving the document into 97-2003 format is fine when you have a spreadsheet that is smaller than 65536 rows, if it's not then using GordonB's link works

Answer 4

You could use an OLEDB connection, assuming DTS supports it. It's been a while since I've used it, so I can't remember. Connection looks something like this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

If your DTS server doesn't have Office installed you'll need to install the drivers: http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en