Exporting to Excel with bcp
I was recently looking for a quick way to export SQL Server table data (over 1,00,000 records) to a Excel file.
I found that the bcp command line tool can export SQL Server table data to a .xls file but when I export it to a Excel 2007 (.xlsx) file, I couldn't open the file. It throws a warning - "Excel cannot open the file because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".
The fix to this issue reported in ASP.NET Forums which points to a MS KB article, is complicated.
I wondered, if bcp can export to a .xls file why can't it smoothly export to a .xlsx file?
Curiously, I was able to able to export over 7 lakh records to a .xls file (which is supposed to have a row limit of ~65K rows in 22seconds) with bcp and the .xls file opens fine in Excel 2007 displaying all the 7 lakh records.
Vinod helped me understand how the process actually works -
I found that the bcp command line tool can export SQL Server table data to a .xls file but when I export it to a Excel 2007 (.xlsx) file, I couldn't open the file. It throws a warning - "Excel cannot open the file because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".
The fix to this issue reported in ASP.NET Forums which points to a MS KB article, is complicated.
I wondered, if bcp can export to a .xls file why can't it smoothly export to a .xlsx file?
Curiously, I was able to able to export over 7 lakh records to a .xls file (which is supposed to have a row limit of ~65K rows in 22seconds) with bcp and the .xls file opens fine in Excel 2007 displaying all the 7 lakh records.
Vinod helped me understand how the process actually works -
Fundamentally, you need to understand that the BCP command doesn’t actually write as a real XLS file. Try to open it in Notepad and it would be a mere csv format. It is just that Excel seamlessly opens it without any problem. Now with .XLSX extension Excel 2007 expects the same to be in the XML format (which it isn’t actually) and hence the error.
BTW, the format of XLS or XLSX doesn’t determine the number of rows. It is the version of Office (2003/2007) you are running.
Really great information. Helped me very much. Thank you!!
ReplyDelete