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
IT-NYCAsked:
Who is Participating?
 
Patrick MatthewsCommented:
IT-NYC,

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: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_1300-Get-Excel-OpenText-to-Import-Exact-File-Contents.html

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

Patrick
0
 
IT-NYCAuthor Commented:
The original data is in Excel 2010.
0
 
Dave BaldwinFixer of ProblemsCommented:
Define it as a text field so it will not be treated as a number.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
GrahamSkanRetiredCommented:
My postcode is 'GU3 2EB', and I don't have any problem with it :)
0
 
Patrick MatthewsCommented:
Yes, Graham, but you all drive on the wrong side of the road.  It's a wonder anyone makes it home alive :)
0
 
GrahamSkanRetiredCommented:
I'm OK in Japan,  but there are greater hazards there.
0
 
MikeMcEvoyCommented:
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.


'Example:

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

0
 
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
zip.csv
0
 
Dave BaldwinFixer of ProblemsCommented:
This is the way I think it should be formatted as all text.
zip2.csv
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.