Post reply

sqlcmd export tables with triple pipe

  • March 31, 2016 at 7:50 am

    #312367

    Hi - if i've got this in the wrong forum please move it to the appropriate one

    What I'm trying to do should I hope be relatively simple and while i can see similar questions have been asked most seem to relate to tab delimiters

    I'm not great with SSIS which is probably what I should use

    Am trying to export approx 25 tables into csvs files (some are very large)

    This process will need to be replicated several times for different variations on the project

    I'm trying to use sqlcmd to achieve this

    Originally I comma separated the fields but a problem has arisen with one of the fields which has freetext including pipes, commas and quotes

    So, to cater for this i'm trying to put in an unusual and long separator such as triple pipe

    This is the basic structure of the sqlcmd for all the tables:

    sqlcmd -S ServerName -d DBName -E -Q "SET NOCOUNT ON SELECT * FROM tablename" -o "output.csv" -W -w 1024 -s "|||"

    This however outputs a single pipe in the output and whatever i use only takes the first character i put after the -s switch

    Is there something really obvious i'm doing wrong before I spend even more time staring at this

    Thanks in advance

  • March 31, 2016 at 8:19 am

    #1868861

    The column separator can only be a single character.

    https://msdn.microsoft.com/en-us/library/ms162773(v=sql.110).aspx

    Could you use BCP instead?

  • March 31, 2016 at 10:34 am

    #1868952

    Thanks

    I had a feeling that might be the case

    Does bcp support separators of more than 1 character?

  • March 31, 2016 at 11:24 am

    #1868964

    yes it does.

    i export html from a database, via bcp, and need to use characters that are not going to appear in the data, becuase the classic terminators like CrLf, commas, quotes, etc will throw off the export.

    here's my classic example of exporting it out and in again:

    --#################################################################################################

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

    --flags explanation:

    -- -c = character data

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

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

    -- -T' = Trusted connection

    --#################################################################################################

    --#################################################################################################

    --bcp 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'

    --#################################################################################################

    --bcp 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 31, 2016 at 12:09 pm

    #1868974

    Thanks all

    Looks like if i redo it with bcp i can get what i need

    Will probably take a bit of time to get it to do what i want and I remember now part of the reason i went with sqlcmd was for the headers - think i'll have to keep the headers in another folder with a manual fix to put in the new separators

    Thanks again

Viewing 5 posts - 1 through 4 (of 4 total)

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