May 12, 2009 at 9:02 am
Hi all,
I have SQL Server 2008 SP1 CU1 (Dev edition). When I try to export a table content to Excel 2007 by the Import/Export SSIS Wizard, choosing the Excel 2007 provider as destination and changing the default extension from XLS to XLSX, it seems that it create the file without errors. But when I try to open that file in Excel 2007, I get the error:
"Excel cannot open the file [filename] because the file format or file extension is not valid. Verify that the efile has not been corrupted and that the file exension matches the format of the file"
Does anyone know how to hack it?
Thanks a lot.
May 12, 2009 at 9:11 am
if it is setting the extension by default as XLS then the spreadsheet will be saved under compatibility mode as a 97-2003 spreadsheet
Changing the extension won't change the method that SQL Server is using to save the spreadsheet but it does mean that windows tries to open it as a 2007 spreadsheet when it isn't!
2003 excel documents are stored with a completely different file structure to 2007
May 12, 2009 at 9:13 am
You are right, but I choose the Excel 2007 provider to export it. I change the extension in the wizard, because it generate a file with the xls extension automatically, even if I choos the Excel 2007 provider. Why it doesn't work?
May 12, 2009 at 9:18 am
LucaZAV (5/12/2009)
You are right, but I choose the Excel 2007 provider to export it. I change the extension in the wizard, because it generate a file with the xls extension automatically, even if I choos the Excel 2007 provider. Why it doesn't work?
By this do you mean an OLE DB connection using the Microsoft Office 12.0 Access Database Engine OLE DB Provider?
I was going to suggest that you post this in the SQL Server 2008 Integration services area (this is 2005) but there doesn't seem to be a dedicated area for SSIS in 2008:hehe:
May 12, 2009 at 9:23 am
As you can see, in SQL Server 2008 you can choose the Excel 2007 Provider directly.
May 12, 2009 at 9:26 am
LucaZAV (5/12/2009)
As you can see, in SQL Server 2008 you can choose the Excel 2007 Provider directly.
Well try using an OLE DB connection with the Microsoft Office 12.0 Access Database Engine OLE DB Provider
You may have to create an empty 2007 spreadsheet first
May 12, 2009 at 9:34 am
Ok, I know this solution works. I was trying to export with that new provider...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply