Re: convert number to text without losing zeroes
- From: "David Biddulph" <groups [at] biddulph.org.uk>
- Date: Wed, 26 Sep 2007 08:59:49 +0100
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.
--
David Biddulph
"DaveK" <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F3AC711A-8594-4F1B-A86E-F603C02EA938@xxxxxxxxxxxxxxxx
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
"Ron Coderre" wrote:
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)
"DaveK" <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:157ED86E-1DA3-46D7-A624-5A7B4D866D74@xxxxxxxxxxxxxxxx
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.
.
- References:
- Re: convert number to text without losing zeroes
- From: Ron Coderre
- Re: convert number to text without losing zeroes
- Prev by Date: Re: convert number to text without losing zeroes
- Next by Date: Excel 2003: How to make transparent columns in Excel chart?
- 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
|
