Post reply

How do I add a text qualifier to a bcp command

  • March 15, 2016 at 11:47 am

    #399866

    I need to export the results of a stored procedure in a pipe delimited, text qualified .txt file. I have everything but the text qualifier. Any thoughts?

    DECLARE @cmd VARCHAR(2048)

    SET @cmd = 'bcp "EXEC SigHealth..spWellPartnerDownload" queryout'

    +' "\\sh-sftp\data$\Wellpartner\Wellpartner_visits.txt" -c -T -t^|'

    +' -SLOCALHOST" '

    print @cmd

    EXEC master..xp_cmdshell @cmd, NO_OUTPUT

  • March 15, 2016 at 1:00 pm

    #1865418

    here's an example of a four character column delimiter(the -t) and a four character row delimiter(-r).

    just put your pipe in dbl quotes.

    i think if you just add the minus q switch for quoted identifiers , you get dbl quotes on data as well.

    -q

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T -q'

    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!

  • March 16, 2016 at 5:17 am

    #1865556

    Does it matter where I put the -q?

  • March 16, 2016 at 5:20 am

    #1865558

    the order of the flags shouldn't make any difference, but i think if you run bcp /?, and put flags in the same order as the help specifies, that would be the best practice.

    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!

  • March 16, 2016 at 5:33 am

    #1865560

    Thanx.

  • March 16, 2016 at 5:50 am

    #1865563

    Have you ever seen the -q work on data? By reading the BCP help, it sounds like you only use the -q when there are quotes or commas in the name of the database.

  • March 16, 2016 at 6:04 am

    #1865565

    ok, bcp IN supports the quoted identifier, but not when you use bcp out/queryout;

    it's been a while since i did bcp, i switched to Common Language Runtime to do my exports a while ago, and this was one of those specific reasons why.

    this has an example on changing the column and row delimiters, but you end up missing the first dbl quote, and the last dblquote has na extra:

    http://stackoverflow.com/questions/2061113/sql-server-bcp-how-to-put-quotes-around-all-fields

    SSIS can do it correctly, as well as a CLR like i mentioned.

    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!

  • March 16, 2016 at 6:07 am

    #1865568

    my first example, where i used delimiters that never occur in my data is a solid example of exporting and importing data with whitespace and dbl quotes int he contents, without the headache of escaping dbl quotes and all that.

    --using a super special 4 char row delimiter to be sure it doesn't exist in the data

    --flags explanation:

    -- -c = charcater data

    -- -t"[||]" = field terminator

    -- -r"[~~]" = row terminator

    -- -T' = Trusted connection

    --out

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

    --in

    EXECUTE master.dbo.xp_cmdshell 'bcp BFONRA.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

    --in via bulk insert

    BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = '[||]',

    ROWTERMINATOR = '[~~]',

    FIRSTROW = 1

    )

    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!

  • March 16, 2016 at 6:13 am

    #1865569

    Ok. Thanx. I got it working.

  • March 16, 2016 at 6:16 am

    #1865571

    I used QUOTENAME for all the fields.

Viewing 10 posts - 1 through 9 (of 9 total)

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