Smartlist: Exports slowly to Excel – Part 1

Patrick Roth - Click for blog homepageThis is a reposting of an article Patrick Roth originally wrote on my Developing for Dynamics GP blog.

In 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. 

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

34 thoughts on “Smartlist: Exports slowly to Excel – Part 1

  1. 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

    Like

  2. 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.

    Like

  3. 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

    Like

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

    Like

  5. 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

    Like

  6. 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.  

    Like

  7. 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. 

    Like

  8. 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

    Like

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

    Like

  10. 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?

    Like

  11. 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.

    Like

  12. 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!

    Like

  13. 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.

    Like

  14. 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.

    Like

  15. 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!!!

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.