Smart List, Excel cannot open file because file format or extension is not valid– Dynamics GP

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:

Find Non-Ascii Characters in One Varchar Column or Mulitiple Columns using SQL Server

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.