How to write the results of a script to a CSV? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to write the results of a script to a CSV?

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-05-16 : 13:17:22

Hi All,

I am running a query which return some results. It includes numeric type fields that may have NULL values.
In the Managment Studio when I run and finish the query I right click on the result grid (and highlight all records ) and click "Save result as" to save the results as CSV . When I look at the CSV records, the numeric NULL values actually say NULL in the CSV. Shouldn't it be blank instead of NULL?

I want it to be blank.
Please help.

Thanks,

Zee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 13:20:22

A blank is not the same thing as NULL. Modify your query to use COALESCE on the columns with possible NULL data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-05-16 : 13:49:27

tkizer,

Can you give some code.

Let say I have a table called ProductPrice with the following four fields and type;
ProductID (nvarchar),ProductName, Price(decimal 18,2), Cost(decimal 18,2)

Let say the records are as follows (seperated by comma)
ProductID (nvarchar),ProductName, Price(decimal 18,2), Cost(decimal 18,2)
001,Chips,2.50,2.20
002,Coke,5.75,NULL
102,NULL,2.0,NULL
333,NULL,NULL,NULL
980,Donut,3.75,NULL

My output (In SQL, and also in a CSV) should be as follows;
001,Chips,2.50,2.20
002,Coke,5.75,
102,,2.0,
333,,,
980,Donut,3.75,

How to get this?...

Please help

Zee

sanjnep
Posting Yak Master

191 Posts

Posted - 2008-05-16 : 13:56:57

Just do like this

ISNULL(CAST(Price AS varchar(100)),' ') AS Price

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-05-16 : 14:15:55

Thanks sanjep.
But the way you explained I am converting the field datatype to Varchar. I dont want to change the numeric fields it to varchar, and want to keep the numeric field as numeric.

Please help.

Thanks,

Zee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 14:21:54

A blank is not valid numeric data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources