title: Ignore null's in exported .csv file, bcp utility
body: <blockquote>
    <p><strong>How can I ignore null's in my exported .csv file</strong>, I used bcp utility
    within sql server, here it is how this looks like altogether.</p>
  </blockquote>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF OBJECT_ID ('[dbo].[generateCSV]') IS NOT NULL
  DROP PROCEDURE [dbo].[generateCSV]
  GO
  
    CREATE PROCEDURE [dbo].[generateCSV]
  
  (
   @table varchar(100),
   @output varchar(100), 
   @date varchar(12),
   @server varchar(30)
  )
  
  AS
  
  DECLARE @sql varchar(8000)
  
  SELECT @sql = 'bcp "select * from ' + DB_NAME() + '.dbo.' + @table  + ' 
  where reportingdate = ''' + @date + '''"' + ' queryout ' +  @output + '  -c 
  -C65001 -t";" -r"\n" -T -S' + @server 
  
   exec master..xp_cmdshell @sql 
  
  
  
  
         -- Main EXEC
  
  EXEC dbo.generateCSV @table = 'Clients', @date = '2017-10-31', @output = 
  '//172.18.16.109/share/Test.csv (server with export target location ) ', 
  @server = '172.18.16.108(server we are connected to and from which we are 
  taking the data)' 
  </code></pre>
  
  <p>After exporting and opening my file in notepad++ empty columns end up filled with NULL's.</p>
  
  <p><a href="https://i.stack.imgur.com/RLjeq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RLjeq.png" alt="enter image description here"></a></p>
  
  <p>What I want it to look like is...</p>
  
  <p><a href="https://i.stack.imgur.com/2Ppwn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2Ppwn.png" alt="enter image description here"></a></p>
  
  <hr>
  
  <p><strong>Edit:</strong></p>
  
  <p>I included <em>ISNULL()</em> in my select clause but still notepad will read those columns as null valued.</p>
  
  <pre><code>SELECT @sql = 'bcp "select 
  ReportingDate,uniqClientID,registrationNumber,name,ISNULL(vatNumber,'''') as 
  vatNumber,ISNULL(entityStatusCode,'''') as entityStatusCode, 
  maximumLifetimeDPD from ' + DB_NAME() + '.dbo.' + 
  @table  + ' where ReportingDate = ''' + @date + '''"' + ' queryout ' +  
  @output + '  -c -C65001 -t";" -r"\n" -T -S' + @server 
  </code></pre>