Cells in .CSV do not keep Format

Posted on 2011-09-23
Medium Priority
Last Modified: 2012-05-12
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)
Question by:Catherine Burow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
LVL 50
ID: 36590635

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

LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 36591284
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.

LVL 50
ID: 36591495
@FA, as I read the problem, it's about saving to CSV and retaining the leading zeros. I may be wrong.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

by:Catherine Burow
ID: 36599712

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.

LVL 50
ID: 36601573
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?

Author Comment

by:Catherine Burow
ID: 36602120

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.
LVL 50
ID: 36602169

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.


Author Comment

by:Catherine Burow
ID: 36602563
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.
LVL 50
ID: 36607175
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.

LVL 50
ID: 36616572
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:

Author Comment

by:Catherine Burow
ID: 36720372
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.

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36728279

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.


Author Closing Comment

by:Catherine Burow
ID: 37528889
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 :)

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question