Search code examples
sql-serverbcp

SQL Server BCP Export where comma in SQL field

I have successfully exported a file to a CSV. I used the following code to export the file: 

exec xp_cmdshell 'bcp "[DC_TrainEnvironment].[dbo].[HAFacilities_Master]" out "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master.csv" -f "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_FORMAT.fmt" -o "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_LOG.txt" -T -S "HAPDBCDC02,2112"'

This works well, but there is a problem. There are certain fields that have commas within them. I am using this format file:

enter image description here

What can I do in the format file to avoid having to change the entire format file? Is there a quick way to indicate a field terminator will not end with the comma in those certain fields? I.e. "','" or "'',''"?

Appreciate any help. Thanks

Solution

  • If a field you are exporting will can contain the character you are using to delimit the fields in your file, your choices are:

    1. Use a different delimiter - this seems the easiest way go to. Just use "|" or "~" as your delimiter. Do a global replace of "," to "|" in the format file with just about any text editor. It's not clear why it would be difficult to modify "the whole" file. Maybe you have a recipient of the file who requires comma-delimited?

    2. If you must use commas as the delimiter, you must change your column delimiter from comma (,) to quote-comma-quote (","). To do this, you need to use the escape character to get the BCP program to ignore the quotes you want to use as delimiters in the output file so it does not think of them as the quotes it understands to contain the delimiter in the format file. So...

    Instead of ","... use... "\",\""

    This will result in the following

    col1,col2,"col,3",col4

    For col1 the delimiter is: , to represent this in format file use: ","

    For col2 the delimiter is: ," to represent this in format file use: ",\""

    For col3 the delimiter is: ", to represent this in format file use: "\","

    For col4 the delimiter is: , to represent this in format file use: ","

    I hope that helps.