I am trying to export the contents a query into a ASCII file.
I need the character "LINE FEED" to be the row terminator.
Using bcp with the option -c I get characters "CARRIAGE RETURN"+"LINE FEED" as row terminator.


EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ltrim(rtrim(CONTENIDO)) FROM delphosSQL.dbo.TEMP_REST_XXXX" queryout G:\ESTUDIOS\DM\PEPE.TXT -c'

I tried with several combinations of -t and/or -r options but I did not get my goal.

Please any idea or trick !!!!

Thanks in advance

slash-r is for the RETURN of CrLf, very typical from unix/linux-sourced files: slash-n is CrLf, which you were already aware.
vbCrLf = CHAR(13) + CHAR(10) = \n
vbCr = CHAR(13) = \r

Lowell

---

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Thank you for the answer. But is there an option for just char(10) (Line Feed only) ?

Regards

Review the "BCP" entry in Books Online.
What you want is "-r /n" (without the quotes).

Hi PhilPacha:
Thank your for your answer.
However it does not work.
If I only use -r it does not copy anything and if I use -c -r I get again both control characters (Cr + Lf) as row terminator.

Kind Regards

Review the "BCP" entry in Books Online.

-c controls the output of data - in this case, "character."
-r controls the row delimiter

-c -r /n

Review the "BCP" entry in Books Online.
Everything you need is explained there.

did you review the link that you posted, or just pointed him there?

I reviewed it, and I did not see anything that answers the specific question: how do you specify LF (CHAR(10)) as the delimiter. I drilled down into the details for MSDN Specifying Field and Row Terminators.

I can't seem to find anything that says, for example, you could use -r 0x0A for the terminator, which is esentially what the original poster is asking.

after that, i'd give up and open the file in a text editor and replace {LF} with {CRLF} so i can use the standard slash-n delimiter

Lowell

---

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

My apologies, Lowell and Armando. It's been a while since I read the entire entry for BCP comand options.

I use BCP quite often, and have run across this before. Other than using a back-slash, rather than a forward slash, the -r option should produce only a line feed. -r \r produces a carriage return, and -r \r produces a carriage return / line feed combination.

If you find that I'm in error, please let me (and future reader) know.

Dear PhilPacha and Lowell:
Thank you for your answers.
I can confirm that:
1. using -c -r \r, I get only CR as row terminator
2. using -c -r , I get CR+LF as row terminator
3. if I do not use -c, extraction does not work.

Kind Regards

Armando

This is how I fixed it "-r 0x0A" (which is LF).