CSV file format export and leading zeroes.

Question

Post reply

CSV file format export and leading zeroes.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

If you want to see your leading zeros in Excel then you need to either:

1. Qualify the field with quotes (e.g. "0000123") when exporting to the csv

or as said before...

2. Put the single quote/apostrophe/tick before the 0000123 (e.g '0000123)

...so that Excel will recognise the data as text and not a number.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Adrian is correct,

Anything that looks like a numeric entry, will be treated like one by Excel. If you type 000123 into Excel, it will be changed to 123 by Excel unless you have some sort of textual prefix like a single (left justify) or double (right justify) quote or circumflex (center). Double quote may give fits in other places.

So, the data must mimic what you type... if you want to preserve leading zeroes, you just include something that will tell Excel to treat it like text.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Rather than using the LEN function to determine the number of times

to replicate your leading zeros, it might be simpler and easier to read

like this:

SELECT RIGHT('000000000' + CONVERT(VARCHAR, ), 9)

It kind of reads easier.

Todd Fifield

Ray White SSC Veteran Points: 278 More actions · Answer 4

I would like to retain a leading zero on decimal output.

IE I would like 0.4567 vs .4567; The only way I can think of doing this is to convert to varchar, but then it's no longer a number its text, and my text is surrounded by quotes. This is no biggie for a Phone Number, but for an exchange rate or and amount it is.

I am opening the file with a text editor notepad or textpad.

Any ideas?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Ray White (3/7/2008)
I would like to retain a leading zero on decimal output.
IE I would like 0.4567 vs .4567; The only way I can think of doing this is to convert to varchar, but then it's no longer a number its text, and my text is surrounded by quotes. This is no biggie for a Phone Number, but for an exchange rate or and amount it is.
I am opening the file with a text editor notepad or textpad.
Any ideas?

SELECT STR(.123,10,3)

Use BCP to export and you won't get the quotes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cdutre SSC Enthusiast Points: 148 More actions · Answer 6

I was asked to do this for ICD-9 diagnosis fields that may have leading or trailing zeros to be retained in CSV export from SSRS 2008 R2. Example is 090.10 for a diagnosis. I found that adding a carriage return to the expression in Visual Studio will preserve the zeros! Looks good in regular Excel/PDF output and print as well.

Fields!diag_field.Value + chr(13)