SQLCMD CSV Output

SQLServer (both express and full versions) provide a useful command line tool (sqlcmd) which you can use to run batch tasks. See the full list of options here. Here's a quick way to get usable CSV output from this tool.

The main things to watch out for are specifying the correct delimiter.

• -s: For Comma-Seperated-Values (CSV), the value of -s ","works a joy.
• -W: The -W option removes the trailing whitespace.
• -h: Setting this to -1 removes the headers of the fields (if your query is returning a result set).
• -i: This is the input file (the file with the sql to be executed). For interactive scripts and to make your batch file resuable, set this to %1 to use the first command line argument
• -o: This specifies the output file
• -S: The server.
• -U: The user
• -P: Password

The complete version of the reusable script with comments and usage:

I'm still working on how to remove the number of rows affected line after the result set.

UPDATE: Setting NoCount On (Set NotCount On) in the SQL file disables this behaviour.