Having some trouble importing data from an Excel 2007 spreadsheet into SQL Server 2005 database - SQL 2005 db is at SP2 - using Import and Export Wizard in SSMS. As the Import and Export wizard only shows Excel 97-2005 under Excel Versions on Choose A Data Source, I eventually found the following guide on how to import an Excel 2007 spreadsheet into SQL 2005 :
So have followed the steps in that guide, involving :
- specifying Microsoft Office 12.0 Access Database Engine OLE DB Provider as Data Source;
- in Data Link Properties, supplying the full path of Excel 2007 spreadsheet in Data Source;
- in Data Link Properties, supplying Excel 12.0 against Extended Properties;
Test Connection on Data Link Properties comes back 'Test connection succeeded'
Specified required SQL Server credentials on 'Choose a Destination' in SQL Server Import and Export Wizard, specifying SQL Native Client as destination.
Specificed 'copy data from one or more tables' on Specify Table Copy or Query;
On Select Source Tables and Views specify Sheet1$;
Preview shows data from spreadsheet OK;
But the final step of the wizard gives an error :
Opening a rowset for Sheet1$ failed - check that the object exists in the database
On the 'How To Guide' link referred to earlier, the Comments section shows that a couple of other people have hit exactly the same problem, but no solution has been supplied.
So can anyone here help me resolve this, please ?