[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

do not strip leading zero from sql to excel using dts

Posted on 2008-11-13
8
Medium Priority
?
638 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:tomasdlv
  • 3
  • 3
  • 2
8 Comments
 
LVL 8

Expert Comment

by:i2mental
ID: 22951011
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
 
LVL 5

Expert Comment

by:volking
ID: 22951024
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
 
LVL 5

Expert Comment

by:volking
ID: 22951040
P.S. four single-quotes are translated by SQL into a single single-quote ...
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 8

Expert Comment

by:i2mental
ID: 22951048
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
 

Author Comment

by:tomasdlv
ID: 22951081
ok let me try this i'll get back to you in a few minutes
0
 

Author Comment

by:tomasdlv
ID: 22951222
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
 
LVL 5

Accepted Solution

by:
volking earned 2000 total points
ID: 22951452
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
 

Author Comment

by:tomasdlv
ID: 22951490
excel 2003 sp3 v.11
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question