The ability to export SQL Server data into Excel is very useful. Often time, I need to extract data from SQL Server for users and for most part they want it in Excel format since the data can be manipulated, analyzed and re-formatted very easily.
In Microsoft Excel 2003, each worksheet can only have a maximum of 64,000 rows. However, in Microsoft Excel 2007, we can have a maximum of 1,000,000 rows on each worksheet. Because of this, in some cases, I need to do data export from SQL Server to Microsoft Excel 2007. The problem is that I don’t have Microsoft Excel 2007 installed on my development system. I only have Microsoft Excel 2003. Now the question is that can I use SQL Server data export feature to export data from SQL Server to Excel 2007 without having Microsoft Excel 2007 installed in my system? The answer to that as I found out is yes (but with a few caveats).
On my system, I have SQL Server 2008 Management Studio Express (you can get this free from here), and a blank Microsoft Excel 2007 xlsx file (that I copy from another system that have Microsoft Excel 2007 installed).
Now if I tried to export data from SQL Server to Excel 2007 using SQL Server 2008 Management Studio Express, I would get the following error message:
The operation could not be completed.
Additional information:
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
To resolve this error, I just need to install Microsoft Excel 2007 Viewer. Microsoft Excel 2007 Viewer is free and can be downloaded from here.
Once I installed Microsoft Excel 2007 Viewer, I am able to use the data export feature on Microsoft SQL Server 2008 Management Studio to export data from SQL Server into Microsoft Excel 2007 without any problem.
