bcp with Quote Text Qualifier and Comma Delimiter

Question

Post reply

bcp with Quote Text Qualifier and Comma Delimiter

rjjh78

SSCrazy

Points: 2787
More actions
September 19, 2018 at 12:20 pm

#363712

Hello,
I want to export data to an CSV using a comma delimiter. However, I have a field with Last Name FirstName (e.g. Doe, John) which is taking the comma and creating a new column. The following code sample works, but is using a Pipe as the Text Qualifer. Is there a way to make this work with a comma instead of a pipe?

CREATE
TABLE ##MyData (ID int identity(1,1), Name varchar(100), FavColor varchar(100))

INSERT INTO ##MyData (Name, FavColor) VALUES ('Doe,John', 'Blue')

INSERT INTO ##MyData (Name, FavColor) VALUES ('Doe,Jane', 'Red')

-- SELECT * FROM ##MyData
DECLARE
@CMD varchar(8000)

SELECT @CMD = 'bcp "SELECT * FROM ##MyData" queryout "c:\\temp\myfile.csv" -c -t "|" -T'

EXEC master..xp_cmdshell @CMD

DROP TABLE ##MyData

Also, is there a better practice to do CSV exports (e.g. SSIS, OPENQUERY, etc.?)
Many thanks in advance!

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

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

ScottPletcher SSC Guru Points: 101093 More actions · Answer 1

Use:
-t ","

instead of:
-t "|"

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rjjh78 SSCrazy Points: 2787 More actions · Answer 2

Hi, Thank you for your reply!

When I do this, the First and Last Name separate into 2 columns better the comma. I think what I need to understand is how can I add a Text Qualifier of double quote to the bcp string???

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

There's a few options, one and this might be easiest is just to wrap all your fields in " in the query or just stripping the commas out of the offending fields in your query. There's also some tricks you can do with a format file in bcp that will work as would just using SSIS which will let you specify quote qualified fields.

Joe Torre SSChampion Points: 10248 More actions · Answer 4

Powershell is a good option as well for importing and exporting CSV files to and from SQL Server tables. If i have a weird file to import I often create a staging table with one wide column and optionally an id int identity and use whatever means to get the rows in the table, then parse it in T-SQL.