convert number to text without losing zeroes

Hey
dont open in Excel .
open with Notepad it will work fine...

> On Tuesday, September 25, 2007 6:22 PM Dave 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.


>> On Tuesday, September 25, 2007 6:27 PM 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" > wrote in message
>> ...


>>> On Tuesday, September 25, 2007 6:38 PM 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 > wrote:


>>>> On Tuesday, September 25, 2007 6:40 PM Dave wrote:

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


>>>>> On Tuesday, September 25, 2007 7:10 PM Dave wrote:

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


>>>>>> On Tuesday, September 25, 2007 7:47 PM Ron Coderre wrote:

>>>>>> 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)
>>>>>>
>>>>>>
>>>>>>
>>>>>> "DaveK" > wrote in message
>>>>>> ...


>>>>>>> On Tuesday, September 25, 2007 8:08 PM Dave Peterson wrote:

>>>>>>> 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.
>>>>>>>
>>>>>>> DaveK wrote:
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Dave Peterson


>>>>>>>> On Wednesday, September 26, 2007 3:54 AM David Biddulph wrote:

>>>>>>>> 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
>>>>>>>>
>>>>>>>> "Pete_UK" > wrote in message
>>>>>>>> ups.com...


>>>>>>>>> On Wednesday, September 26, 2007 3:59 AM David Biddulph wrote:

>>>>>>>>> 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" > wrote in message
>>>>>>>>> ...


>>>>>>>>>> On Thursday, September 27, 2007 10:55 AM Dave wrote:

>>>>>>>>>> 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.
>>>>>>>>>>
>>>>>>>>>> "David Biddulph" wrote:


>>>>>>>>>>> On Thursday, September 27, 2007 11:06 AM Dave wrote:

>>>>>>>>>>> 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.
>>>>>>>>>>>
>>>>>>>>>>> "Ron Coderre" wrote:


>>>>>>>>>>>> On Thursday, September 27, 2007 11:07 AM Dave Peterson wrote:

>>>>>>>>>>>> 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.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> DaveK wrote:
>>>>>>>>>>>>
>>>>>>>>>>>> --
>>>>>>>>>>>>
>>>>>>>>>>>> Dave Peterson


>>>>>>>>>>>>> On Thursday, September 27, 2007 11:08 AM Peo Sjoblom wrote:

>>>>>>>>>>>>> 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


>>>>>>>>>>>>>> On Thursday, September 27, 2007 12:51 PM Dave wrote:

>>>>>>>>>>>>>> sorry let me clarify.. importing data from an ODBC source.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> "Peo Sjoblom" wrote:


>>>>>>>>>>>>>>> On Thursday, September 27, 2007 1:07 PM Dave wrote:

>>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> "Dave Peterson" wrote:


>>>>>>>>>>>>>>>> On Thursday, September 27, 2007 1:33 PM Peo Sjoblom wrote:

>>>>>>>>>>>>>>>> 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


>>>>>>>>>>>>>>>>> On Thursday, September 27, 2007 3:55 PM Dave wrote:

>>>>>>>>>>>>>>>>> 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" wrote:


>>>>>>>>>>>>>>>>>> On Thursday, September 27, 2007 4:17 PM Peo Sjoblom wrote:

>>>>>>>>>>>>>>>>>> "DaveK" > wrote in message
>>>>>>>>>>>>>>>>>> ...
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> 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


>>>>>>>>>>>>>>>>>>> On Thursday, September 27, 2007 5:22 PM Dave Peterson wrote:

>>>>>>>>>>>>>>>>>>> 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?
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> DaveK wrote:
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> Dave Peterson


>>>>>>>>>>>>>>>>>>>> On Monday, December 10, 2007 8:18 AM coli wrote:

>>>>>>>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> "DaveK" wrote: