new line in bcp

I am exporting sql server table data to excel using bcp... But if any field
contain carrige return , that fields break up into to next line. Is their
any solution to itOne other way might be to create an ODBC connection to Excel and export
through this data source
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Vikram" <aa@.aa> wrote in message
news:ew#myvQPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> I am exporting sql server table data to excel using bcp... But if any
field
> contain carrige return , that fields break up into to next line. Is their
> any solution to it
>|||If you do not want the carriage returns in the data, create a view
that returns all the columns of the table but removes the carriage
returns from the particular column(s) using REPLACE(). Then BCP out
from the view, instead of from the table.
If you must preserve the carriage returns in the data, don't use BCP.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:

>I am exporting sql server table data to excel using bcp... But if any field
>contain carrige return , that fields break up into to next line. Is their
>any solution to it|||But I cant use views as I am calling stored procedure from bcp... And stored
procedure return data from temp tables...
Is there is no way by which bcp can preserve carrige return in the data.
Because when we use DTS, it export the data perfectly. I even cannot use DTS
as sp return data from temp table...
Any suggestion will be helpful?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:uubc02t9b70m4qtavu7cohq4vmuvi1oqlr@.
4ax.com...
> If you do not want the carriage returns in the data, create a view
> that returns all the columns of the table but removes the carriage
> returns from the particular column(s) using REPLACE(). Then BCP out
> from the view, instead of from the table.
> If you must preserve the carriage returns in the data, don't use BCP.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:
>
field|||Vikram (aa@.aa) writes:
> But I cant use views as I am calling stored procedure from bcp... And
> stored procedure return data from temp tables...
You using the queryout option? Anyway, if you are already using temp
tables, you have all possibilities to modify the data.

> Is there is no way by which bcp can preserve carrige return in the data.
I guess the question is not how get BCP to preserve the CR in the data -
it bulks out whatever that is, but how to a file should look like for
Excel to accept it with the newlines preserved. I don't know Excel well
enough to say how the file should look like.
Once you are equipped with that knowledge, you can address this by either
formatting the data when you select it, or use a format file, to have BCP
to do it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx