SmartList Export: Excel cannot open error

Today’s #TipTuesday is one a client ran into a little while ago, and is worth sharing how you may find out what is causing the issue. The TL;DR version is a strange character somehow was input into an invoice comment and it caused an issue when that record (with that field) was exported to Excel from a Dynamics GP SmartList. Exporting to Word instead of Excel showed where the character was to be able to edit the data to remove it.

The Error

The full text of the error message is “Excel cannot open the file ‘XXXXX.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.”. A screenshot of the error is below.

Investigating to find the issue

Skip this section if you don’t care about the thought process I used. I find sometimes “how” someone attacks a problem is more important that the fix itself, so I’m going to explain in more detail than I might ordinarily for a post of this type.

Visually, the data didn’t look odd at all. It’s an export issue, so the first thing I looked at was the data on screen in SmartList. The user was attempting to export (in this particular case) a list of outstanding receivables. Most of the fields that were in the SmartList were “normal” fields - Document No., Date, Customer ID, Customer Name, Original Amount, Current Balance etc. and in that data, nothing visually appeared to be off, no special characters that I could see, nothing that might explain this.

This SmartList was custom built using SmartList Builder, and it also contained the full invoice text from Key2Act Job Cost. Issues with SmartList exports often tie back to a custom-built SmartList and a datatype issue (in my experience). In this case, I spot checked the comment field which happens to be quite lengthy and there were non-alphabetic characters all over the place, like brackets and quotation marks etc., but nothing else jumped out.

I realized this is a monthly task (to export receivables for some reporting) therefore I ruled out any repeated characters that were in prior months’ outstanding invoice comments because no one alerted me to an issue last month. That led me to narrow down the field of potential candidates to just invoices issued this month - which cut the list to about 40.

Now it was a bit of trial and error to find the document(s) that had the issue. First, I was still assuming it was a comment field issue. I removed that field, and the SmartList exported just fine, so that validated I was looking in the right spot. I would recommend the same, remove some columns and add back one at a time until you narrow down where to look for the issue. It helps to know which column contains the so-called bad data.

After that, I simply went in stages restricting the list of what to export, 50% at a time until I found the document that had the error. What I mean by that is I had 40 invoices, so I added a search to filter on date to cut that approximately by half. First half still had the error. Filter again to cut further, and repeat. I got down to a list of 5 or so and at that point just filtered to each doc # by itself to find the problem child.

Now: I knew which document failed to export and I knew which field the bad data was in. Unfortunately, in SmartList the data looked completely normal to the naked eye. There did appear to be a double space between 2 characters but that’s not exactly unusual. I then ran a query in SQL on the Job Cost billing table because that field is pulling from that table. In SQL it also visually seemed fine. Huh.

Workaround #1 to find the issue

The first thing I tried was changing my dex.ini setting to add in the line “SmartlistEnhancedExcelExport=TRUE”. This overrides formatting and things like that during export routines and I was curious if the SmartList would still fail to export. It did not fail, this time it worked, but even still, I had no idea what the bad data was that caused the problem.

Unfortunately, this is a workstation change that wouldn’t work for the user who needs it resolved. The user exports a number of SmartLists and because some data contains leading zeros, using this dex.ini switch would cause more issues in other areas (as it strips leading zeros and treats the field as numeric). Now, if this wasn’t a monthly task, I would have just given the user the exported list and we’d be fine. As long as this invoice stays outstanding, this SmartList won’t export so a solution was needed.

Side rant: please don’t set up IDs in your system with leading zeros. When you want to use the dex.ini switch above, a numeric ID with leading zeros, even though the field in Dynamics GP is a char field, Excel will think it’s a number and strip the leading zeros. Add at least an alpha prefix or something to ensure the data exports seamlessly every time.

Workaround #2 to find the issue

I changed my dex.ini setting back to SmartlistEnhancedExcelExport=FALSE and then tried exporting to Microsoft Word. I rarely export using the Word option in SmartList because it’s just not useful for anything I’ve run across. However, in this case, it’s the miracle alternative because not only does the data export, it shows you the offending special character so you can correct it.

Here is what the export looked like (some data blurred out to protect the client’s data, some left in to see the context around the character). Right in front of the word starting with “Brook” is a box with a question mark in it. I have no idea what keystroke the user did in GP to have this occur, but it’s there nonetheless.

Using Export to Word from SmartList, the “bad data” is more clearly visible

Resolution

The ultimate resolution was editing the data to remove that character. Unfortunately it was not a “search and replace” scenario as you cannot paste that into SQL to do that. Option 1 - depending on where the data is - is to edit the data in Dynamics GP if you can. Option 2 - what I had to do here - is edit in SQL.

I’m *very* hesitant to edit a posted transaction via SQL generally speaking but this issue would continue month after month until the invoice is paid. In that scenario, I believe it’s “acceptable” to edit it in SQL. I copied the text exactly, edited the bad section out and updated that record with a slightly edited version of the comment. After that, the SmartList export worked again.

That’s it for this post. I hope it helps someone out there!