SBX - Search With Button

SBX - Forum Post Title

SmartList Export - Excel cannot open the file...

Microsoft Dynamics GP Forum

Jim Lines asked a question on 11 Jun 2014 11:10 AM
My Badges

Question Status

Verified

Hello,

I've got a user in HR that runs a custom SmartList that was created in SLB which returns all employee information.  When she initially runs the SL and then attempts to export to Excel, she gets the following error message (note: this is at the time SL exports the data, not when saving or opening the file):

"Excel cannot open the file [filename] because the file format or file extension is not valid."

We're on GP 2013 SP2 in a Terminal Server environment running Excel 2007.  In this case, the export is creating a file with a .xlsx extension.  

Of course the user restarted her computer several times (not realizing this is irrelevant b/c of the TS environment), and the export finally worked.  This is only happening on this one SL and no others.  

From what I've found, this error can be related to some Malware, but I do not believe this is the case and no one else has reported issue.

Has anyone else encountered this problem?  If so, did you find a solution?  

Thanks in advance.

Reply
Derek Albaugh responded on 30 Jun 2014 12:08 PM
My Badges

Hello Jim,

We've seen this type of error caused by having special characters in the data being returned by the SmartList and then trying to export it to Excel.

Often, it isn't common special characters such as *, !, #, %, etc., but one example is using Integration Manager and it converts a character to a wing-ding symbol and Excel doesn't recognize it on the export.

Unfortunately, it often takes redundant troubleshooting to find the 'bad' data, such as narrowing down the data to where the issue exists. For example if you are able to return 450 records and then when trying 500 it fails, then you know the issue is between records 450 and 500.

Thanks,

Reply
Leslie Vail responded on 30 Jun 2014 2:29 PM

Hi Jim,

Does the same thing happen with all users logging into the TS? Are you running the TotalFileGuard application on the terminal server? Does the workstation doing the Export have Office 2007 installed?

If you rename the file in the C:\Users\%username%\AppData\Roaming\Microsoft\Excel\XLSTART folder, does it behave the same way?

Just throwing some random ideas out there.

Kind regards,

Leslie

Reply
Jim Lines responded on 7 Jul 2014 8:35 AM
My Badges

Derek,

Thank you for your reply.  The next time it occurs, I'll test the data myself and see what I can find.  I'll follow up with my results.

Jim

Reply
Jim Lines responded on 7 Jul 2014 8:50 AM
My Badges

Leslie,

Thank you (as always) for your reply.  I'll have our support engineers take a look at your suggestions.  I will follow up.

Thanks!

Jim

Reply
weinmagaul responded on 21 Sep 2014 11:49 AM

