Whatever the reason they provide to justify this new behaviour when exporting, this is clearly a bug they don't want to fix because they are short in time... The workaround is not working so good since you need to select decimal columns without including the header line and then convert to numbers... It is painfull and lead to a lack of time...
They may call this a feature, but it absolutely is a BUG!
Saying you can just convert the text to numbers in Excel is insane. Not only do you have to pick out just the correct columns/cells, then when you do it it takes Excel a long time. (It takes longer to fix all of text than it does to export.) In one example I gave up after Excel was converting cells for more than an hour. I ended up downgrading SQL Prompt and re-exporting before Excel finished "fixing" the bad file.
If this isn't fixed our company won't be renewing our support contract. (Why would we pay for upgrades when we can't install them because critical functionality is broken in the new versions.)
Unfortunately I have to report that our development team have re-reviewed the decimal data type output to Excel concern and, with their current workload and available resources, have made the decision that this is not an item they are able to provide a fix for at this time.
As mentioned by @alphonseg there are workarounds within Excel are to use the Data > Text to Columns ribbon-menu option or right-click > Format Cells... context-menu options to change the formatting on these cells.
Apologies for any inconvenience this may cause with exporting to Excel; and whilst we cannot commit to a fix at this time it is possible that changes may occur in future releases.
@Jon_Kirkwood Really? They don't have time to pull the Excel export code out of the 10.12.1/2 version of the source code and replace the crap code that is in the current version?
Don't make it look like what SSMS displays, that isn't why we want the data exported to Excel. The scientific notation display in Excel isn't/wasn't a bug. (If you insist on having the capability to export numbers as text make it an option that people can select.)
That isn't a viable work-around... I can uninstall SQL Prompt, Install the old version, start SSMS, run the query, and export it again before Excel would get done converting the text to numbers.
@ATurner, I'd really like to know how many users requested this change. Or was it just that someone at Redgate got a 'bright' idea?
Exactly. I just looked in UserVoice and I don't see that anyone requested or voted for this change. So now we have to request and vote to revert this change/"feature", or at least make it optional. Why?
I, like others here, am quite disappointed that this is still a problem. The matter was reported at the start of August and we're now almost at the end of October; almost 3 full months have passed. Data Typing is such a fundamental part of working with data (I hope we all know how bad/foolish it is to store numerical data in a varchar here) so the fact that numerical data is being converted to a string in this process and yet to be fixed is a huge fundamental breaking change.
Altered Export to Excel to treat numeric values as numbers in Excel.
Does this release also fix the horrible lag in typing in a script that existed in 10.12.5? (10.12.3 claimed to fix it for large snippets, but in 10.12.5 I had the lag even with small snippets. Another reason I rolled back to 10.12.1.)
The new release's behaviour seems consistent with the 10.12.2 version. Luckily it seems that no major functionalities have been added to the recent releases affected by the export to Excel bug. Thank you for reverting the change!
Version 10.12.6.30951 is working for me also. Thanks again for including this. This helps significantly in my workflow. It honestly would have meant that I never updated the application if it wasn't fixed which defeats the purpose of having a product on a subscription model.
Answers
The workaround is not working so good since you need to select decimal columns without including the header line and then convert to numbers...
It is painfull and lead to a lack of time...
Saying you can just convert the text to numbers in Excel is insane. Not only do you have to pick out just the correct columns/cells, then when you do it it takes Excel a long time. (It takes longer to fix all of text than it does to export.) In one example I gave up after Excel was converting cells for more than an hour. I ended up downgrading SQL Prompt and re-exporting before Excel finished "fixing" the bad file.
If this isn't fixed our company won't be renewing our support contract. (Why would we pay for upgrades when we can't install them because critical functionality is broken in the new versions.)
Don't make it look like what SSMS displays, that isn't why we want the data exported to Excel. The scientific notation display in Excel isn't/wasn't a bug. (If you insist on having the capability to export numbers as text make it an option that people can select.)
That isn't a viable work-around... I can uninstall SQL Prompt, Install the old version, start SSMS, run the query, and export it again before Excel would get done converting the text to numbers.
The team are currently looking into changes to this.
This behavior has been changed in todays release.
- Altered Export to Excel to treat numeric values as numbers in Excel.
Does this release also fix the horrible lag in typing in a script that existed in 10.12.5? (10.12.3 claimed to fix it for large snippets, but in 10.12.5 I had the lag even with small snippets. Another reason I rolled back to 10.12.1.)It honestly would have meant that I never updated the application if it wasn't fixed which defeats the purpose of having a product on a subscription model.
Thanks again!
SSMS: 18.2.1
SQL Prompt: tried the latest january version and both 10.12.6.30951
After opening in Excel, all decimal values have a green icon. When I convert it to value, it works fine.
Any workaround? It normally worked perfectly.
There is currently an issue with export to excel that the team are looking into.