do not strip leading zero from sql to excel using dts

i am running a dts package that it's destination is excel however i have a field in sql that has leading zeroes that when it reaches excel they are stripped...how do i configure dts or sql to leave in these zeroes?

thanks
tomasdlvAsked:
Who is Participating?
 
volkingConnect With a Mentor Commented:
hmmmm ... what version of Excel are you targeting?
You might simply try casting to a varchar() without the single quote, depending on the version of Excel, it MIGHT recognize it as a TEXT column anyways.
0
 
i2mentalCommented:
Stripping the leading zeros happens when you're dealing with that number as an integer or float or basically anything but a "text" fields (varchar, nvarchar, etc). Make sure that through the entire dts stream, you're keeping it as a varchar. DTS will try to make assumptions to the data type when placing it in excel. It will take a sampling of the first x number of rows and determine that should be a number and it then would likely strip out those zeros.  I don't have DTS installed on my computer now to tell you how to override that but it's in your mappings going to excel.
0
 
volkingCommented:
Problem is probably from Excel side. It automatically formats a column as a numeric value (thus stripping the leading zero's) from anything that can be converted to a number.

Try, casting the outgoing field as Varchar(x) and prefix with a single quote. The single quote forces Excel to make the receiving column a TEXT value (thus retaining zeros)

CAST( '''' + MyField as varchar(20)) as MyField2

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
volkingCommented:
P.S. four single-quotes are translated by SQL into a single single-quote ...
0
 
i2mentalCommented:
You can also change your export destination to a .txt file. Then open it up with excel and it will go into the Import Data routine and you can specify that the column should be text.
0
 
tomasdlvAuthor Commented:
ok let me try this i'll get back to you in a few minutes
0
 
tomasdlvAuthor Commented:
this is working great...i had one question though it now populates excel with a single quote in the begining and then the number which was exactly what was coded. is there a way though that we can have that single qoute but have excel display just the number..the people i'm sending this to are not so bright.
0
 
tomasdlvAuthor Commented:
excel 2003 sp3 v.11
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.