Leading Zeros dropped in Excel

[grnzbra]

I am creating a comma separated value file from SQL server using a DTS package. When I look at the zipcode field in the CSV file using Notepad, zipcodes having leading zeros are correct. However, when I open it with Excel, the leading zeros are missing. When I format the cells to Text from General, it still doesn't display the leading zeros. When I save the file after formatting the cells as text and then reopen in Excel, the cells are again formatted as General. Is there any way to get this to hold the settings?

 

[AnotherHiggins]

When you are importing, that field is being converted to a number.

By the time that has happened, it is too late to "bring back" the leading zeros.... Leading zeros have no meaning in a number, so they aren't stored.

Depending on how you're importing the data, you may be able to select that column and tell Excel to treat it as TEXT. Or if you can make the SQL wrap the zip codes in double quotes (" "), then Excel should recognize them as text, not numbers.

Now, if you are only concerned with DISPLAYING the information as a zip code, then you could just format the column as Special > Zip Code. But take note: the cell will not actually contain any leading zeros - You'll see this in the formula bar. But the cell will look like a zip code.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Don;t export as csv

Comma Seperated VALUES

Excel expects a file full of values not text - that is why zeroes are removed

Export as .TXT and your problems should go away

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JockMullin]

Unfortunately enclosing the zip field in quotes does NOT force the cell format to text. Wouldst t'were so.

Instead of using a .csv extension for your file, use .txt. Then when you open it in Excel the formatting wizard will open up. Then you say it is delimited, with commas and when you click on Next you will be shown the file separated into columns. Click on the Zip code column and make it "Text" rather than "General". That will preserve the leading zeroes.

If you save the file in a spreadsheet format that will be remembered. If you save it as a .txt again, then you will have to go thru the process each time you open it.

Jock

 

[grnzbra]

Thanks, everyone. Unfortunately, it must remain a CSV file.

 

[JockMullin]

You mean you can't rename it to .txt??
Not telling you to change the contents, just the file extension.

Jock

 

[ck1999]

If you can change the format to general then change the format to custom and set the format to 00000

ck1999

 

[AnotherHiggins]

FYI, ck - That's the same thing as using the special > zipcode format I mentioned above, and it carries the same caveats.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Thanks, everyone. Unfortunately, it must remain a CSV file.

Then you must endure losing the leading zeroes - there is no way to keep them when using a csv file - the clue is in the name as I explained earlier

Really don't see why you cannot export as .txt as opposed to .csv

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

IF you FORMAT the cells as TEXT, BEFORE the values are entered, then saveas a .csv, the leading ZEROS are in the .csv file.

HOWEVER, you cannot just OPEN the .csv in Excel. The values will STILL be interpreted as NUMBERS since there is NO FORMATTING SAVED in the .csv.

You can solve this by IMPORTING the .csv into a sheet, via Data > Import External Data > IMPORT FILE...

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[grnzbra]

The problem is that a button on a web page has to pop up a spreadsheet view of the .csv file that is used by someone else for something that requires a .csv file. ie. I've been told that it is to remain a .csv file; not .txt files allowed. Thanks anyway.

 

[SkipVought]

Then enter your ZIP codes, preceeded by a SINGLE QUOTE
[tt]
'02178
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

You can alos run a little macro like this...

dim r as range
for each r in range([A1], [A1].end(xldown))
  with r
    if isnumeric(.value) then .value = "'" & .value
  end with
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[grnzbra]

Thanks for the efforts guys, but I think I'm going to have to run this in the asp area. I was hoping that there was some way of saving the "text" setting, but apparently this spreadsheet is being created on the fly.