I'm using BCP to download data from SQL Server, using queryout option.
However, I notice that if the data content in any columns contain '\n', the content exported from BCP will be treated as newline.
For example, if the data in SQL Server is:
COLUMN_1 COLUMN_2
AAA NAME\nSURNAME
BBB NAMESURNAME
The exported file be like:
AAA NAME
SURNAME
BBB NAMESURNAME
Refer to BCP document, as I understand, the -c should not treat \n as newline.
-c Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.
I'm not sure what I misunderstood.
Here is the command I use:
bcp "select [col_name] from [table_name] where [condition]" queryout test.dat -U[username] -P[password] -S[serverip.port] -c
Thank you.
If your data contains newline or crlf control characters then those characters WILL, naturally, be included in the data that is copied out.
Are the control characters supposed to be there? If so, then leave them and they will be imported into whatever your destination is. Just because your text view shows a "broken" line, does not mean that SQL Server cannot ingest that line again and keep the control character tucked into the data (again, if those control characters belong there... i've seen plenty of cases where they would be).
If the newline character "\n" (or any control character for that matter) is NOT desired, then it's just a matter of doing as you commented in Martin's answer. Just clean the data either before you query it ("update") or during query (as you commented with "select/replace") or after you've copied the data out.
I've used "file cleaner" applications in the past to "clean" a file of unwanted characters (this can be an issue with long-lived data or data that has traversed various platforms or has been touched by humans!!! Yuck!).