Earlier I wrote two articles about how to efficiently copy data from SSMS to Excel. Since I wrote that post there are plenty of interest generated on this subject. There are a few questions I keep on getting over this subject. One of the question is how to get the leading zero preserved while copying the data from SSMS to Excel. Well it is almost the same way as my earlier post SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet. The key here is in EXCEL and not in SQL Server.
The step here is to change the format of Excel Cell to Text from Numbers and that will preserve the value of the with leading or trailing Zeros in Excel. However, I assume this is done for display purpose only because once you convert column to Text you may find it difficult to do numeric operations over the column for example Aggregation, Average etc. If you need to do the same you should either convert the columns back to Numeric in Excel or do the process in Database and export the same value as along with it as well.
However, I have seen in requirement in the real world where the user has to have a numeric value with leading Zero values in it for display purpose. Here is my suggestion, instead of manipulating numeric value in the database and converting it to character value the ideal thing to do is to store it as a numeric value only in the database. Whatever changes you want to do for display purpose should be handled at the time of the display using the format function of SQL or Application Language. Honestly, database is data layer and presentation is presentation layer – they are two different things and if possible they should not be mixed.
If due to any reason you cannot follow above advise and you need is to have append leading zeros in the database only here are two of my previous articles I suggest you to refer them. I am open to learn new tricks as these articles are almost three years old. Please share your opinion and suggestions in the comments area.
SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display
SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Leading zeroes to the left of a numeric are often required for things like an Employee ID Number which must have the same number of characters for all records. There is a problem when pasting to Excel and an ID of, say, ‘005726’ gets truncated to 5726. Since this is an ID, there will never be the need to perform math functions on it, so AVG, SUM, etc. are irrelevant. There is not an inutitive way that I know of to preserve those leading zeroes unless you format the entire Excel sheet as Text BEFORE you paste the data in.
I have found that the best solution is to include a single quotation mark at the beginning of the cell when copying and pasting. This ensures the formatting and can easily be remove with ‘TextToColumns’ if necessary.
, CHAR(39) + CONVERT(varchar(20), EmployeeId) As EmployeeID
This is Really helpful. Thank you very much.
The solution did not really help excel will loose its Text formatting when you copy the column with numbers, i found a solution by using “Paste Special”. PFB steps:
1) Open Excel and change the formatting of the destination column to Text.
2) Copy the Column from SQL.
3) On Excel Under Home there is a Clip Board menu named Paste with an arrow, click that arrow. You will get 2 options:
a. Keep Source Formatting.
b. Match Destination Formatting.
4) Click on Destination Formatting.
Now your column in excel will be same as in DB.
Thanks for sharing. Appreciate it.
it worked perfect..thank you
Hi Pinal,
Our issue is more complex. We constantly need to pass bar code values eg Ebids, GLNs, GTINs etc back and forth and Excel either chops leading zeroes off, or turns strings into logarithms. No amount of formatting Excel fields as general, number, text or anything in between prevents one problem or the other.
We end up needing to employ various formulas to refill the zeroes, pre-pend or remove leading apostrophes.
Is there a cleaner way to handle, especially passing back to Excel from SQL, that can be done in a single copy/paste?
Thank you.