IT-NYC
asked on
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My postcode is 'GU3 2EB', and I don't have any problem with it :)
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.
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 FunctionASKER
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
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
This is the way I think it should be formatted as all text.
zip2.csv
zip2.csv
ASKER
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.
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.
ASKER