Knowledge
  • Read Me
  • Programming
    • ASP.NET
      • .NET Libraries
      • ASP.NET Core
        • Helper
          • Encryption
          • CSV Helper
          • String Helper
        • Logging
          • Simple Serilog
        • Middlewares
          • IP Restrictions
          • Request Throttling
          • Request Logging
        • Console
          • Command Line with arguments
        • JSON
      • ASP.NET Framework
      • Testing
        • Resources
        • xUnit.net
      • Naming Conventions
      • REST API Guidelines
    • Database
      • SQL Style Guide
      • MSSQL
        • Installation
          • Install MSSQL on MacOS M1 (ARM64)
        • Looping
        • Table Valued Functions
        • Session State
        • SQL Cheat Sheet
        • Export Pipe Delimited CSV With cmdshell
      • Redis
        • Redis Installation on Mac OS
        • Redis Installation on Docker
    • Java
      • AWS SDK - SSM
      • mTLS HTTP Connection
      • Read Resource Files
    • Javascript
      • Javascript Libraries
    • Python
    • OpenSSL
      • One Way SSL & Two Way SSL
      • Common OpenSSL Commands
      • Create Self-Signed Certificate
    • Misc
      • Git Commands
      • Windows Commands
      • PowerShell Commands
      • Vulnerabilities Dependency Check
      • Replace Filename Command
      • JSON Web Token (JWT)
      • Rabbit MQ Message-Broker
      • Pandoc Convert Document
  • DevOps
    • What is DevOps
    • CI & CD
    • Azure DevOps
  • Tools
    • Development Tools
Powered by GitBook
On this page
  • Enable xp_cmdshell
  • Disable xp_cmdshell
  • Simple Usage
  • Advanced Usage
  • Reference

Was this helpful?

  1. Programming
  2. Database
  3. MSSQL

Export Pipe Delimited CSV With cmdshell

Enable xp_cmdshell

By default, this feature is disabled.

-- Turns on advanced options and is needed to configure xp_cmdshell
master.dbo.sp_configure 'show advanced options', '1'
RECONFIGURE

-- enable xp_cmdshell
master.dbo.sp_configure 'xp_cmdshell', '1' 
RECONFIGURE

Disable xp_cmdshell

Recommend to turn off for security after finished your query.

-- Turns on advanced options and is needed to configure xp_cmdshell
master.dbo.sp_configure 'show advanced options', '1'
RECONFIGURE

-- dosable xp_cmdshell
master.dbo.sp_configure 'xp_cmdshell', '0' 
RECONFIGURE

Simple Usage

Example of export all the column data from a specific table. In this example, we use -t"|" to change the delimeter to | instead of default comma ,

--============================================================================
-- BCP <table> out <filename> <switches>
-- The switches used here are: 
-- `-c` Output in ASCII with the default field terminator (tab) and row terminator (crlf)
-- `-t` override the field terminator with “,”
-- `-T` use a trusted connection. Note that U -P may be used for username/password
-- `-S` connect to this server to execute the command
--============================================================================
EXECUTE master.dbo.xp_cmdshell 'bcp "
  SELECT * FROM [LocalTest].[dbo].[PersonTable]
  " queryout C:\temp\PersonTable.csv -t"|" -c -T -S'

Advanced Usage

To extract specific columns, the easier way is to create a new view table and then retrieve from the view.

CREATE VIEW [dbo].[DoctorForHtmlListing_vw]
AS
    SELECT
        Id,
        PersonName,
        JobTitle,
        REPLACE(JobDescription, '\r\n', '<br/>') AS JobDescription,
        REPLACE(Specialties, '\r\n', '<br/>') AS Specialties
    FROM dbo.PersonTable
    WHERE JobTitle = 'Doctor' 
GO

EXECUTE master.dbo.xp_cmdshell 'bcp "
  SELECT * FROM [LocalTest].[dbo].[DoctorForHtmlListing_vw]
  " queryout C:\temp\DoctorForHtmlListing.csv -t"|" -c -T -S'

Reference

PreviousSQL Cheat SheetNextRedis

Last updated 4 years ago

Was this helpful?

Microsoft - xp_cmdshell (Transact-SQL)
Enabling xp_cmdshell in SQL Server