Re: convert number to text without losing zeroes
- From: DaveK <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 25 Sep 2007 16:10:27 -0700
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!
"Pete_UK" wrote:
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
On Sep 25, 11:22 pm, DaveK <Da...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.
- Follow-Ups:
- Re: convert number to text without losing zeroes
- From: Dave Peterson
- Re: convert number to text without losing zeroes
- References:
- Re: convert number to text without losing zeroes
- From: Pete_UK
- Re: convert number to text without losing zeroes
- Prev by Date: Re: convert number to text without losing zeroes
- Next by Date: Is Conditional Protection Possible in 2003?
- Previous by thread: Re: convert number to text without losing zeroes
- Next by thread: Re: convert number to text without losing zeroes
- Index(es):
Relevant Pages
|