If it happens completely randomly, does it happen more often to the same group of people? Perhaps an update malfunctioned?
Reinstall Office? Try XLSX Open File Tool for recovery your documents (http://www.xlsx.openfiletool.com/)
There could be so many reasons this could happen, but all the above is where I'd start.

Reply
Harry Lee responded on 21 Sep 2014 12:45 PM
My Badges
Suggested Answer

Jim,

Office 2007 is no longer supported for Dynamics GP 2013 - that doesn't mean it doesn't work.  I seem to remember a recent post very similar to this one.  It turned out the user needed to set the association of an XLXS file and Excel on their machine.  To do this, simply find an XLSX file, open it with Excel, and choose the Always open with this application option.

Reply
Jim Lines responded on 1 Nov 2014 10:11 AM
My Badges
Suggested Answer

I believe I found the resolution to this issue.  The person reporting this error most frequently was running an employee list from the '*' bookmark in SmartList.  My impression is that Excel has an issue with the asterisk during the export process.  I was able to replicate the issue a few times using the * favorite and then I created a new SL called "AAA" with the same parameters (none) and exported.  I did not receive any errors after that.  I'm hoping this is the final resolution.   I'll post back if this was just a fluke.  

Reply
Derek Albaugh responded on 3 Nov 2014 2:35 PM
My Badges

Thank you Jim for the information.

Reply
David Morinello responded on 6 Jan 2015 11:17 AM
My Badges
Suggested Answer

I also ran across this today in GP 2013 R2 and SmartList Builder. The SmartList was run against a SQL view with RM00101, RM00102, and SY01200 (for INET1 email addresses). It seems it was the nulls causing the issue.

 

Randomly when running the SmartList, the Email address column was returning non-printable characters. Run it one time and CUSTNMBR 123 (with no matching record in SY01200, thus NULL) would return something like ñUßàA. The next time it would be empty, but CUSTNMBR 234 (another NULL) would return something similar. It displayed fine in the SmartList, but gave the error when attempting to Export to Excel

I fixed the SQL View, replacing

rtrim(SY01200.INET1) AS INET1 with COALESCE(rtrim(rtrim(SY01200.INET1)), '') AS INET1

and the issue went away.

Reply
Jim Lines responded on 8 Jan 2015 5:17 PM
My Badges

David - thanks for sharing.  Though in my post from 11/1 I thought I had found my issue, but that was not the case.  I still hear from users periodically when this happens.  Next time I run into it I'll dig into the code and see if I can apply your logic.  

Have a great day.

Jim

Reply
Osbert Francis responded on 19 Jan 2015 3:02 PM

I am new here and experiencing the same problem.  MS has advised that it is a "Characer/Data" issue. 

My Export is from GP for an import from a in-house system which takes plave monthly.  I can run the SL report for first half of the month with no issues.  i can run the SL report with data for the balance of the month with no problem.  if I run the SL Report for the entire month the error occurs.  it seems to be some kind of size issue. ANy thoughts on a resolution would be appreciated.  The workaround of the split gets us there just timely.

Reply
Leslie Vail responded on 19 Jan 2015 7:30 PM

Jim,

Could the user ever export to Excel from SmartList while using 2013? I rambled on with my answer, but I don't think 2007 and GP 2013 play nice with one another. I was just at a client site last week and they got the same message. We upgraded to Office 2010 and the report exported right away.

Redbeard has pointed you in the right direction.

Kind regards,

Leslie

Reply
Amanda Anderson responded on 28 Jan 2015 12:03 PM
Suggested Answer

Good morning all,

I just upgrade to GP 2013 R2, we have used Office 2010 for at least the last 3 years.

The first time I generated this specific SmartList, it exported fine. The second time, I received the error.

I tried the same with a different SmartList, no problem on either count.

I assumed it was a size issue so I began narrowing down content from 1,200 lines to 400 line segments. Still got the error.

So I created an .XLSX document and chose open with Excel always. Still fails.

I have checked nulls on the key ids, still fails.

I noticed some odd characters in SmartLists and in the view these were nulls, however, they were not on all the nulls just a few. I can provide screen shots if you need them.

In SQL I opened the view and found NUMEROUS nulls (50 columns…). I changed the fields to isnull. Then re ran the SmartList and there were no symbols.

After this, I ran the full report, 1,200 lines and no problem exporting.

**SUMMARY - Remove ALL Nulls**

I hope this helps,

Amanda

Reply
DHM responded on 27 Apr 2015 11:31 AM

Ran into a similar issue today and, in this case, it's special characters to blame.  Turns out GP writes the data to an intermediate XML document (as text) and then opens the XML doc with Excel.  Excel is balking at the special characters.

Reply
Derek Albaugh responded on 27 Apr 2015 11:41 AM
My Badges

Thanks for the additional information.

Reply
Jim Lines responded on 25 Jan 2017 1:26 PM
My Badges
Verified Answer

I ran into this issue again this week and was actually able to resolve.  Here's a summary of my troubleshooting and resolution.  It probably won't work for everyone, but in my case the corrupt data was in a very small table (SOP10107).

Custom SmartList for Sales/Shipment Tracking Numbers:
Sales Transaction History (SOP30200) and Sales Tracking Numbers Work History (SOP10107) are joined to produce sales transactions with tracking numbers.

Summary:

  • User selected a range of SOP Orders from 1/16/2017 to 1/21/2017 and upon export, the famous “Excel cannot open the file…” error message.
  • I removed the tracking number column and was able to export the data.  (Problem lies in Tracking data)
  • Ran SL with only the tracking number column (less columns, faster results, easier isolation). Still get error.
  • Ran SmartList for each day in the range.  Found an issue on 1/17 and 1/18.  By process of elimination, I was able to isolate two records.
  • Those two records have a 22-character tracking number which is much longer than the normal ones in the dataset.  
  • Thinking the length of the tracking # may be the problem, I updated those records replacing the tracking # with “TEST.”   Still receiving same error. 
  • Created a new SmartList using only the Sales Tracking Numbers Work History table.  Still get export error.  So I’ve narrowed it down to two records in the SOP10107 table causing the issue.  
  • Since the SOP10107 table is very simple (SOP #, SOP Type, Tracking Number), I felt comfortable deleting the records and then inserting them back.  This resolved the problem.  So even though I could not see any signs of data corruption, it appears something in those records was the cause.

Of course, deleting and inserting is not recommended, but in this case it was an easy table and the solution worked.

I hope someone can find this useful.  

Reply
Harry Lee responded on 21 Sep 2014 12:45 PM
My Badges
Suggested Answer

Jim,

Office 2007 is no longer supported for Dynamics GP 2013 - that doesn't mean it doesn't work.  I seem to remember a recent post very similar to this one.  It turned out the user needed to set the association of an XLXS file and Excel on their machine.  To do this, simply find an XLSX file, open it with Excel, and choose the Always open with this application option.

Reply
Jim Lines responded on 1 Nov 2014 10:11 AM
My Badges
Suggested Answer

I believe I found the resolution to this issue.  The person reporting this error most frequently was running an employee list from the '*' bookmark in SmartList.  My impression is that Excel has an issue with the asterisk during the export process.  I was able to replicate the issue a few times using the * favorite and then I created a new SL called "AAA" with the same parameters (none) and exported.  I did not receive any errors after that.  I'm hoping this is the final resolution.   I'll post back if this was just a fluke.  

Reply
David Morinello responded on 6 Jan 2015 11:17 AM
My Badges
Suggested Answer

I also ran across this today in GP 2013 R2 and SmartList Builder. The SmartList was run against a SQL view with RM00101, RM00102, and SY01200 (for INET1 email addresses). It seems it was the nulls causing the issue.

 

Randomly when running the SmartList, the Email address column was returning non-printable characters. Run it one time and CUSTNMBR 123 (with no matching record in SY01200, thus NULL) would return something like ñUßàA. The next time it would be empty, but CUSTNMBR 234 (another NULL) would return something similar. It displayed fine in the SmartList, but gave the error when attempting to Export to Excel

I fixed the SQL View, replacing

rtrim(SY01200.INET1) AS INET1 with COALESCE(rtrim(rtrim(SY01200.INET1)), '') AS INET1

and the issue went away.

Reply
Amanda Anderson responded on 28 Jan 2015 12:03 PM
Suggested Answer

Good morning all,

I just upgrade to GP 2013 R2, we have used Office 2010 for at least the last 3 years.

The first time I generated this specific SmartList, it exported fine. The second time, I received the error.

I tried the same with a different SmartList, no problem on either count.

I assumed it was a size issue so I began narrowing down content from 1,200 lines to 400 line segments. Still got the error.

So I created an .XLSX document and chose open with Excel always. Still fails.

I have checked nulls on the key ids, still fails.

I noticed some odd characters in SmartLists and in the view these were nulls, however, they were not on all the nulls just a few. I can provide screen shots if you need them.

In SQL I opened the view and found NUMEROUS nulls (50 columns…). I changed the fields to isnull. Then re ran the SmartList and there were no symbols.

After this, I ran the full report, 1,200 lines and no problem exporting.

**SUMMARY - Remove ALL Nulls**

I hope this helps,

Amanda

Reply

SBX - Two Col Forum

SBX - Migrated JS