Use "Enter" as column or field separator in SQLCMD

Question

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 "
   "

2. Then executed above in CMD using below command:

   test.bat localhost user pass db_name

3. But it gives output like:

   ""REC"""LINE"

4. I want output like:

   "Rec"
   "LINE"

Any input is appreciated!

Answer 1

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 "
    "

Answer 2

[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?