Preserve ZipCode and Zip4 formatting while saving as .csv

I need to preserve ZipCode and Zip4 formatting in .cvs. No matter what I do (have been through a few suggestions through Google), it truncates 0s after saving as .csv
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IT-NYCAuthor Commented:
The original data is in Excel 2010.
Dave BaldwinFixer of ProblemsCommented:
Define it as a text field so it will not be treated as a number.
Patrick MatthewsCommented:

If you use the Text format for those columns, saving to CSV preserves the leading zeroes.

To prove it, open the resulting CSV file in Notepad, NOT in Excel.  (Excel, in its zeal to be helpful, will try to convert the text strings to numbers.)

To get Excel to open CSV files faithfully, you may want to see this article:

The article has some fine work, and I also posted a comment showing how I implemented something similar in a previous EE question.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

My postcode is 'GU3 2EB', and I don't have any problem with it :)
Patrick MatthewsCommented:
Yes, Graham, but you all drive on the wrong side of the road.  It's a wonder anyone makes it home alive :)
I'm OK in Japan,  but there are greater hazards there.
I agree with the brain trust above. Postal Codes should use a text field.

However... if it's necessary to store it in a number field you could always add the leading zeros to the string when you want to display it on a form or report using a function.


Public Function AddLeadingZeros(PostalCode As String) As String
Dim ReturnedString As String
Dim NumOfZerosToAdd As Integer

  NumOfZerosToAdd = 5 - Len(PostalCode)
  ReturnedString = String(NumOfZerosToAdd, "0") & PostalCode
  AddLeadingZeros = ReturnedString

End Function

Open in new window

IT-NYCAuthor Commented:
I am enjoying this conversation:) It started so innocent :)
My .cvs file has to be used in the Microsoft CRM 4.0 import function, so it needs to be stored properly within the file itself. I tried all the suggestions, even prior to the post. It actually does display the 0s properly when opened in Notepad but when used in the CRM import, it brings '876' instead of '00876'.
I am attaching a sample for your review
Dave BaldwinFixer of ProblemsCommented:
This is the way I think it should be formatted as all text.
IT-NYCAuthor Commented:
Thank you everyone!
I believe the key issue was not to open the properly formatted file in Excel again. Check in Notepad to make sure it is correctly formatted and use it.
I did that and my import to CRM process worked correctly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.