bcp to export sql table to excel, how avoid suppress of leading ze

[pensio]

I'm using bcp command to export a sql server table to an excel sheet. I've
created a bcp format file to use with the command. It looks like this :

9.0
5
1 SQLCHAR 0 20 "\t" 1 ArticleNumber
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\t" 2 Description
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 6 "\t" 3 InvoiceNumber
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 19 "\t" 4 SalesPrice
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 19 "\r" 5 NumberDelivered
SQL_Latin1_General_CP1_CI_AS

to execute the command in T-sql :
declare @sql varchar(500)
select @sql = 'bcp dataBaseX.dbo.FileToExport out c:\ExcelSheet.xls -f
c:\bcp.fmt -T'
exec master..xp_cmdshell @sql


The export executes without errors. Problem is that ArticleNumber field in
the sql table is a varchar field that most of the time contains only numbers
and often starts with a zero.
If I open the excel sheet, then the leading zeros are suppressed.
The fields in the first record of the sql table contain the columnheaders
('ArticleNumber', 'Description' etc).
I hoped to mislead excel this way to see aspecially the ArticleNumber field
as a text field, but to no avail.

It is important that I see the leading zeros in the excel sheet when opening
the document, without first having to change the properties of the column.
How do I solve this ?

versions : SQL Server 2005, Office 2000


Thanks for any advice.

[matteus]

Hi pensio, just some thoughts...
I know 2 ways to view leading zeros in excel: first creating
personalized cell formatting like "00...0" with as many 0 as are the
numbers in the fields. typically you can use this with omogeneous
data. Second you can attach an ' before the number. But importing it
with such a leading ', the number will be shown as '12345 until you
edit the cell and validate the value.

Have you tried to use an already formatted and empty Excel file?

But because you want the numbers to be shown with leading zeros when
opening and without any forced formatting and of course, because of
we're writing on a sqlserver.dts NG i would suggest to either attach
at the beginning of the field some dummy char such as a space,
underscore, apostroph or build a simple pkg to create the file as you
like ;)

Ciao,
M.

Post by pensio
I'm using bcp command to export a sql server table to an excel sheet. I've
9.0
5
1 SQLCHAR 0 20 "\t" 1 ArticleNumber
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\t" 2 Description
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 6 "\t" 3 InvoiceNumber
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 19 "\t" 4 SalesPrice
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 19 "\r" 5 NumberDelivered
SQL_Latin1_General_CP1_CI_AS
c:\bcp.fmt -T'
The export executes without errors. Problem is that ArticleNumber field in
the sql table is a varchar field that most of the time contains only numbers
and often starts with a zero.
If I open the excel sheet, then the leading zeros are suppressed.
The fields in the first record of the sql table contain the columnheaders
('ArticleNumber', 'Description' etc).
I hoped to mislead excel this way to see aspecially the ArticleNumber field
as a text field, but to no avail.
It is important that I see the leading zeros in the excel sheet when opening
the document, without first having to change the properties of the column.
How do I solve this ?
versions : SQL Server 2005, Office 2000
Thanks for any advice.

[Mark Han[MSFT]]

Hi Pensio,

This is Mark, a SQL Server Engineer. I'm glas to assist you with the issue.

Based on the requirement, please try the following action
1 Please set the column ArticleNumber as TEXT data type in SQL Server.

2 Creating a non-XML format file for Unicode native data.
There is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms191516.aspx

bcp dataBaseX.dbo.FileToExport nul -T -N -f c:\bcp.fmt

3 run the command
declare @sql varchar(500)
select @sql = 'bcp dataBaseX.dbo.FileToExport out c:\ExcelSheet.xls -f
c:\bcp.fmt -T'
exec master..xp_cmdshell @sql

Then the leading zeros will be seen in the excel sheet.

Besides, since the bcp is used to export data out of tables into data
files and the column name is not considered as data, the column name() will
not be copied from SQL Server into data file.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[pensio]

Thanks all for your replies, I tried your suggestions, but the problem
persists.
Because I had to find a solution real fast, I just added an underscore at
the end of the ArticleNumber field contents, so Excel automatically detects
the column as a Text column.
Should you have more ideas, I'm still interested !

Post by pensio
I'm using bcp command to export a sql server table to an excel sheet. I've
9.0
5
1 SQLCHAR 0 20 "\t" 1 ArticleNumber
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\t" 2 Description
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 6 "\t" 3 InvoiceNumber
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 19 "\t" 4 SalesPrice
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 19 "\r" 5 NumberDelivered
SQL_Latin1_General_CP1_CI_AS
c:\bcp.fmt -T'
The export executes without errors. Problem is that ArticleNumber field in
the sql table is a varchar field that most of the time contains only numbers
and often starts with a zero.
If I open the excel sheet, then the leading zeros are suppressed.
The fields in the first record of the sql table contain the columnheaders
('ArticleNumber', 'Description' etc).
I hoped to mislead excel this way to see aspecially the ArticleNumber field
as a text field, but to no avail.
It is important that I see the leading zeros in the excel sheet when opening
the document, without first having to change the properties of the column.
How do I solve this ?
versions : SQL Server 2005, Office 2000
Thanks for any advice.

[Mark Han[MSFT]]

Hi Pensio,

Thank you for the update. I'm glad the issue is resolved by yourslef. I'm
so proud of you.

Since the column in the EXcel is able to be changed from number type to
text type by ' , Copying data from a query to the excel sheet could be a
solution also.

For example:
bcp "select ''' +'ArticleNumber ' from table_name" queryout
c:\ExcelSheet.xls -f c:\bcp.fmt -T

There is an article to share with you:
bcp Utility:http://msdn.microsoft.com/en-us/library/ms162802.aspx

Hope the above is helpful. Thanks

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================