Smartlist: Exports slowly to Excel – Part 1


Patrick Roth - Click for blog homepageIn the last few weeks (here and here), I've enumerated several questions that I've been asked and have seen posted to the public Dynamics GP newsgroups.  These questions are typically of the "Why doesn't Smartlist..?" variety.

In this post and a few planned future posts, I'll look at each of these questions individually and give a few thoughts around them and maybe a few solutions as well.

For this post, the question is:

Why does Smartlist export to Excel so slowly?

Because Smartlist exports row by row and sets each cell individually.  While that doesn't sound like a "performance based" solution, it is really what Excel itself is doing if you fill the Excel grid when you sum a row or other calculation.  And that is fast.  The difference is that Dexterity makes a single call to write to the cell that has to pass through the COM layer to Excel.  And the COM layer is the part that is slow.

As I indicated in my premise of common questions, Smartlist export performance has been an ongoing concern for customers.

A gentleman asked in a newsgroup post around January 2007 about export performance and correctly guessed that the export routine used cell by cell.  He pointed me to Microsoft KB 247412 that talks about methods to interface with Excel.  Excel automation was discussed and "cell by cell" was mentioned but discouraged due to performance reasons.  But one method that I recall was specifically - "Create a recordset and then have Excel use the recordset as a datasource".

That got me to thinking of how I could potentially leverage ADO in Dexterity for this recordset and find a way to make Excel use that recordset.

To make a long story short, I was able to make that work in 9.0 right before Dynamics 10.0 was released.  After proving my theory, I lost interest in the project and somewhat forgot about it except from time to time when I saw an Excel export performance posting.  Not knowing exactly what to do with my creation, I didn't do anything.

Not all that long ago, one of the system support techs had a customer who also was unhappy about export performance.  I mentioned about my app that I'd created and how it did help this issue.  As I explained how it worked and the gains provided, I had opened the Dynamics GP 10.0 Smartlist code to show him how Smartlist did the export.

And when I did, I found something unexpected that didn't exist previously.

Smartlist is looking for a Dex.ini switch and if it finds it, it calls a different Excel export routine than the one you all are familiar with.

In this routine, it loops through the rows and cells of the listview (where the data is displayed) in the same way that the normal export routine does.  But instead of setting each Excel cell with that data, it writes it into a tab delimited file in your temp folder.  The file is SL<userid>.tmp and it is deleted after the export is finished.

After creating the text file, the routine adds a QueryTable to this report using the text file just created.  It refreshes that QueryTable and Excel populates.

How much of a difference does this change make?

A lot. 

In my unofficial testing of an unofficial feature for Smartlist in Dynamics 10.0, I selected the default Account Transactions Smartlist and changed the number of results from 1,000 to 10,000 to get a decent amount of data to work with.

I pressed the Export to Excel button and Smartlist exported the 10,000 rows by 7 columns in 1 minute, 55 seconds (115 seconds).

Then I added the Dex.ini switch:

SmartlistEnhancedExcelExport=TRUE

and saved the dex.ini file.

Since this switch is read on the fly, I didn't have to restart Dynamics or even refresh the Smartlist.  I just pushed the Export to Excel button again.  How long was the export this time?  Would you believe 9 seconds?

Looking at both of the Excel spreadsheets created, I couldn't see any obvious differences in formatting.  And perhaps in this Smartlist that was the case since there was no QTY's or MC information displayed.

So if this is so great (and it sure seems to be based on a few tests that I've done), why is this undocumented?  Why isn't it out-of-the-box functionality since the performance is awesome?

That is hard to say - most likely because it wasn't fully tested.  The other thought is that because of how the data is exported we possibly lose formatting capability to Excel and so the out-of-the-box export won't always match how the export looks with this method.  And if we cannot guarantee that the formats are 100% the same as they used to be, then to not break anybody's report/routine/whatever we left it since it does work - just takes longer.

So what does a Dynamics 9.0 or earlier customer do since this undocumented feature was added in 10.0?  Well, hopefully they'll update to GP 2010 soon.  But until then, remember my project?  Stay tuned for Part 2.

Patrick
Developer Support

Note: As an undocumented ini switch, this falls under "unsupported".  So if your export isn't working or doesn't format things the same way it used to, you are on your own.  You'll have to go back to the "old" method. 

Comments (32)

  1. Mark Polino says:

    Patrick,

    This rocks. This may be the single coolest Dex.ini switch ever! Thanks for sharing this. I’m going to play with huge SmartLists now.

    Mark

  2. Chad Smith says:

    Thank you So much, this is a HUGE Find!

    Thanks for sharing it.

    Chad

  3. Patrick,

    Awesome stuff – thank you very much!

    -Victoria

  4. Doug says:

    I’m amazed at how smart you are. Thanks for sharing this.

  5. mgomezb says:

    I am sure this is not the only "undocumented" DEX.INI switch, but sure is a great one to have in the arsenal.

    MG.-

    Mariano Gomez, MVP

  6. Peter says:

    Are there any known issues with this? (other than improving performance)? 🙂

  7. David Musgrave says:

    Posting from The Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2010/05/undocumented-dexini-switch-cuts-down.html

    Thanks Mariano

  8. Leslie Vail says:

    Great news! And I have another .ini switch for my list!

    Thanks

    Leslie

  9. David Musgrave says:

    Posting from the Dynamics Confessor Blogspot

    http://dynamicsconfessions.blogspot.com/2010/05/speeding-up-smartlist-exports.html

    Thanks Leslie

  10. Patrick,

    I used this in a demo on Friday.  It’s fantastic!  Thanks.

    Steve

  11. David Musgrave says:

    Email from David Flowers

    Posting Comments is disabled at the moment but I wanted to first thank you for this enhancement, but also note that it doesn't seem to render British Pound £ correctly when exporting to Excel.

    Environment is GP 10 service pack 3, UK localization and Office 2007.

    Thanks,

    DavidF.

  12. David Musgrave says:

    Hi David

    I would say that the export using this method just sends out the raw data with no formatting.  That's why there is no formatting.

    Goes back to…. Undocumented and Unsupported…. and lack of formatting is probably one of the reasons.

    David

  13. Mark Polino says:

    Patrick,

    Again, this is a fantastic tip. There isn't a similar tip hiding in there to speed up the export of Navigation List data to Excel is there?

    Mark

  14. Steve Benoit says:

    This is a tremendous improvement for SmartList. I hope the product team properly implements it by R2 of GP 2010.

  15. David Musgrave says:

    Posting from Leslie Vail at the Dynamics Confessor Blogspot

    dynamicsconfessions.blogspot.com/…/smartlist-ini-switch-problems.html

  16. David Musgrave says:

    Posting from Mohammed Feradh Zain at Dynamics GP Middle East

    dynamicsgpme.blogspot.com/…/smartlist-export-enhancer.html

  17. Ian Chesnick says:

    Anyone tried a similar switch on Word?  We found that although some formatting changed or was lost on Excel, the speed seems to be a very decent compromise.

    Our other issue is that we have beem moved onto CITRIX to the file is shared by everyone and so its all or nothing for us.

    Regards

    Ian

  18. Andrei Bruzgulis says:

    It does speed up the export to Excel, but I found out that serial numbers do not get exported properly if serial number does not alpha characters. Then system treats it as a number, thus, drops leading zeros and for one or another reason if SN is longer then 15 numerals it substitutes last 5 most right positions with 0.  

  19. John Ellis says:

    I did find an issue with this switch, if the client is in a Multicurrency environment!  Try an Account Transactions SmartList, and you will see what I mean.

    For companies that are not US Dollar-based, the foreign currency symbols get exported to Excel along with the debit and credit amounts.  But, the symbols are not at all the correct symbols.  They appear, for lack of a better phrase, as "jumbled-up characters".

    So, for non-Multicurrency environments, this switch is great.  Otherwise, I'm afraid that there is this issue.

    I'm going to hunt for a resolution. 

  20. David Musgrave says:

    Hi John

    I have removed your email address from the comment.  Don't want you getting spammed.

    As mentioned before.  There is a reason why this dex.ini switch was undocumented and unsupported…. because it is not fully tested and has known issues.

    Mutli-currency and handling of currency symbols is one of those issues.

    David

  21. Brent Clove says:

    With SmartlistEnhancedExcelExport=TRUE preceding zereos are stripped from number strings when exporting to excel.

    Any solutions?

  22. David Musgrave says:

    Posting from Jivtesh Singh at About Dynamics, Development and Life

    http://www.jivtesh.com/…/everything-dynamics-gp-16.html

  23. Eric says:

    Just wanted to check, was there any solution to the previous question raised where the preceeding zereos were stripped from number strings when exporting to excel with the SmartlistEnhancedExcelExport=TRUE switch?

  24. Patrick Roth [MSFT] says:

    Eric,

    As far as I know, there have been no changes to either the typical or the enhanced SmartList export code.  

    I wonder if you could create a template with the column set as "string"?  The "import" of the text recordset by Excel hopefully would honor that column setting.

    However it would seem a better solution is to use the code that I provided in part 2 of this series.

    blogs.msdn.com/…/smartlist-exports-slowly-to-excel-part-2.aspx

    I re-wrote the export routine as described in the article.  It has been nearly 2 years since I wrote this and looked at the code but I'm pretty sure that this code doesn't have the strip issue you refer to.

  25. Sanjay Kumar says:

    When we export to excel using smart list leading zeros are lost.  Can we get hold of the csv directly instead of  it being opened in excel ?

    Cheers!

  26. Patrick Roth [MSFT] says:

    I assume this happens only when you use the ini switch?

    Yes, I think you could.  If I recall, Smartlist generates a file in your temp folder for this.  I don't think it deletes it.  I can't look it up now or test, you'd have to try it.

  27. Sanjay Kumar says:

    Thank you  Patrick, I tried with my limited abilities to locate the file could not figure it out.  If you have figured out the location would be of great help.  My thought process is the leading zero would not be removed at the csv and I can open the csv in excel by designating the specified column as text.

    I have now installed your cnk file and understand you do this automatically by designating the column as text, which means i don't have to hunt for the CSV.

  28. Mohamed Abdelhafez says:

    i tried it is working , but arabic language appear as symbols

  29. Pam Schneider says:

    Thank you so much for this post I was so frustrated exporting to excel because all the currency fields were exporting as text. With this dex.ini entry they export as currency. I was getting very frustrated with the new smartlist designer and you saved the day!!!

Skip to main content