Sql-server – Use BCP To Copy When Data Has Apostrophe In It

bcpsql serversql-server-2008-r2

Anytime my data has an apostrophe in it, the data is automatically split into the next field (which is skewing the result set). Let's say this is my DDL

Create Table OneTwoThree
(
    userid varchar(max)
    ,name varchar(max)
    ,data varchar(max)
    ,data1 varchar(max)
    ,data2 varchar(max)
)

Insert Into OneTwoThree Values
('bb12', 'Hi Blee', 'Drop It Like It''s Hot', '', 'Boop')

And this is the BCP command I am using

Select @sql = 'bcp "SELECT ''userid'',''name'',''data'',
''data1'',''data2'' 
UNION ALL SELECT CAST([userid] As VARCHAR(MAX)),
CAST([name] As VARCHAR(MAX)),CAST([data] As VARCHAR(MAX)),
CAST([data1] As VARCHAR(MAX)),
CAST([data2] As VARCHAR(MAX)) dbo.OneTwoThree" queryout "'
SET @sql = @sql + 'C:\BCPTest.csv' + '" -c -t, -T -S '+ @@servername
exec master..xp_cmdshell @sql

This syntax will place "Drop It Like It" in data and "s Hot" in data1 when the entire phrase should go in data and data1 should be blank.

How should I alter this procedure so that the hyphen does not cause improper splitting?

Best Answer

Presumably you are expecting the .csv file to be opened using Excel or some equivalent. I'd recommend wrapping the outputted text columns in double-quotes, such as:

declare @sql VARCHAR(1000);

Select @sql = 'bcp.exe "SELECT ''"userid"'',''"name"'',''"data"'',
''"data1"'',''"data2"'' 
UNION ALL 
SELECT CHAR(34) + CAST([userid] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([name] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data1] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data2] As VARCHAR(MAX)) + CHAR(34)
FROM dbo.OneTwoThree" queryout "'
SET @sql = @sql + 'C:\temp\BCPTest.csv' + '" -c -t, -T -S '+ @@servername
PRINT @sql;
exec master..xp_cmdshell @sql

The output will look similar to:

enter image description here

If the BCPTest.csv is then opened with Excel, the double-quotes will be automatically interpreted as indicating those columns are text columns, and Excel will not show the quote marks. Therefore, you only want to wrap text (CHAR, VARCHAR, NVARCHAR etc) columns in CHAR(34).

At that point, you'll start to see issues with columns that contain double-quotes; however you might decide to use the REPLACE() function to convert them into single-quotes or some other innocuous character.

Related Question