I have a recurring problem when converting a spreadsheet from .xls to
.csv format. I have a column of either 12- or 13-digit numbers (UPC
codes) that I need to carry over to a .csv file. There are no special
characters or alphas in the number string. Sometimes they have a
leading zero (or zeroes), other times they don't. When I convert and re-open the
.csv, the number is almost always displayed in scientific notation.
Also, I tend to lose the leading zero(es). How can I save the column of
numbers when saving the existing .xls as a .csv so that 1) the column
is displayed properly upon opening the .csv, and 2) any leading zeros
are preserved?

Hi, 

You are having this problem because the column is formatting the
values as numbers instead of text. Expand the column out so that all
of the codes are viewable on the screen (change the column width),
then right click on the column header area (the letter), and choose
"Format" then choose Text as the format you want to use for that
column. Save the file, and then save as CSV. The leading zeros will
now be displayed and it will not change long numbers to notation.

You should do this with any column, which is not "numeric", a good
rule of thumb is if you don't add it, then it is not numeric. Another
example is zip codes, adding zip codes is meaningless, so they are
text (zip codes also have leading 0's in many areas).

There is a website with some very good Excel tips and tricks at
Daily Dose of Excel on
http://www.dicks-blog.com/

thanks, 

webadept-ga

---

Request for Answer Clarification by everyman-ga on 12 Jan 2005 07:15 PST

I have tried this before, without success. I've tried it on the file
I'm currently working on, and cannot get the column to open without
displaying in scientific notation. Any other ideas, such as excel
settings I may have missed? Thanks for your effort.

---

Clarification of Answer by webadept-ga on 13 Jan 2005 07:38 PST

Is there a way that you could post all or part (maybe 10 or 15 rows)
of the file onto the web so I can take a look at it? I would be able
to write exact instructions for conversion for you if I could see the
file you are looking at, or enough of it to get a good idea of what
you need to do.

webadept-ga

---

Request for Answer Clarification by everyman-ga on 13 Jan 2005 12:18 PST

Thanks for your concern with this. Two files are posted. When you
download the one without the extension, rename with .xls extension. I
don't know why, but when I uploaded with the extension and downloaded
again, the download had all row data in the leftmost ('A') cell,
comma-delimited. Tried it again without the .xls and worked perfectly.
The .csv file is what I got after saving this .xls, closing the file,
then opening it again.

www.royalelectric.com/goog/example

www.royalelectric.com/goog/example.csv

Thanks again.

---

Clarification of Answer by webadept-ga on 13 Jan 2005 13:01 PST

Hi, I got them, you can take them off your server now if you wish.
I'll take a look and get back to you with a solution as soon as
possible.

Thanks

webadept-ga

---

Clarification of Answer by webadept-ga on 20 Jan 2005 19:50 PST

Hi, 

Sorry about the delay in getting back to you on this, but I'm moving
and ... well, let's just leave it at that, I'm moving. So... back to
your question.

I downloaded both files and took a look at them. The CSV file came
through just fine. I re-read your clarification request and then
checked the file again, and then it struck me,... you are probably
opening and checking the CSV file "with Excel" and it is changing the
column of information (column A) to the scientific format you are
seeing as an error.

CSV files (even though Excel takes them as its own) are really just
plain text files, which is why they are popular for transferring
information between different formats. If you open the file in
Notepad, or some other plain text editor, then you will see that the
first column is indeed formatted correctly. To show you this, I'm
posting here, the first three columns from your CSV file.

UPC/SOURCECODE,CATALOG NO,DESCRIPTION,PRODUCT LINE,LIST
PRICE,BOOK/BASE PRICE,QTY,WEIGHT
781087028056,71A0280500D,LPS BAL 18W L69 120/277V C&C,ADV.HID,209.75,171.98,9,3.77
781087010464,71A0280510D,LPS BAL 18W L69 120/277V C&C,ADV.HID,210.69,172.75,6,3.8
781087028063,71A0280600,LPS BAL 18W L69 120/277V C&C,ADV.HID,196,160.7,12,3.67

As you can see, the numbers are correctly shown. The only thing you
have to do to insure that the column is always outputting the proper
information is to change the column formatting (as I described
earlier) to text format, and it will output with the leading zeros and
proper number.

If you have any other questions regarding this feel free to ask for
clarification again and I'll get right back to you.

thanks, 

webadept-ga

Thanks for staying with it and giving me realistic tips.

I think making the format of the numeric fields as general will solve
your problem. I do not remember exactly but the catch lies in the
number format used for the numeric fields.

OK here it is. Do an import of this CSV file rather than directly
opening it. Make the "Column data format" for the fields with leading
zeroes as "TEXT". This will retain the leading zeroes.

valid procedure, but this still does not allow me to save the new file
as a csv and reopen correctly formatted. thanks for the try.

This might not be completely appropriate to this thread, but I've had
a similar problem. I work with software that generates CSV files, but
those generated files always result in the loss of leading 0s. After
trying several tricks, I finally found one that worked. The following
line of raw CSV will render a zip code properly:

Zip, ="00123", etc.

This will render in Excel as:

Zip | 00123 | etc.

Hope this helps!

pents90 you rock!! That solved my problem for generating csv files
from Brio reporting to be displayed in Excel!

I have been puzzled by this problem for a long time.  I used Excel-VBA
to create CSV input file for UPS Worldship.  And the problem of losing
leading zeros in zip codes crops up from time to time.

Here is the the real cause: it is NOT that Excel saved the CSV file by
stripping the zero!  It is that Excel strips the leading zeros when it
OPENS the CSV file.  So, it you format the data correctly with leading
zeros through one of the many ways (such as using '01234 as oppose to
01234), do not re-open the file with Excel to look at it!  If you had
to look at it, use Notepad.

If you had not re-open it with Excel, the CSV file is good for
importing by other applications. Learning this helps explain why I
only had the problem "from time to time" by not always!