Smartlist Builder – Existing Smartlist Export Error
vaidy.mohan@elcome.com asked 10 years ago
Whenever we modify an existing Smartlist item, we are getting this error message when we try to export resultset of the modified Smartlist. Following is the error message (I am not able to attach the screenshot for some reason):
Excel cannot open the file '______.XLSX' because the file format or file extension is not valid. Verify that the file has not be corrupted and that the file extension matches the format of the file.
This error message happens only for modified existing Smartlist items. All others work perfectly without any issues.
Has anyone faced this and/or is this a bug?
Thanks in advance.
Vaidy
Excel cannot open the file '______.XLSX' because the file format or file extension is not valid. Verify that the file has not be corrupted and that the file extension matches the format of the file.
This error message happens only for modified existing Smartlist items. All others work perfectly without any issues.
Has anyone faced this and/or is this a bug?
Thanks in advance.
Vaidy
Answers
Best Answer
patrick answered 9 years ago
patrick answered 9 years ago
Rochelle,
The error you see is due to a Microsoft issue on the export as it fails (silently) when trying to write the ‘garbage’ characters to the Excel file. It should handle this possibility better but it doesn’t.
That said, the underlying issue is the Dexterity/SLB code isn’t handling the NULLS being returned and you get random garbage data. The more data/columns this can occur in the more likely you’ll see this.
This could happen with customer select statements/views that could enter this condition or it could be due to the TSQL generated by SLB on an outer join.
The “TSQL generated by an outer join” scenario will be resolved in a future service pack and still exists as of 12.0.0070.
patrick
We are seeing the issue if an Excel document is saved using Office 2013 – saved in the latest format. If a user with Office 2010 tries to open the document they are getting the message you are having.
What version of Excel are you using? When you modify a list are you just adding some columns or doing more like restrictions, summary, calculations?
Excel on our terminal server is Excel 2010. Modifications to an existing item do not involve any restriction, summary and/or calculations.
I have added some Extender Window/Form information to an existing Smartlist item. That's all about it.
If you remove your Extender resources from the setup does it export? I was not able to recreate your issue with using a similar setup in SLB with Excel 2010. It's possible that it could be the data being returned too.
Only reason I modified that Smartlist item was to add our Extender resources' information. There is no point in modifying it otherwise. But yes, I also got that sense of invalid data.
I will check this out and update this thread. Meanwhile, if you could lend some ideas on how to resolve it, that would be great.
Vaidy
Our customer is also receiving this error. She is receiving the error when she does the export from smartlist to Excel. The report will export for me, but not her. We are both logged into the same RDS. Any ideas why this would happen? She is on GP2013
This is likely because the SQL query is returning NULLS which doesn't work with Dexterity. If you look through your SmartList results do you see invalid characters when it won't export?I've seen this several times where a custom view was being used and it was resulting in NULLS which in trun was resulting in random invalid characters to show in SmartList. Depending on how many records and fields are set to display increases the pobability of the invalid characters showing up which causes the export to Excel to fail. You can take the SQL query from SmartList Builder (Options-Display SQL) and run it directly in SQL to see if you are getting NULLS. If you do get NULLS this is the cause of SmartList not Exporting to Excel.