convert number to text without losing zeroes

[DaveK]

I cannot find an answer to this problem anywhere, perhaps someone here can
help.
I have 2 columns of data that I pulled from my proprietary database using
ODBC. The first column is part numbers, and the second is a list price for
the part. The part numbers are a mix of numbers, letters, and some dashes.
I am saving this list to a .csv, uploading it to my web server, and then
logging in to my webserver and importing the data from the .csv to a mysql
database.
The problem is coming in when I have part numbers that start with zero. An
example would be 010888. The zero gets dropped off when I save it as a .csv.
I figured I could convert it to text and it would be fine but when I convert
it to text in excel it drops the zero too. A part number like 010888L is
fine because its treated like text and the zero remains through any
conversion.

Please help.. I have some 9000 part numbers that I need to save to a .csv
and 300 of them start with zero.

 

[Ron Coderre]

One of the usual "fixes" is to change the file extension on the CSV file to
..TXT.

Then, when you open the file, Excel's Text Import Wizard will allow you to
set the delimiter to Comma and set the first column to be Text...which will
preserve the leading zeros.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Pete_UK]

Just check that the leadings zeros are not actually missing from
the .csv file by opening it in Notepad - you should be able to see the
format of the file more clearly there. If the part numbers are stored
in Excel as numbers and just formatted to have a leading zero, then
they will be missing from the csv file - a formula like:

=TEXT(A1,"000000")

will convert them to text values. You could fix these values then copy
them back over the offending cells.

If you are bringing the file into Excel then rename it by changing
the .csv extension to .txt. Then with Excel running do File | Open,
point to the .txt file and then Excel will take you into the Data
Import Wizard, where you have more control over how the fields are
treated.

Hope this helps.

Pete

 

[DaveK]

Thanks for the quick response! Unfortunately, the problem lies in excel
converting the "number stored as text" (ie 010888) to 10888 when it writes it
to a .csv. I am using the .csv to populate data in an SQL server. When I
pull the data into excel, it is in the correct format. If I use any of the
tricks I have found to convert the numbers to text, excel drops the preceding
zeroes. If I save the files as .csv it drops the preceding zeroes. I don't
know what excel has against preceding zeroes, but it sure doesnt like to keep
them in my data. I really hope someone has a solution for this..

Dave

 

[DaveK]

Thanks for the reply. I did check with notepad and excel exports the values
with no leading zeroes, which is why I wanted to convert the fields to text
first, but regardless of how I try to convert, excel takes the "number stored
as text" and converts it to a number before converting it to true text, and
always drops the zero. I came across the idea of the function
text(a1,"000000"), but in my list of 9000+ products, the fields are anywhere
from 5 to 19 characters in length, and intermingled with products containing
both numbers and letters.

So, I decided that since Excel is really designed for crunching numerical
data, I should be using something that is designed for crunching data to pull
my data and convert it. I went to access, linked to the data in ODBC,
created a query, and then exported it to a .csv, and got my data with all the
zeroes.

Excel is a really easy way to work with data, especially for database
novices such as myself. For years I have fought with numerical data being
treated incorrectly in Excel, and it would be nice if Microsoft would realize
that there is a shortcoming here that should be fixed. In my search for an
answer, I came across dozens upon dozens of people with the same exact
problem.. importing data with preceding zeroes and having excel drop it. It
is easy to work around these short-falls if you are say, importing zip codes,
as they are all 5 or 9 digits.. but not all data is so cut and dried..

Thanks for the help and the super fast responses!

 

[Ron Coderre]

Sorry, I misunderstood your situation.

Perhaps this approach will work for you....

Make sure the Part Numbers not only *look* like text, but that the cells are
actually formatted as text.

Select the column of Part Numbers
<data><text-to-columns>...Click [Next] until Step_3_of_3
Check: Text (to set the column type)....Click [Finish]

Now try saving the file as a CSV

Does that help?
If no...can you post a sample file at one of the free file-hosting sites and
post the link for us?
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Dave Peterson]

I've never seen excel drop the leading 0's when saving as a .csv file in any
version. But if I reopen the .csv in excel and save there, I can destroy those
leading 0's.

You may want to try saving the .csv file and then check using notepad once more.

 

[David Biddulph]

Your advice to look at the csv with Notepad is sensible, as is the adviceto
rename the csv as txt to control the reimport.

