2

How can I ignore null's in my exported .csv file, I used bcp utility within sql server, here it is how this looks like altogether.

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)'

After exporting and opening my file in notepad++ empty columns end up filled with NULL's.

enter image description here

What I want it to look like is...

enter image description here

Edit:

I included ISNULL() in my select clause but still notepad will read those columns as null valued.

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
unknown
  • 461
  • 9
  • 23

1 Answers1

1

SELECT @sql = 'bcp "select * from ' + DB_NAME() + '.dbo.' + @table + ' where reportingdate = ''' + @date + '''"' + ' queryout ' + @output + ' -c -C65001 -t";" -r"\n" -T -S' + @server

Instead of SELECT * write the specific column names.

Wherever you feel NULL can appear you can write like following.

....   
SELECT 
Column1,
ISNULL(Column2,'') AS Column2,
Column3,
....
 from ' + DB_NAME() + '.dbo.' + @table 
.....

Note: If you have INT as datatype, you need to slightly change your select according to following query.

DECLARE @INTVAL INT
SET @INTVAL = NULL
SELECT
ISNULL(LTRIM(NULLIF(@INTVAL, 0)), '') AS ColumnName
PSK
  • 17,547
  • 5
  • 32
  • 43
  • Yes, this must be the only way, tried to avoid it as I have too much of those null columns. – unknown Feb 05 '18 at 11:05
  • If you don't handle it in select, by default SQL will return NULL – PSK Feb 05 '18 at 11:06
  • I have a problem. SELECT @sql = 'bcp "select ReportingDate,uniqClientID,registrationNumber,name,ISNULL(vatNumber,'') as vatNumber ... I get some syntax errors, unclosed quotations,but when I remove ISNULL(vatNumber,'') as vatNumber from the select clause it will work just fine. – unknown Feb 05 '18 at 11:26
  • Can you update your query in the question itself as Edit. It will help to resolve. – PSK Feb 05 '18 at 11:27
  • You can take a look. – unknown Feb 05 '18 at 11:32
  • They have to be of the same type, I can't replace column which stored int data type with an empty string. – unknown Feb 05 '18 at 11:50
  • For int use SELECT ISNULL(LTRIM(NULLIF(0, 0)), '') AS ColumnName – PSK Feb 05 '18 at 11:52
  • Unfortunately when I open it with notepad NULL's are still there. – unknown Feb 05 '18 at 12:03
  • @T_Dejan INSULL works. Your script though uses escaped queries inside escaped queries which means you probably don't know *what* is executed in the end. Forget about NUL, think about SQL injection attacks and someone running code with our credentials on the server because you disabled security to run `xp_cmdshell` – Panagiotis Kanavos Feb 05 '18 at 12:30
  • @T_Dejan try a *simple* example first. Run bcp from the command line wiht a simple query and ISNULL, no string concatenations. Once you get it working you can think about changing the query – Panagiotis Kanavos Feb 05 '18 at 12:34
  • Okay I got it working with NULLIF, what do you suggest on enhancing security. – unknown Feb 05 '18 at 12:51