We have covered multiple times in this blog that we can import CSV to Database very easily. However, recently I received a very interesting question where the user wants to export data with the help of SQLCMD.
The answer is very simple. Here are two different methods:
Method 1: CSV with Column Header
C:\Users\pinaldave>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,”
Method 2: CSV without Column Header
C:\Users\pinaldave>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,” -h-1
Additional explanation:
- -S”,” – sets the delimiter to the comma
- –w number – sets the length of the csv line before it wraps
- -W – removes trailing whitespace
- –h-1 – removes header in CSV (Column Header)
- -E (use trusted connection) – instead of this use  -U username and -P password
Additional note: The CSV file format is not standardized. The basic idea of separating fields with a comma is clear, but that idea gets complicated when the field data may also contain commas or even embedded line-breaks. CSV is a common data exchange format that is widely supported by consumer, business, and scientific applications.
Reference :Â Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
