1

What I need to do:

  • I want to export data from SQL Server 2008 to a CSV in specific format.
  • I can't use the BCP utility for this.
  • I have exported CSV File using SQLCMD but the only issue is I need "Enter" as column or field separator in CSV file.
  • Then I saved SQLCMD file as windows batch file and executed that using CMD

For Example, (Note: test.sql contains a PLSql Block which return columns data.):

  1. I have saved below statement as batch file with name test.bat:
   sqlcmd -S %1 -U %2 -P %3 -v DatabaseName = %4 -i %current_dir%\test.sql -o %current_dir%\test.csv -y0 -I -s "
   "
  1. Then executed above in CMD using below command:
   test.bat localhost user pass db_name
  1. But it gives output like:
   ""REC"""LINE"
  1. I want output like:
   "Rec"
   "LINE"

Any input is appreciated!

TT.
  • 15,774
  • 6
  • 47
  • 88
Jatin Singla
  • 11
  • 5

2 Answers2

0

I used this syntax in my table and when i opened the file it gived to me some kind of the result you are asking for. Just try it if it is what you are requiring. 

 sqlcmd -S .\SQLEXPRESS -d Atrax_BazaRM -E -Q "select * from Njesite" -o "E:\MyData.csv" -h-1 -s "
    "
Shukri Gashi
  • 535
  • 2
  • 10
  • I have saved below statement as batch file: sqlcmd -S %1 -U %2 -P %3 -v DatabaseName = %4 -i %current_dir%\test.sql -o %current_dir%\test.csv -y0 -I -s " " Then execute this in cmd using: test.bat localhost user pass db_name. But it gives output like: “"REC"”"LINE", and I want output like: “Rec” “LINE” – Jatin Singla Feb 10 '16 at 09:49
0

[Too long for comment]

You have in your list I can't use the BCP utility for this.. In the comment section you replied BCP gives error of xp_cmdshell on our client..

You know you can enable use of xp_cmdshell, right? I'm not saying you have to and possibly the client won't agree. You can enable xp_cmdshell by:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

After that you can use BCP and possibly this will make your problem easier to solve?

Community
  • 1
  • 1
TT.
  • 15,774
  • 6
  • 47
  • 88
  • Thanks TT, I have already used above option, instead of this it is giving error on client machines. Just curious to know if this is possible with SQLCMD. – Jatin Singla Feb 11 '16 at 08:35
  • @JatinSingla I didn't understand your comment: *"I have already used above option, instead of this it is giving error on client machines"* > what exactly is giving an error? About SQLCMD, I think it's an ugly beast, I never use it so I can't really say whether it is possible in SQLCMD. I've searched on this issue online and like you I have not found anything about using newline as column separator. – TT. Feb 11 '16 at 08:39