Smartlist: Exports slowly to Excel - Part 1
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.
Comments
Anonymous
May 13, 2010
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. MarkAnonymous
May 13, 2010
Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2010/05/read-this-right-now-smartlist-exports.htmlAnonymous
May 13, 2010
Thank you So much, this is a HUGE Find! Thanks for sharing it. ChadAnonymous
May 14, 2010
Patrick, Awesome stuff - thank you very much! -VictoriaAnonymous
May 14, 2010
I'm amazed at how smart you are. Thanks for sharing this.Anonymous
May 14, 2010
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, MVPAnonymous
May 14, 2010
Are there any known issues with this? (other than improving performance)? :)Anonymous
May 14, 2010
Posting from Dynamics GP Land Blog http://dynamicsgpland.blogspot.com/2010/05/making-smartlist-export-to-excel-faster.htmlAnonymous
May 14, 2010
Posting from The Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2010/05/undocumented-dexini-switch-cuts-down.html Thanks MarianoAnonymous
May 14, 2010
Great news! And I have another .ini switch for my list! Thanks LeslieAnonymous
May 15, 2010
Posting from the Dynamics Confessor Blogspot http://dynamicsconfessions.blogspot.com/2010/05/speeding-up-smartlist-exports.html Thanks LeslieAnonymous
May 16, 2010
Patrick, I used this in a demo on Friday. It's fantastic! Thanks. SteveAnonymous
May 24, 2010
The comment has been removedAnonymous
May 24, 2010
The comment has been removedAnonymous
May 24, 2010
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. DavidAnonymous
May 25, 2010
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? MarkAnonymous
May 25, 2010
This is a tremendous improvement for SmartList. I hope the product team properly implements it by R2 of GP 2010.Anonymous
June 07, 2010
Posting from Leslie Vail at the Dynamics Confessor Blogspot dynamicsconfessions.blogspot.com/.../smartlist-ini-switch-problems.htmlAnonymous
July 06, 2010
Posting from Mohammed Feradh Zain at Dynamics GP Middle East dynamicsgpme.blogspot.com/.../smartlist-export-enhancer.htmlAnonymous
July 08, 2010
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 IanAnonymous
September 27, 2010
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.Anonymous
October 03, 2010
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.Anonymous
October 04, 2010
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. DavidAnonymous
August 02, 2011
The comment has been removedAnonymous
August 08, 2011
Posting from Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../everything-dynamics-gp-16.htmlAnonymous
March 12, 2012
The comment has been removedAnonymous
March 12, 2012
The comment has been removedAnonymous
May 31, 2013
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!Anonymous
May 31, 2013
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.Anonymous
June 01, 2013
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.Anonymous
February 25, 2014
i tried it is working , but arabic language appear as symbolsAnonymous
June 04, 2014
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!!!