display leading zeros in CSV

Question

display leading zeros in CSV

pcq0125

SSCrazy

Points: 2890
More actions
February 12, 2007 at 1:25 pm

#69591

Hi,
On DTS SQL 2000 I have exported a file to CSV format with one of the fields that has leading in varchar datatype. however, I opened the CSV file I did not see leading zeros that I wanted it to be. On the DTS process Task preview it did show the leading zeros. Please advise how I can make my exported CSV file to show leading zeros? Thanks.
February 15, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

It's on going issue for everybody who transforms number string to csv format. But if you do not mind to have double quotes at the beginning and end of the number string, then I can suggest the following.

In your Transform Data Task Property in DTS, choose the option SQL query in the Source tab. Then add '"' before and after the column of the number string. See the example below.

From: select [strID],[data] from [TestDB].[dbo].[test]
To: select '"' + [strID] + '"' as [strID],[data] from [TestDB].[dbo].[test]

The result in the csv file would look like below:

strID data
"0000123" data1
"0000124" data2
"0000125" data3
"0000126" data4
"0000127" data5
"0000128" data6
"0000129" data7

But interestingly, if you open the file in Notepad it would show like below:

"strID","data"
"""0000123""","data1"
"""0000124""","data2"
"""0000125""","data3"
"""0000126""","data4"
"""0000127""","data5"
"""0000128""","data6"
"""0000129""","data7"

This means that actual output csv file contains all the leading zeros but Excel treated this column(strID) as number. Therefore this is a potential bug in Excel - perhaps Format Cells function???

pcq0125 SSCrazy Points: 2890 More actions · Answer 2

Hi Terry,

Thanks for your help. I don't think the "" is going to work for me. This is because my customer would like to see the numbers without the "". Any ideas if you can do without the """ and still showing the zeros. By the way the interger data has a conversion to varchar. Thanks.

Loner SSC-Insane Points: 21279 More actions · Answer 3

I just did one that required leading zeros on one of the numeric fields.

If the length of the CSV field is 15.

In the select statement

SELECT RIGHT(('000000000000000' + CAST(Field AS VARCHAR(15))), 15)

Hope it helps.

pcq0125 SSCrazy Points: 2890 More actions · Answer 4

Becacause my value varies somestime is 1 or 23, or 564 and it need varies leading zeros to have a fixed length of a varchar(5). How does this work. Please advise. Thanks.

Loner SSC-Insane Points: 21279 More actions · Answer 5

Can you give me an example?

You mean the value in the table can be 1, 23 or 456, but the fixed length of the CSV field is 5 ??

In my example, if the field is varchar(5) and the length in the CSV is 5

SELECT RIGHT(('00000' + CAST(Field AS VARCHAR(5))), 5)

RIGHT is

Returns the part of a character string starting a specified number of integer_expression characters from the right.

SO if the Field = 1

SELECT RIGHT(('00000' + CAST(Field AS VARCHAR(5))), 5) will have the value '00001'

if the Field = 456

Then the result is '00456'

pcq0125 SSCrazy Points: 2890 More actions · Answer 6

I apologized I did not make myself clear. MY values of the field can have 1 digit or 2 digit or 3 digits and up to 5 digits. the leading zeros will fill in the empty spaces if is the digits are leass than 5. For example,

2 will become 00002

25 will become 00025

378 will become 00378

8899 will become 08899

56678 will remain 56678

Any ideas ? Thanks.

Loner SSC-Insane Points: 21279 More actions · Answer 7

Then my query

SELECT RIGHT(('00000' + CAST(Field AS VARCHAR(5))), 5) will give you waht you want, just try it.

Let me know.

pcq0125 SSCrazy Points: 2890 More actions · Answer 8

pcq0125

SSCrazy

Points: 2890

February 16, 2007 at 2:56 pm

#690340

Thanks, I will try it and let you know.

Deborah Atkinson Valued Member Points: 56 More actions · Answer 9

I have this same issue in exporting zip codes to a .csv file. I need the first 5 from the left, including the 0. This code works beautifully in the query analyzer, but when it actually does the export to .csv, the leading zeros still don't show up.

pcq0125 SSCrazy Points: 2890 More actions · Answer 10

unless you open from wordpad you will not see the zeros. This is a automatic default from Microsoft. You might want to check if there is an option in the csv file to remove this default option.

Loner SSC-Insane Points: 21279 More actions · Answer 11

It is problem of excel. When it sees a numeric fields, it will automatically delete the leading zeros. The only way is before loading into the sheet, select the column that will hold the field, right click and select format cell, change it to 'text'. Then when you load the data into the excel spreadsheet, it will retain the leading zeros.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 12

That's what the "" is supposed to do when importing into Excel... it's one of the default settings. Tell your client the "" is absolutely necessary.

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

Deborah Atkinson Valued Member Points: 56 More actions · Answer 13

If the problem is with Excel, I will just not worry if I preview the file in Excel. My process is DTS to a .csv and then automatically scheduling the FTP of the .csv to the website. So, even though I don't see the zeros, in my case it seems to work now.

strID	data
"0000123"	data1
"0000124"	data2
"0000125"	data3
"0000126"	data4
"0000127"	data5
"0000128"	data6
"0000129"	data7