bcp row terminator

  • armando.horcajo

    SSChasing Mays

    Points: 626

    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

  • Lowell

    SSC Guru

    Points: 323349

    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

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

    Regards

  • PhilPacha

    Hall of Fame

    Points: 3525

    Review the "BCP" entry in Books Online.

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

  • armando.horcajo

    SSChasing Mays

    Points: 626

    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: 3525

    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: 323349

    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: 3525

    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

    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

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

  • m.t.cleary

    SSC Veteran

    Points: 200

    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

    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

    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