In which version of Excel are you encountering the situation you describe
whereby numbers stored as numbers and formatted with a leading zero will
lose the leading zero on saving as CSV, Pete? That doesn't happen for me
with Excel 2003

 

[David Biddulph]

If you have 010888 as a "number stored as text", then I am sure that it will
be 010888 in the csv (or if not, I would be interested in hearing which
version of Excel you are using?). Look at the CSV with Notepad.

The problem comes if you try to use Excel to read the CSV, as it will drop
the leading zeroes on reimport by converting them from text to numbers.
Ron's suggestion will get round that problem.

 

[DaveK]

It is excel 2003.

Keep in mind this is NOT when you have a number, or a text field, it only
happens when you import data. It is not a true text field (if it was the
stupid green triangle would appear) and it is not a number field. It is
treated as a number but retains the zero. The data is fine until you try to
manipulate it in excel. The only way I found to convert it to true text
without dropping the preceding zero is to do a function =text(a1, "000000")
but that only works if the number has 6 digits, and my data ranged from 3 or
4 digits to maybe 19..

If you want to reproduce this, import data from some source that contains
some numbers and some text in the same column. The numbers will right
justify, and anything with a character in it will left justify.. now save it
to a .csv and open the .csv with notepad.. you will see there are no
preceding zeroes now. If you enter a number manually, it will drop the
preceding zeroes. If you put a single quote in front, it will give you the
green triangle which denotes number stored as text.

Using access to import my data and exporting it to a .csv is working great
and actually works better than excel did.. I should have started with access
to begin with. I use excel perhaps 10 times a day to import data and create
a report so I am very comfortable with it and it is very easy. In older
versions of excel, typing a number in a field that had preceding zeroes
removed the zeroes, and even worse, if the number had 6 digits it would
automatically convert it to a date, so at one time I had hundreds of
spreadsheets with part numbers that the author had to put a single quote in
front of to force it to store the number as text. When I started linking all
these sheets to a mater spreadsheet with all my pricing (this was for our
product catalog), I found that a number stored as text will not lookup from a
regular number that is the same, or from a number imported from an external
source (ie 010888), so back then I had to learn how to convert a number
stored as text to a real number. However, I was never exporting that to a
..csv until now.

Excel needs to add to their import data function and have a raw data field
format where everyting coming in is true text, instead of trying to be smart
and make certain fields numbers just because they dont have characters in
them. Further, there should be a way to define each column when importing
data, much like you can when opening a .txt file.

One more note, I use the .csv data to populate a sql database in a remote
server that does not allow a direct import. I do not open it back up in
excel to check things out and didnt notice this problem until I was trying to
query my sql data with a part number starting in zero.

 

[DaveK]

That was the first thing I tried, and the preceding zeroes disappear. As I
was trying to get across in another reply, I cannot reproduce this situation
unless I am importing the data.. if you go to a general field and type 0123,
you get 123. If you type '0123 you get 0123 with the green triangle. If you
change the format to custom, you set the number of characters and can get
0123. I think what happens on import is excel takes any field with all
numbers and counts the digits and creates a custom field format for that
number. When you try to convert it to anything else, it drops the zero.

I just don't think excel is capable of taking a column of data with a mix of
these custom formats and doing anything without losing the preceding zeroes.
However, Access does not have the field restrictions that excel has, so it
worked for me.

Thanks again for any advice here.. I learned a lot about what excel's
limitations are in the last couple days.

Sorry, I misunderstood your situation.

Perhaps this approach will work for you....

Make sure the Part Numbers not only *look* like text, but that the cells are
actually formatted as text.

Select the column of Part Numbers
<data><text-to-columns>...Click [Next] until Step_3_of_3
Check: Text (to set the column type)....Click [Finish]

Now try saving the file as a CSV

Does that help?
If no...can you post a sample file at one of the free file-hosting sites and
post the link for us?
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Thanks for the quick response! Unfortunately, the problem lies in excel
converting the "number stored as text" (ie 010888) to 10888 when it writes
it
to a .csv. I am using the .csv to populate data in an SQL server. When I
pull the data into excel, it is in the correct format. If I use any of
the
tricks I have found to convert the numbers to text, excel drops the
preceding
zeroes. If I save the files as .csv it drops the preceding zeroes. I
don't
know what excel has against preceding zeroes, but it sure doesnt like to
keep
them in my data. I really hope someone has a solution for this..

Dave

 

[Dave Peterson]

Nobody said that the leading 0's would be saved if you opened a .csv file in
excel.

But if I create a workbook and either enter the value as text '001 (or
preformatting the cell as text), or use a formula like: =text(a1,"000000") or
use a custom format of 000000, then those leading zeros are preserved when excel
saves the file as a .CSV file.

If you leave the cell's format as General and type in: 000123, then those
leading 0's will be lost as soon as you hit enter. Saving to a .CSV file will
not put them back. The data has to have the leading 0's for them to be saved in
the .CSV file.

 

[Peo Sjoblom]

Excel needs to add to their import data function and have a raw data field

format where everyting coming in is true text, instead of trying to be
smart
and make certain fields numbers just because they dont have characters in
them. Further, there should be a way to define each column when importing
data, much like you can when opening a .txt file.

It's already there if you open the csv file using data>import external
data>import data
then use *.* in the file name box. Then you can open teh csv file and it
will trigger the text import wizard where you can format each column


--


Regards,


Peo Sjoblom

 

[DaveK]

sorry let me clarify.. importing data from an ODBC source.

 

[DaveK]

I am not opening the .csv file.. Excel never saves the leading zeroes in the
first place when saving to a .csv file, which is the problem, but they are
there when I import the data. When you do a data import using ODBC, you do
not get a text field, or a number field (although it right justifies it like
a number field), and you dont get the green triangle so it is not number
stored as text, which I thought was the case when I first posted.

I believe excel brings each item in as a custom format if it is all numbers,
and the values with characters it brings in as text, but it labels the whole
column as general (although if I create a custom field with 0000 and type in
0001 the value excel stores is 1, not 0001, where the data imported value
stored is 0001). Changing the format of the column does nothing. Anything
other than cut, copy, paste type actions on these number-like fields results
in Excel converting it to a number and dropping the zero.

I have been unable to duplicate what excel is doing to imported data by
manually entering data.. like you said, if you type 0001, you get 1, unless
you put a single quote in front in which case you get a number stored as
text, which for data purposes is not the same as a custom field (ie you
cannot use a lookup function to get that number).

If anyone is interested in examining some data, I would be happy to email a
sample to them of data imported with ODBC that has leading zeroes. Short of
manipulating each individual field I cannot find a way to save the zeroes
when saving to a .csv file.

Dave

 

[Peo Sjoblom]

I am not opening the .csv file.. Excel never saves the leading zeroes in
the
first place when saving to a .csv file,

You are incorrect, its' when someone opens the csv file in Excel it removes
the zeros. It definitely saves with the leading zeros


--


Regards,


Peo Sjoblom

 

[DaveK]

Trust me, it is not saving the zeroes. Perhaps the newer version of excel
has fixed this, but on excel 2003, if you import data to excel from an ODBC
source that has leading zeroes, then save to a .csv, the fields are stripped
of the preceding zeroes. I can save the file as a .csv, and then open the
..csv in notepad and there are no leading zeroes. If I do the same using
Access the zeroes remain.

 

[Peo Sjoblom]

Trust me, it is not saving the zeroes. Perhaps the newer version of excel
has fixed this, but on excel 2003, if you import data to excel from an
ODBC
source that has leading zeroes, then save to a .csv, the fields are
stripped
of the preceding zeroes. I can save the file as a .csv, and then open the
.csv in notepad and there are no leading zeroes. If I do the same using
Access the zeroes remain.

It is when you import it to Excel it removes the zeros, not when you save
it.
If you would import it as numbers and not text and then use a custom format
showing
leading zeros it will save them in the csv file


--


Regards,


Peo Sjoblom

 

[Dave Peterson]

Are the leading 0's there after you import the data from that ODBC source?

If they're gone after that import, then the .CSV file won't have them. If
they're there (because of formatting or anything else), then you're describing
something new to me. I've never seen it in xl95 to xl2007.

Are you saving the .CSV file using File|SaveAs or are you doing something else?

 

[colin]

Hi,

The solution I have is to select the 1st cell that has the flagged error
then prees control and the down button to highlight the rest of the data in
the column
and then select the option flag which is still visible and select ignore
error

this will then apply this to all highlighted cells and retain the leading zero

rgds

Colin