bcp row terminator

Question

bcp row terminator

armando.horcajo

SSChasing Mays

Points: 626
More actions
May 11, 2011 at 5:10 pm

#97941

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

armando.horcajo SSChasing Mays Points: 626 More actions · Answer 2

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

Regards

PhilPacha Hall of Fame Points: 3645 More actions · Answer 3

Review the "BCP" entry in Books Online.

What you want is "-r /n" (without the quotes).

armando.horcajo SSChasing Mays Points: 626 More actions · Answer 4

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

PhilPacha Hall of Fame Points: 3645 More actions · Answer 5

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.

Lowell SSC Guru Points: 323518 More actions · Answer 6

PhilPacha (5/17/2011)
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!

PhilPacha Hall of Fame Points: 3645 More actions · Answer 7

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.

armando.horcajo SSChasing Mays Points: 626 More actions · Answer 8

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

AlexVM SSC Journeyman Points: 95 More actions · Answer 9

AlexVM

SSC Journeyman

Points: 95

October 22, 2014 at 12:27 pm

#1754153

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

m.t.cleary SSC Veteran Points: 200 More actions · Answer 10

https://technet.microsoft.com/en-us/library/ms191485(v=sql.105).aspx

When you use bcp interactively and specify (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r.

Using -r "0x0A" appears to fool BCP into not doing this.

jesse.macnett SSC Enthusiast Points: 185 More actions · Answer 11

finally cracked this earlier today...variation on what i've seem some folk using, but it worked. Note: you gotta do this from powershell, vs a normal command prompt:

-r ([char]0x0A)

Gives you straight-up LFs, no CRs. So there that is.

soleas SSC Rookie Points: 29 More actions · Answer 12

I had a file that was from a UNIX environment having only the LF character and was comma delimited that I needed to use the 'bcp' command to load into my SQL Server database. Using the information here was helpful but I thought I'd share the exact syntax I used that made it work specifically for getting a LF only file in. Specifically I used the command -r0x0A concatenated with the LF character in hex.

EXECUTE master.dbo.xp_cmdshell 'bcp myDB.dbo.myTable in "C:\myFile.txt" -c -t, -T -r0x0A -SmyServer'