March 15, 2016 at 11:47 am
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
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
March 16, 2016 at 5:17 am
Does it matter where I put the -q?
March 16, 2016 at 5:20 am
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
March 16, 2016 at 5:33 am
Thanx.
March 16, 2016 at 5:50 am
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
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
March 16, 2016 at 6:07 am
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
March 16, 2016 at 6:13 am
Ok. Thanx. I got it working.
March 16, 2016 at 6:16 am
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