Little SQL Server Tricks: Export CSV With Sqlcmd

If you frequently export the same but updated data from SQL Server to a CSV file, you can automate that task with sqlcmd. The sqlcmd tool is part of SQL Server and offers us various options to customise the export to our needs.

If sqlcmd is not already on your system, you can install it for Windows or for Linux / Mac. You can check if everything works with this command:

This should give you a lengthy list of options you can use. If you get an error instead, you can add "" to the parameter:

We can export the result of a SQL query with this command:

This should give us a CSV file like this one:

The different options we used above have this meaning:

• -S: the server we want to connect to. The . means the current machine.
• -d: the name of the database.
• -Q: the SQL query.
• -s: the separator between the cells.
• -W: removes the trailing whitespaces in the cells – without this option you get a formatted output.
• -o: the output file.
• -E: use a trusted connection.

To get rid of the number of exported rows at the end, we can add "SET NOCOUNT ON; " in front of our query:

This gives us a CSV file that looks like this one:

Unfortunately, there is no easy way to keep the header row but not the second row with the "----". We can remove the two first rows with the option -h -1:

This gives us a CSV file that looks like this one:

 

Conclusion

This little command line tools can be of immense help when you need to export data from the same source on a regular basis or if you need to document the exact options you used to create a file for auditing reasons.