Export Data as csv file from SQL server using command line tools

In the post we will be discussing about the ways of exporting data from SQL server to csv file. As per the use case the output should have:

  1. Header row
  2. The seperator of columns should be ",".
  3. The file should be encoded as UTF-8.

There are two tools for exporting data from SQL Server to csv file. These are sqlcmd and bcp.

SQLCMD

The command which can be used for this purpose is
Sqlcmd -S "<ServerName\InstanceName>" -d "<DatabaseName>" -U "<UserName>" -P "<Password>" -i "<SQLScriptFilePath>" -W -f 65001 -h -1 -s "," -o "<OutputFilePath>"

E.g.: Sqlcmd -S "10.00.00.00" -d "EmployeeDatabase" -U "User1" -P "Password" -i "Script1.sql" -W -f 65001 -h -1 -s "," -o "c:\File.csv"

Attributes


  • -W is used to remove all the whitespaces
  • -h -1 is used to remove the header (if header is not removed from sqlcmd command than header will come in csv output but with "---" below the header line.
  • -f 65001 is used for specifying UTF8 encoding
  • -s is used to specify separator ","


Workarounds


  • We can use -Q "SELECT * from tbl_Employee" instead of -i "Script.sql"
  • The header should be included using SQL query. E.g. SELECT 'EmployeeID' AS EmployeeID, 'EmployeeName' AS EmployeeName UNION ALL SELECT EmployeeID,EmployeeName FROM tbl_Employee.
  • We can also use Windows authentication using -T attribute instead of specifying username and password.

BCP


The command which can be used for this purpose is
bcp "<storedprocedure or query>" queryout "<outputfile>" -S "<ServerName\InstanceName>" -d "<DatabaseName>" -U "<UserName>" -P "<Password>" -w -t"," -r"\n"

Attributes

  • -t specifies the field terminator.
  • -r specifies the row seperator


Comments

Post a Comment

Popular posts from this blog

Query within a Task Scheduler Library folder using schtasks.exe and getting total number of tasks

As we all know that working with task scheduler is not an easy task when it comes to query, change, add etc. through the command prompt. Here i will share few of the details while working with schtasks.exe. What is Schtasks.exe ? Schtasks enables an administrator to create, delete, query, change, run, and end scheduled tasks on a local or remote computer. Running Schtasks.exe without arguments displays the status and next run time for each registered task. For more information to work with Schtasks follow the link " https://docs.microsoft.com/en-us/windows/desktop/taskschd/schtasks " Now lets get back to what our topic concerns -  Query within a Task Scheduler Library folder using schtasks.exe and getting total number of tasks. For Query we just use  - schtasks.exe /query /TN \[FolderName]\ Now to query and get the number of tasks we will follow below process : Just for an example I'm working for two folder - \Microsoft\Windows\SideShow and \Micros
Read more

Getting Started with AppCmd.exe

Overview AppCmd.exe is the single command line tool for managing IIS 7 and above. It exposes all key server management functionality through a set of intuitive management objects that can be manipulated from the command line or from scripts. AppCmd enables you to easily control the server without using a graphical administration tool and to quickly automate server management tasks without writing code. Some of the things you can do with AppCmd: Create and configure sites, apps, application pools, and virtual directories Start and stop sites, and recycle application pools List running worker processes, and examine currently executing requests Search, manipulate, export, and import IIS and ASP.NET configuration AppCmd also allows server administrators to build advanced management tasks simply by combining multiple simpler AppCmd.exe commands, or reusing the output of the tool inside another program. This article contains: How to Use AppCmd.exe Getting H
Read more