Cells in .CSV do not keep Format

I am using a .CSV file to import addresses for shipping. The problem is that the .CSV will cut off any zip code beginning with "0" This is a big problem and causes problems with the delivery of the shipment, and wastes money on shipping items with invalid zip codes.

Is there any way for me to protect the "Zip code" formatting when I save as a .CSV? (example file attached)
Catherine BurowCustomer Engagement ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:

in the scenario you describe, it would be best if you worked with a master Excel file, where the zip code column is formatted as Text. You can then enter values with a leading zero.

Keep this file as an Excel file. When you need a version to import into your shipping software, save the current state of the Excel file as a CSV and use that CSV as the import source file in the shipping software.

To get the properly formatted CSV file into Excel with the leading zeros intact, use the Import dialog.

Excel 2003 and earlier:
- Data > Import External Data > Import Data > Select "Text file" in the dropdown for "Files of type" and then navigate to the location of the CSV file.
  import dialog- Follow the steps in the Import Wizard. Select "Delimited" in step 1, tick "Comma" in step 2. In step 3, select the zip code column and tick "Text" in the Column data format pane.

 import wizard
In Excel 2007 and later
- Data ribbon > From Text > navigate to the location of the file
- Follow the steps in the Import wizard as above. The interface looks a bit different, but not much.

Save the file as an Excel file and make this your master copy. Select the whole column with the zip codes and format it as Text to ensure that new rows will not use the General format for zip codes.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

either format the cells as text before you enter the numbers with the leading zeros, or enter the numbers with a leading single quote character.


This will keep the leading zeros in the csv.

see attached. The first zip was entered with  a single quote, the next two were formatted as text and had leading zeros.

cheers, teylyn

Farzad AkbarnejadDeveloperCommented:
You must open csv file as follow:
1. Using Import Text file from Data menu command.
2. Choose Text column format for your zip code column when you are importing.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@FA, as I read the problem, it's about saving to CSV and retaining the leading zeros. I may be wrong.
Catherine BurowCustomer Engagement ManagerAuthor Commented:

When I opened the .CSV that @teylyn provided, the values were still lacking the zeros. Basically the problem doesn't actually occur when I save the document (while it's still open) the problem is when I open it again, and when I import it into our shipping software. It seems to me (And I'm no Pro) that a .CSV does not save special formatting such as Zip Code, or text.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Opened with what? Open it in Notepad and you see the leading zeros. Open it in Excel and Excel will transform the numbers with leading zeros back to numbers.

Instead of opening the csv (by double clicking it or by using File > Open), use the Text Import dialog, and in step 3 of the text import wizard specify that the Zip column is text. If you let Excel apply the defaults, then the codes will be converted back to numbers.

I don't know what your shipping software requires to maintain leading zeros.

A CSV file does not hold ANY formatting, it just contains data separated with commas. To make sure that values are treated as literal values, they need to be enclosed in quotes.

What does your shipping software expect the zip code to be? A number or text?
Catherine BurowCustomer Engagement ManagerAuthor Commented:

I am using Excel to open it (in order to verify) but I understand why the .csv isn't saving the formatting. Per your comment, I've opened the file using the notepad, but I do not believe that I would be able to import data to our shipping software using a Notepad file (I will try as soon as I can and post my results)

I don't think that our shipping software is able to differentiate between text-only and number-only zip codes because it will accept both US and international zip code formats (int'l sometimes includes letters AND numbers, which is why *I think* that there's no specification)

If a .csv file will not save the formats, then this technically has a "You can't do that" solution. Which is ok. But I will still try to import the addresses using the Notepad and see if that works. Will check back once I've tried this to verify if this is a valid work-around.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

There is no such thing as a Notepad file. The csv file is a container for data. When you open it with a tool, the content may or may not be changed.

If you OPEN a csv file with Excel, then leading zeros for values like 00123 will be stripped and the value will be converted to a number.
If you IMPORT a csv file into Excel and specify that the value "00123" is TEXT, then the leading zeros will be retained.
If you open a csv file with Notepad, the content will not be manipulated.

The problem seems to lie with the shipping software, not with Excel or with CSV files. If the shipping software turns text values like


into a number like


then this needs to be tackled from the shipping software side. The CSV file has the correct information. What the shipping software does with it is the problem.

Catherine BurowCustomer Engagement ManagerAuthor Commented:
The shipping software does not change the values, opening the .CSV file in Excel is when the number 007750 gets changed to 7750.

The shipping software only takes the info that is included in the file, no matter what it is, if it is all 000000, it will enter that in, no changes.

The problem is that when you save a .csv and then open it in Excel, Excel will change it from 0007750 to 7750 because the .csv does not save the formatting, and there is no way to FORCE it to save it.

I am going to try using the .txt file to import the addresses into the shipping software and see if that works.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Have you read any of what I've written above? I think I explained several times what happens to leading zeros when you OPEN a CSV in Excel. Have you read that at all?

Why do you need to open the CSV in Excel at all, if it is destined for the shipping software? What is the problem?

Excel can CREATE CSV files with leading zeros for numbers.
Excel can IMPORT CSV files and correctly show leading zeros for numbers formatted as text.

It's only when you let Excel apply its default conversion mechanisms that leading zeros are stripped from numbers. If you understand that and if you then opt for one of the methods to retain the leading zeros, then they show fine in Excel.

So, again, what's the problem?

You say:
>>The shipping software only takes the info that is included in the file, no matter what it is, if it is all 000000, it will enter that in, no changes.

Well, then just feed it the CSV file. We've established that the CSV file has leading zeros for the zip code.  So, use that file as the source for your import to the shipping software.

If you don't want Excel to destroy the leading zeros, then leave the CSV alone and don't open it with Excel.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Some screenshots:

CSV file. Note the values with leading zeros.
The same file when OPENED with Excel: The leading zeros are stripped and the values are displayed as numbers
The CSV file IMPORTED into Excel, the third and sixth column have been specified with Text format, keeping the leading zeros in place:
Catherine BurowCustomer Engagement ManagerAuthor Commented:
I am very sorry for my confusion, and I *have* been reading your comments. The problem may be that I have to open this document to add, edit, and remove addresses all the time. It's kind of a revolving list, so that's why I can't really just save it as .CSV and then not open it again.

I would like to try the IMPORTING .CSV into Excel, but I have not been able to figure out how to do that. Would you mind providing steps for that?

Thank you, I appreciate your help on this, and I'm sorry if I've frustrated you.

Catherine BurowCustomer Engagement ManagerAuthor Commented:
Although the .csv file will not save the "0" in the beginning of the zip code, this problem can be avoided by using a normal excel file as you suggested. Thanks so much for your help :)
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.