locked
BCP when a NTEXT column contains carriage returns RRS feed

  • Question

  • Hello, I am trying to use BCP to export a table to a flat, delimited file then import the data to an identical table on another server.  The table contains email data.  The body is in an NTEXT column.  Emails contain newline and carriage return characters and those are throwing off my format file.

    Can anyone give me any pointers on how to code a BCP format file to handle a column that can contain newline / carriage returns?  I am using a non-XML format file but will use XML if that would make this work...

    thanks!

    Wednesday, June 13, 2012 5:36 PM

Answers

All replies

  • Best way is to replace carriage return with come character. You should replace the char(10) and char(13) with something like <CarriageReturn>. On the other side replace <CarriageReturn> with new line.

    Also check this link for possible solution for your problem: http://www.sql-server-performance.com/forum/threads/bcp-problem-with-carriage-return.22271/


    • Edited by irusul Thursday, June 14, 2012 1:40 PM
    • Proposed as answer by Iric Wen Monday, June 18, 2012 9:05 AM
    • Marked as answer by Iric Wen Thursday, June 21, 2012 8:53 AM
    Thursday, June 14, 2012 1:39 PM
  • Realizing this is old, but I started using "«" (Alt+0171) and "»" (Alt+0187) as row and field terminators, respectively, as a work-around for this issue. These are characters that I know do not appear in the data.

    The other alternative, if both servers are SQL Server, is to simply use native format "-n" instead of delimited files. the native format option also eliminates the need for format files in most cases.

    • Proposed as answer by steven.buehler Wednesday, September 7, 2016 4:49 PM
    Wednesday, September 7, 2016 4:48 PM
  • The other alternative, if both servers are SQL Server, is to simply use native format "-n" instead of delimited files. the native format option also eliminates the need for format files in most cases.

    I thought so for a long time, but these days I always use format files with native format. If collations are different it does matter. They are very simple to generate with the format option. ("format" instead of "in" or "out".)

    Wednesday, September 7, 2016 9:19 PM