Handling null values when bcp .csv file export

2019-09-15 23:40发布

问题:

DECLARE @CMD varchar(4000),
        @DelCMD varchar(4000),
        @HEADERCMD varchar(4000),
        @Combine varchar(4000),
        @Path varchar(4000),
        @COLUMNS varchar(4000)

-- Set values as appropriate
SET @COLUMNS = ''
SET @Path = '\\servername\share\outputpath'

-- Set up the external commands and queries we'll use through xp_cmdshell
-- Note that they won't execute until we populate the temp tables they refer to
SET @CMD = 'bcp "select * from ##OutputTable" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"'
SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"'

-- Create and populate our temp table with the query results
SELECT
  * INTO ##OutputTable
FROM YourSourceTable

-- Generate a list of columns   
SELECT
  @COLUMNS = @COLUMNS + c.name + ','
FROM tempdb..syscolumns c
JOIN tempdb..sysobjects t
  ON c.id = t.id
WHERE t.name LIKE '##OutputTable%'
ORDER BY colid

SELECT
  @COLUMNS AS Cols INTO ##Cols

-- Run the two export queries - first for the header, then for the data
EXEC xp_cmdshell @HEADERCMD
EXEC xp_cmdshell @CMD

-- Combine the two files into a single file
EXEC xp_cmdshell @Combine

-- Clean up the two temp files we created
EXEC xp_cmdshell @DelCMD

-- Clean up our temp tables
DROP TABLE ##cols
DROP TABLE ##OutputTable

I used this script to export .csv file to my path. But however it works fine for non-null values. If the result set contains any null values the formatting crashed . How can we handle null values in bcp. Kindly advise