Exporting from Dynamics GP Smart List to Excel can cause the following error on GP2013R2 upwards.
Excel cannot open the file “xx.xlsx” because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file
The community post SmartList Export - Excel cannot open the file... has some ideas for Smart List Builder and what may cause the issue however we saw this issue today in normal smart lists.
By prematurely stopping the export at different time intervals (press stop), it is possible to find the threshold record where the export fails. Limiting the number of columns in the smart list also narrows down to the field involved. In this example it was some extended characters that had found their way from an ecommerce solution through econnect into the customer name field. It is also important to bear in mind that wrong assumptions about the tables and fields in the smart lists can be made. For example the customer name may originate from one entered in an order, not the debtor maintenance record. Try to establish the source of the data in the smart list.
Once the offending character was identified, in the GP user interface, that character was edited out of the name that resulted in the export to excel working correctly again.
The following Stack Overflow question helped by providing some TSQL function that allows such characters to be found:
Following function looks for characters that are “non-ASCII”:
create function [dbo].[udf_ContainsNonASCIIChars] ( @string nvarchar(4000), @checkExtendedCharset bit ) returns bit as begin declare @pos int = 0; declare @char varchar(1); declare @return bit = 0; while @pos < len(@string) begin select @char = substring(@string, @pos, 1) if ascii(@char) < 32 or ascii(@char) > 126 begin if @checkExtendedCharset = 1 begin if ascii(@char) not in (9,124,130,138,142,146,150,154,158,160,170,176,180,181,183,184,185,186,192,193,194,195,196,197,199,200,201,202,203,204,205,206,207,209,210,211,212,213,214,216,217,218,219,220,221,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,248,249,250,251,252,253,254,255) begin select @return = 1; select @pos = (len(@string) + 1) end else begin select @pos = @pos + 1 end end else begin select @return = 1; select @pos = (len(@string) + 1) end end else begin select @pos = @pos + 1 end end return @return; end
The above function can be called against GP table fields like this, do so for any suspect fields and tables:
-- Find Non-ASCII in Debtor CUSTNAME select CUSTNAME from RM00101 where dbo.udf_ContainsNonASCIIChars(CUSTNAME, 1) = 1 -- Find Non-ASCII in Purchase order work, Vendorname select * from POP30300 where dbo.udf_ContainsNonASCIIChars(VENDNAME, 1) = 1 -- Find Non-ASCII in Vendors, Vendorname select * from PM00200 where dbo.udf_ContainsNonASCIIChars(VENDNAME, 1) = 1
Note that many characters such as “™ ® ” don’t seem to cause issues, the character that caused the problem in this case was a “T” like character finding its way in substitute for the “&” character. Investigating we find this is character code 22. This is useful as attempting to copy and paste this character causes issues in editors.
It can be seen that now there is a character code number to work with, a SQL replace is now possible, for occurrences of this character, in these fields. As always backup your data and don’t play if you don’t know what you are doing, instead contact your support partner.
SELECT REPLACE(VENDNAME, char(22), '&') from pm00200 where vendorid='DELO'
It is also a good idea to check your integration and integrating applications, to find the source of the corruption.
I would like some further investigation time to see what other characters can cause this error, I guess it is all the low value codes (below 32). It would be possible make a SQL reporting job to notify users of data issues if they occur again in the future.