zip codes in .csv files

[TRathjen]

Has anyone found a way to have zip codes in a .csv file open as text in Excel so that the leading zeros of addresses in the northeastern US are not dropped?

 

[DBohner-(db)]

Just a thought (haven't had to try it yet)... convert the zip to a string on the PDF...

 

[TRathjen]

I am dealing with ADDZ which is a string size 12 to begin with.

 

[Segfault]

After importing, select the column and set the column's cell type to a string in Excel. That is all it takes...

 

[dr2]

check your format options...


On Tue, 21 Jan 2003, TRathjen wrote:

 

[TRathjen]

I have tried that. I view the CSV file from Work With Servers. This brings up Excel. The Postal Code fields, as well as all the others, are formatted General. This causes the postal codes of Chicopee MA to be 1020 instead of their real 01020. When these fields are formatted as text they are now left justified but still 1020.
My current work around is to close Excel after viewing the file, change the extension to .txt, open it in Excel again. This causes Excel to invoke its import routine where I can then tell it Postal Code is to be treated as text. Then I have to save the file as a CSV or XLS file for future use. Very cumbersome. Not for me mind you ;-) but for the people who have to follow these steps. And I really would rather them do the work than me. As it is I have to handhold them every time they try to do this.
Any more good suggestion out there?

 

[vbojan]

Try something like ADDZ = concat(',ADDZ). Single coma should force Excel to format cell as text regardless of data. I didn’t try this but it should work.

Bojan.

 

[KSmithCPA]

Instead of using File Open in Excel try the following:

From the Menu Bar at the top of you Excel Screen select:
Data
Get External Data
Import Text File

Now browse for your *.cvs file (Note: You will need to change the file type to "All" to see *.cvs files.) and click "Import". This will open the "Text Import Wizard". Click the "Delimited" radio button and click next. Select "Comma" and click next. On the "Step 3" screen of the wizard tab over to the column with the Zip Code values and click the radio button at the upper right corner that says "text". This will change the format to text and your leading zeros should be imported. Click Finish and you're done.

Hope this helps.

 

[Carl_Fisher]

Your problem really is with Excel (and the assumptions it makes), you can force the issue by using a custom format, eg 00000, will force leading zeros on a 5 digit number. We have similar problems with formatted G/L codes ours are 4digit (Business Unit). 6 Digit (Object), to force this just requires a custom 0000.000000 this solves the problem of Excel automatically dropping leading zeros and trailing decimals.

 

[TRathjen]

Thanks all for the suggestions. I'll let the people here who actually use these reports decide which they like best. My guess is they will just be changing the format of the field. If I could remember everything I've learned in the last 15 years I probably would have figured that out but my Excel skills have been deteriorating since I jumped full time into Oneworld programming.
When I get a chance I'll try prepending a ' to the field to see what that does.
Thanks again Ken, Carl and Bojan.

 

[DamCPU]

In excel:
1. High light column OR CELL
2. Right click FORMAT cells
3. Click CUSTOM
4. UNDER type: 00000