May 11, 2011 at 5:10 pm
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
May 11, 2011 at 6:03 pm
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
May 12, 2011 at 11:33 am
Thank you for the answer. But is there an option for just char(10) (Line Feed only) ?
Regards
May 16, 2011 at 2:48 pm
Review the "BCP" entry in Books Online.
What you want is "-r /n" (without the quotes).
May 17, 2011 at 12:02 pm
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
May 17, 2011 at 12:18 pm
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.
May 17, 2011 at 12:52 pm
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
May 17, 2011 at 3:33 pm
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.
May 18, 2011 at 1:08 pm
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
October 22, 2014 at 12:27 pm
This is how I fixed it "-r 0x0A" (which is LF).
May 20, 2015 at 10:02 pm
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.
April 4, 2017 at 8:17 pm
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.
December 10, 2017 at 9:06 pm
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'
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy