Excel opens .CSV file and drops leading zeros #$&@$#

[DougP]

I am creating a .CSV file using Access and when I double click it, it opens in Excel. This is good since I can use the summation key to add columns and check my calculations.
But the social security numbers that start with zero, the leading zero is dropped. I check the file using Notepad and the leading zero is there. This makes it hard to find a number when my list shows 034123456 and Excel has 345123456.
How can I set Excel to leave the "Darn" thing alone? Can I set the default Template or something. I have had this problem before with other data and it is "VERY" annoying.

TIA

DougP

 

[AnotherHiggins]

Generally text fields (SSN is a text field because you aren't every going to do arithmetic with them and you want to include leading zeros) in .CSVs are denoted with double quotes (") around them.

But you probably don't want to go through every row in Notepad and add quotes....

So in Excel, in a new column, use a formula to change the format. Let's say your SSNs are in column A and you have a row of headers. So in some other empty column, type in [COLOR=blue white]=Text(A2,"000-00-0000")[/color] and drag/fill the formula down for all rows.

But you don't want to store this as a formula, so select that entire column and Copy. Select the column that contains the problem-SSNs (without leading zeros) and Paste Special > Values. This will convert the formulas to text values. Now delete the formula column.

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

 

[DougP]

But is there anything to make Excel open everything as TEXT? so I don't have to be watching and make formulas and ....

DougP

 

[AnotherHiggins]

-> I am creating a .CSV file using Access
I assume that this is coming from an Access query? have you right clicked on the SSN field, gone to Properties and told it TEXT?

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

 

[DougP]

Yes, the field is Text and I wrote a gob of VBA code to create this file. It does a myriad of things to gather the data from 4 or 5 tables.
As I mentioned above opening it in Notepad shows a zero.
Here is the partial .CSV file.
I changed the real SS#'s to mostly 9's for obvious reasons!

1,703,20080208,WEBPAY,000000000000359,0
2,ASBESTOS,039999999,REG,1,0,0,080051,AM,0,0,0,,
2,ASBESTOS,039999999,REG,1,10,0,080051,AM,0,0,1,,
2,OFFICE,049999999,REG,1,0,0,,,0,0,0,,
2,OFFICE,049999999,XXX,1,0.75,0,070103,EA3,0,0,1,,
2,OFFICE,049999999,REG,1,2.5,0,,,0,0,2,,
2,OFFICE,049999999,XXX,1,1.25,0,,,0,0,3,,

The issue is Excel not my code.

DougP

 

[SkipVought]

Do not OPEN the cvs file. Rather use Data > Import External Data > IMPORT DATA.... Macro record and you'll never have the problem again.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[xlbo]

The issue is not excel

You are creating a csV file

comma seperated VALUES

not comma seperated TEXT

Excel sees something that could be a value (ie looks like a number) and acts appropriately based on the file type

If you want the data to be kept as is when opening in excel (ie treated as text), you can do as Skip suggests or you could simply export the file as a .TXT file with commas as the delimeters

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

 

[DougP]

Thanks Skip
and xlbo for Clarifing the "V" for Values.

One note though as I tried the import and just clicked Next, next, next, at the bottom of the import wizard dialogs, it still dropped the leading zeros.
But the second time I did it I saw the field data type was General, so I changed it to Text for the SS# and another column.
Then it worked perfect.



DougP

 

[SkipVought]

"...I changed it to Text ..."

THAT'S the ticket!



Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}