Export to Excel 2013 is painfully slow

Hi,

Has anyone else run into this issue?   I'm trying to export a full receivables aging to Excel to do some analysis on it.  This is a very large file, with nearly 20,000 invoices, but...

Exporting to text takes around 1 second.   Exporting to Excel on an old Pentium IV workstation without Excel takes about 15 seconds.

This is the progress on Windows 8 / Office 2013, after 45 minutes:

Progress bars are notoriously difficult to program, so it's hard to be sure, but this looks like it has another 3 hours to go, so...

An export takes roughly 900 times as long to Office 2013, versus exporting in Excel 2.1 format.

Is there a setting in Excel, or in Sage 50, or in Windows, that anyone has come across, that will improve the speed?  

Thanks,

Randy

  • 0

    I don't use that configuration but I assume you are using the Open in Excel button?  Have you tried the Export option to Excel instead?  I don't think that requires Excel to open first.

  • 0 in reply to Richard S. Ridings

    Thanks Richard,

    I was using the Export to Excel option.

    I would think that it shouldn't be bothering with Excel during the Export process.  When using Export to Excel (97-2007 format) or Open In Excel, Sage 50 appears to use Microsoft Excel to build the Excel file.

    It seems to take the same amount of time, right up to either opening it for the 'Open In Excel' option, or not opening the output file for an Export.

    The stopwatch shows 2:20:55 now, and the progress bar's past the halfway mark.  Because it's using a third of a 4 core processor, it's used 2:52:09 of processor time. 

    (update, it took 3:26:04.27 to export 32,043 rows x 9 columns, so about 2.5 rows per second.)

    (Edit) this problem can be solved by turning off all animation in Excel.

  • 0

    Hi Randy,

    May I ask if the office is a local 32 bit 2013 version or the Office 365 Cloud version?

    Thanks

  • 0 in reply to Keith L

    Hi Keith,

    It shows in control Panel as Office 365 ProPlus - en-us,  15.0.4615.1002.  

    Up until just now, I was fairly sure it was locally installed, and just updated itself when necessary, (i.e. that the '365'-ness of it was that it was a deployment method of delivering the installation.)

    Update - did more testing, export takes less than 5 seconds if I choose Excel 2.1, instead of Excel 97-2007 format, and about the same time to export in CSV as Text, a bit under 3 seconds.  When using the Sage 'baked-in' export code it's very quick.

    Tested with Windows 7 64 bit, Excel in Office 2013:

    ~30 minutes to export a 30,000 line report from Sage 2013.2

    ~3 hours to export a 30,000 line report from Sage 2014.2

    I think this should be looked at by Sage development, 3 hours to export a report into Excel is an indication of a problem. 

  • 0 in reply to RandyW

    Hi Randy,

    I personally test it with a file which has 710 rows and 9 columns.  I apologize that I cannot come up with a report with as many rows as you do.  

    For windows 7-32 bits with MS Office Professional Plus 2013 (my computer), exporting to Excel 97-2007 takes about 20 seconds

    For windows 8-64 bits with MS Office Professional Plus 2013 (VM), exporting to Excel 97-2007 takes about 60 seconds.  

    The most updated information I get is that our R&D department is already made aware of this and it is still under investigation.  

  • 0 in reply to Keith L

    Thanks Keith,

    Could you please also pass on to Development, I tested this on Windows 7 64 bit, with Office 2013, and:

    Sage 50, 2013.2 exported a 30,000 line report to Excel 97-2003 in under 30 minutes.

    Sage 50, 2014.2 exported the same data from a converted file, in the same format, in about 3 hours. 

    The 2014.2 release takes about 5 times longer.  

    Thank you,

    Randy

  • 0 in reply to RandyW

    Hello!

    I hate to revive an old thread, but I can't find any information anywhere else on this and this appears to be the exact issue we are struggling with.
    Upon updating to Microsoft Office 2013 (Office 365 edition 32-bit) exporting to excel has gotten extremely slow, as Randy has described.


    I'm interested as to if anything ever came of this? Was a solution ever found?


    Thanks,

    Kevin

  • 0 in reply to Kevin DariFill
    My guess is that some issue was found by Sage Development. In the 2015.1, 2015.2, and 2015.3 releases the process was much faster - A huge (60,000 line) report went from 40 mins in 2013 to 3.5 hours, and is now back to about 40 minutes.

    But.. I don't really know - Microsoft has released hundreds of patches and updates in that time, as would the antimalware software vendor.

    If you're using Office 2013 'Click-To-Run' (which is the default installation method from Microsoft) there may be delays while Excel downloads and installs additional modules, but those should be one-time things. If using the 'online' version of Office it could be very slow if it works at all.
  • 0 in reply to RandyW
    Well, in that case it appears that I am facing a new issue as the problem still persists for me.

    When you say the 'online' version of Office, are you referring to the browser based suite?
  • 0 in reply to Kevin DariFill
    by 'Online' I mean the Office 365 subscription that only works while you are online, I've not used it but probably it is browser based.

    For this workstation, (4-core (I5-3570) running at 3.40 Ghz, Windows 8.1 64 bit, Office 2013, Sage 50 2015.3) to export 5000 journal entries into Excel takes 17 minutes, for 5,191,168 bytes (51,817 lines x 10 columns).

    To export in text format takes 3 seconds, CSV takes 7 seconds, PDF takes 2.5 minutes (but only the first 378 pages are readable.)

    What is your setup like? (Sage 50 version, O/S 32 or 64 bit, hardware, running over a network, etc.) and what performance are you seeing?
  • 0 in reply to RandyW

    Okay, you're getting significantly better results than we are.

    Here's some information:
    Sage version - 2016 Build 23.0.00.0090 (We experienced the issue on Sage 2015 as well, though I'm not sure the exact version)
    OS - Windows 7 64bit
    Hardware - (i5-2400 3.1 GHz, 4GB Ram)
    Office Version - Microsoft Office 2013 installed via Office 365 (32bit)


    We're running around 20k line reports and it's taking 30 minutes + whereas it used to take 5 minutes max.

    EDIT:

    The Office 365 installation shows up as:

    Microsoft Office 365 ProPlus 15.0.4753.1003 in Programs and Features.

  • 0 in reply to Kevin DariFill

    That is exactly the version of Office I'm using, saving to a local hard drive folder, and running as a local administrator.

    You can use the Excel 2.1 format, which takes 10 seconds (but it crashes out of Sage 50 if there's more rows than 16384 rows)
     
    A lot of online articles recommend disabling animations, I've turned it off and there's not a great difference in speed when exporting from SA 50 2015.3.

     e.g. https://community.intuit.com/questions/827014-slow-exports-to-excel-2013

    I've turned off most of the automation in Excel (spell checking, etc.) long ago, so that may also be different from your system.

    Our network consultants have always had the antimalware software set for what I would call extreme paranoia. About half the time I use 'Open in Excel' it tells me the file is locked. (because it's scanning it for malware). But it doesn't seem to slow down the exporting unless it's scanning the whole drive during the day (which I always make a point of complaining about)

    I find testing any problem on today's computers almost hopelessly convoluted - there are so many moving parts and automation that it's difficult to test the effect of one thing at a time.  

  • 0 in reply to RandyW
    I saw the disabling animations recommendation too, but my understanding was that it was for a different issue than what we are experiencing, so I didn't try it.

    Interesting. I'll try having someone in our finance team disable all that in excel and run the report again and see if it makes a difference. I suppose if Excel is pausing the export to spell check after each cell is filled, that could be a problem.
  • 0 in reply to Kevin DariFill
    I have the exact same issue. Windows 10 64bit running Excel 2016. I just got off a chat session with support. They mentioned that the Export to Excel function was not within the scope of support of Sage 50 then suggested I contact a Sage-authorized consultant.
  • 0 in reply to ChrisT
    Hello Chris,

    I wonder why Export to Excel is out of the scope of Sage 50 support... Didn't they create the Export to Excel function? Or are the implying this problem is on Microsoft Office's side?

    Did you have any luck with the suggestions made in this thread?
  • 0 in reply to Kevin DariFill
    I was trying to read between the lines of the support agent. From what I gather, the Export to Excel feature sounds like it is actually a third-party add-in that they've stopped supporting. "It's there if you want to use it, but it's not our problem" was the gist of the conversation. She said I should make a suggestion for future releases, as in I should suggest that they correct a malfunctioning essential feature for which we pay a significant annual fee. It would be like Honda telling me, "Hey the airbag was made by Takata. It's not ours and we're not supporting it, so you're going to have to figure it out on your own."

    I hope a Sage 50 developer chimes in to this convo and tells me I'm completely wrong and completely misunderstood my conversation with their support agent. That would give me some hope that the problem could eventually be fixed. But alas, it sounds like something we're just going to have to live with for a while.
  • 0 in reply to Kevin DariFill

    Sage 50 has the programmed-in ability to export to text, HTML, to Excel 2.1, and CSV.

    'Open in Excel' and export to "Microsoft Excel 97-2007" use Microsoft Office APIs that the Sage software feeds data to, and it's up to the Microsoft Office program to handle the data from there.  Sage has no little control over the Excel import process at the other end, other than doing it correctly
     
    When I goggled this problem on the interweb I found a lot of support and knowledgebase references to this problem in software that wasn't made by Sage. 

  • 0 in reply to RandyW
    Hmm, I hope to hear from support or the dev team here as well. It would seem like even if it's not their software to support, they could perhaps provide more comprehensive suggestions. I'll talk to support as well and see if I can figure anything out.

    As a software developer I've had the pleasure (haha...) of working with the Microsoft Office libraries. You actually do have a reasonably significant amount of control over it and I've never really had speed issues myself; but at the same time, I've never developed anything that creates a 20k line document.
  • 0 in reply to Kevin DariFill
    I'm kind of amazed at the advice to "contact a Sage-authorized consultant".

    When I was a Sage consultant I received an advance release and an invoice every year, and a semi-regular newsletter.

    I never saw the source code for the Office export interface, so I flip settings on and off, and test what happened.
  • 0 in reply to RandyW
    Hmm, yeah, that's pretty odd.
    I contacted the support team yesterday and was asked to try following a document to remove extra data from the Company data folder. I don't think this has anything to do with the problem though, as the data has not changed significantly in size since before the upgrade to 2013.
    If you're interested in the document it was in the Sage KB, ID: 11379 "When should I purge my data?"

    Eventually he gave me an email address for the SDK Dev Team and told me they would be able to help.

    So, I've emailed them and hopefully they have a solution.
  • 0 in reply to Kevin DariFill
    Unless the data you are purging reduces the data you are presenting, there will be no change in the export time in my opinion. Purging data does not free up hard drive space either.

    I did a bit of testing on this last night. I don't have Excel 2013 (I use 2007) but it looks like the export of reports to the 97-2007 version and the Export to Excel buttons both open Excel in the background and therefore probably use the Excel object model to do the exports. I believe this is the preferred current method but I am not an expert in this.

    The Excel 2.1 option does not open Excel first which is why Randy found it faster (but I guess has the inherent problem that it does not understand the larger row capacity of current Excel versions).

    I butchered some VBA code I have here in Excel that pulls data from the Sage 50 file. When I programmed it to pull all data from one table and present it in a new sheet in the workbook, it resulted in a little over 37600 rows and 22 columns and took 2:51 (that's minutes and seconds). This was just an unformatted data dump that included column headers.

    When I dumped the journal entries report from Sage 50, it took 1:37 (minutes:seconds) to do 8640 rows, 8 columns. So it does depend on the method and type of information that is being dumped to some degree but when Excel is used in the background and the dump is formatted, it does take longer.
  • 0 in reply to Kevin DariFill

    Data storage in Sage 50 (MySQL) works like a filing cabinet, where deleting (by "Purging" in Sage50-speak) would take print off the pages without moving them, and later adding data would printing on the blank pages.   The file cabinet stays the same size until it's completely full, or until it's "reorganized".  (Sage 50 has no menu option that does this)

    I would recommend that you do not "purge" your Sage 50 data for anything other than business reasons (i.e. you want to NEVER see paid invoices from last year in your search results), it will make absolutely no speed difference, and hasn't, since the 2008 version. 

    The only time that purging your data would make certain reports run faster would be when there is no data to report.   Exporting a blank page will go quick, too.

    Technical support staff may make recommendations based on a key word or two, like "Slow", with only the vaguest concept of how things work, and the cause of the problem.   Sage Certified Consultants would be rich if they could bill for all the time spend cleaning up the mess that results from taking some of the more bizarre advice offered. 

    Tuning up the system, and making sure there isn't anything broken or badly misconfigured (like virus-scanning your database folder, enabling VSS snapshots on the data volume, etc.).   There's a lot you can test without a lab full of equipment - for example if it's slow running off the server, try it running on the local workstation and see if it's faster.

  • 0 in reply to Richard S. Ridings

    Richard S. Ridings said:
    The Excel 2.1 option does not open Excel first which is why Randy found it faster

    The Excel 2.1 option does not use any part of Microsoft Excel - it works without Excel installed.

    Richard S. Ridings said:
    I don't have Excel 2013 (I use 2007)

    Count your blessings Richard!

    If you use the Excel 2.1 version export and try to open in Excel 2013, you may go quietly mad looking for this setting:

    ... and all the 'trusted location' and 'trusted publisher' and 'trusted file' settings. 

    On the whole, I find Excel 2013 an improvement, but some of this security paranoia stuff that's baked into it now makes it slow to use, slow to run, and maddeningly difficult to configure.

  • 0 in reply to RandyW
    I have worked a little on client computers with it but not enough to get into all the "advances". I was on a system the other day however, working on a VBA program that works on Sage 50 files. There are other issues with 32 vs 64 bit versions in some aspects. I don't know if exporting is affected by the bit version of Excel but VBA programming is when connecting to the data. I just didn't have time to confirm where the problem was.

    BTW, I didn't become a Certified Consultant to get rich, I was born that way! ;)
  • 0 in reply to RandyW

    Richard S. Ridings said:
    Unless the data you are purging reduces the data you are presenting, there will be no change in the export time in my opinion. Purging data does not free up hard drive space either.

    I had a feeling this would be the case.

    And yeah, I'm pretty sure that's how they're exporting to Excel. Something must have changed between how 2010 handled incoming data and how 2013 handles it.

    The real unfortunate thing here is that I don't have access to Sage and I've never used it before. I'm the technical support guy here, so it's rather hard to find the problem when I can't test (We have about 1 opportunity to test a new things per day.)

    RandyW said:
    Data storage in Sage 50 (MySQL) works like a filing cabinet, where deleting (by "Purging" in Sage50-speak) would take print off the pages without moving them, and later adding data would printing on the blank pages.   The file cabinet stays the same size until it's completely full, or until it's "reorganized".  (Sage 50 has no menu option that does this)

    I would recommend that you do not "purge" your Sage 50 data for anything other than business reasons (i.e. you want to NEVER see paid invoices from last year in your search results), it will make absolutely no speed difference, and hasn't, since the 2008 version.

    I do understand how MySQL works. When I was talking with the tech support agent, I told him the database file was rather small, but we had other information in the Company folder which added up to around 10gb. He said that could negatively impact performance, so we should clear it.

    As far as using Excel 2.1; I hadn't tried using other formats because when I suggested to try the "Export" option instead of "Open in Excel" I was told from our Accounting team that would not give the results they want.