Maintaining 5 digit zip code in CSV file - Excel 2007

Jenna Sauber · April 2009

As we all know, we must save import files for Convio in a CSV format. As we also know, CSV strips a lot of formatting from excel worksheets, so we usually have to go back in and reformat cells to show the full zip code. In the past, I've saved a a file as a CSV after doing this and it appears to maintain the formatting.

However, we recently upgraded to Excel 2007 at our office, and when I open a file I had saved, the leading 0s in a zip code are NOT saved. The weird thing is that when checking past imports, the files themselves don't have the 0s, but the individual constituent profiles DO. This somewhat comforts me, but I would like to figure out if it's possible to save a CSV file in Excel 2007with all 5 zip code digits. And if it's not, can someone affirm that the full zip codes are being captured in Convio anyway?

William Tuthill · April 2009

Hi Jenna,

In Excel 2007 there is a zip code format that can be applied to preserve leading zeroes. In the Format Cells utility, chose "Special" as the Category and "Zip Code" or "Zip Code + 4" as the Type. If you save the file using this formatting the leading zeroes will be maintained. You may need to reapply this cell formatting every time you open up the file. Otherwise it is possible that the data appears as having leading zeroes stripped in Excel when in fact they do exist in the file. All this being said, because of this risk with Excel we recommend using a text editor (there are some good free one's available such as Crimson Editor or Notepad++) which does not auto format data like Excel does. The loss of leading zeroes is a particular concern with Member IDs (the off-line unique constituent identifier) because this is the primary field used for constituent matching when uploading records to Convio. A member ID of "1" is not considered the same as a member id of "01".

In regard to your second question about full zip codes being captured in Convio, the answer is not black and white. Some upload operations will take the data just as it is in the file and apply the update. Some operations will run the address through address standardization first which would result in the leading zeroes being added back in. Please check with Support on the specifics of the particular operation or operations you are using. But of course the bottom line is to pay close attention to the accuracy and completeness of the data in the upload file.

Matt Burghdoff 2 · April 2009

Hi Jenna,

You don't actually have to save your files in .csv format. When you choose to download a file, you can switch it to .txt pretty easily (we do this virtually all the time since some of our Member IDs have leading zeros). All you have to do is:

Right click the file you want to download

Save As.../Save Link As...

Delete ".csv" at the end of the file name and change to ".txt"

Change "Save as type" or "File Format" to "All Files"

When you're ready to open the file, just open an excel window and select your file. It'll ask you some questions about how the file is setup, and I can walk you through that if you're not comfortable with it. FYI, your files from convio will always be "Delimited" --> Comma.

Maintaining 5 digit zip code in CSV file - Excel 2007

Comments

Categories