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

image

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.

image

image

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.

Comments (1) -

  • Thank you Tim!  This quickly helped me to identify a non-ASCII character which would not export to Excel.  Very easy to follow, much appreciated!
    Eve

Add comment

Loading