Friday, June 25, 2010

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.

No comments: