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.
| Author |
Topic |
|
aenagy
Starting Member
2 Posts |
 Posted - 2014-11-12 : 18:37:40
|
| First of all I'm a sysadmin, not a DBA.I need to extract data from a number of different queries. The result from each query will go to a separate text file. The extracted data will most likely be viewed using Microsoft Excel or maybe Notepad. In some cases the result of the query includes a field that contains carriage return and linefeeds and/or fields with a value of NULL. I'm looking for a solution that can be scripted and does not require (much) prior knowledge of the data -- I would like to keep this generic. If there is a better approach, then I'm open to suggestions.First, I settled on BCP after finding this code:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/812b8eec-5b77-42a2-bd23-965558ece5b9/bcp-export-to-csv-with-headersSecond, I was able to address the CR+LF using these links and they work as expected.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150396http://tannock.net/2012/03/clearing-line-breaks-in-sql-server-text-fields/Now I'm trying to combine the two ... or is there a better solution?As for the NULL fields, if they are converted to the text "NULL" that would be good.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-11-12 : 18:42:09
|
| If you want to use the string NULL for actual NULL values, then you'll need to add that to the query:select ISNULL(Column1, 'NULL') AS Column1, ...from ...Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
aenagy
Starting Member
2 Posts |
Posted - 2014-11-13 : 14:02:21
|
| Thanks.Is there a way to specify a file that contains my query using BCP? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-11-13 : 14:05:31
|
| Yes but that'll get tricky as it's not directly supported by bcp. It's easiest to save your query as a view and then bcp out the view like you would a table.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Export data with with CR,LF and/or NULL