Solved

Cells in .CSV do not keep Format

Posted on 2011-09-23
13
388 Views
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)
 Comma-Sep.csv
0
Comment
Question by:Catherine Burow
  • 7
  • 5
13 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 36590635
Hello,

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.

'00543

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


Comma-Sep.csv
0
 
LVL 14

Expert Comment

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

-FA
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36591495
@FA, as I read the problem, it's about saving to CSV and retaining the leading zeros. I may be wrong.
0
 
LVL 1

Author Comment

by:Catherine Burow
ID: 36599712
All,


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.

~Cburow
0
 
LVL 50

Expert Comment

by:teylyn
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?
0
 
LVL 1

Author Comment

by:Catherine Burow
ID: 36602120
Teylyn,

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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:teylyn
ID: 36602169
cburow,

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

"00123"

into a number like

123

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.

cheers,
0
 
LVL 1

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.
0
 
LVL 50

Expert Comment

by:teylyn
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.

cheers
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36616572
Some screenshots:

CSV file. Note the values with leading zeros.
 csv
The same file when OPENED with Excel: The leading zeros are stripped and the values are displayed as numbers
 Opened
The CSV file IMPORTED into Excel, the third and sixth column have been specified with Text format, keeping the leading zeros in place:
 Imported
0
 
LVL 1

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.

~Cburow
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 36728279
Hello,

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.

cheers,
0
 
LVL 1

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 :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now