Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Adding more functionality to SQL Server BCP with PowerShell


By:   |   Last Updated: 2016-06-29   |   Comments (5)   |   Related Tips: More > Import and Export

Problem

As a SQL Server DBA, I frequently need to use the SQL Server BCP utility to export query data to a CSV file and then pass to other users or applications. However, I find there are some deficiencies with the BCP utility as listed below:

  1. It is hard to put double (or single) quotes on each column value.
  2. It is hard to add column names as the first line in the exported file.
  3. If a column of char[N] (assuming N > 1) has any empty string, i.e. [col]='', BCP will generate [N] blank spaces (when the business requirement is no space).
  4. If a column of varchar[N] (assuming N > 1) has any empty string, i.e. [col]='', BCP will generate [1] blank space instead of a real empty string.

Problem Demonstration

The following code will generate a table with 3 rows on my local SQL Server instance, and then I will use bcp.exe to export the SQL Server table to a text file.

Use MSSQLTips -- my test database
--drop table dbo.t;
create table dbo.t (id int identity, b varchar(10), c char(10), note varchar(100));
go
-- populate with 3 records insert into dbo.t (b, c, note) select '', '', 'both b and c columns are empty strings, i.e. b='''' and c=''''' union all select null, null, 'both b and c columns are null, i.e. b=null and c=null' union all select 'Hello', ' World', 'both b and c columns have values, for c column, there is leading blank space'; go

Now in an Command window, we run this bcp.exe command

 bcp.exe "select  * from dbo.t" queryout "c:\temp\test.txt" -t "|" -S localhost -d MSSQLTips -T -c ;

If I open "c:\temp\test.txt" with NotePad, I will see the following (I highlighted the blank spaces of interest)

1| |          |both b and c columns are empty strings, i.e. b='' and c=''
2|||both b and c columns are null, i.e. b=null and c=null
3|Hello| World    |both b and c columns are normal with values

The first line demonstrates the issues. For column [b], it is exported to be 1 blank space though it is an empty string, while for column [c] it has 10 blank spaces, though it is an empty string.

Actually, if you look at line 3, you will see World , with leading/trailing blank spaces (the total string is 10 characters), this is because column [c] is defined as char(10), but in many cases users want the trailing spaces trimmed.

Solution

To solve the above mentioned issues, I wrote a PowerShell (PS) function to replace BCP utility's export function (with -c parameter). But make no mistake, this is not a replacement of the BCP utility, not even with any intention or attempt to do so. But it indeed saves me lots of time and effort to meet user requirements.

<#
.Synopsis
   Bulk Copy Query Data to CSV file
.DESCRIPTION
   Bulk Copy Data from a query against a SQL Server Database into a CSV file
.EXAMPLE
   bcp-data -ServerInstance localhost -Database mssqltips -Query "select * from dbo.MyTable -FilePath c:\temp\mytable.txt -Trim Y
.INPUTS
   None
    You cannot pipe objects into BCP-Data
.OUTPUTS
   No output
.NOTES
   ver 1.0 - Jeffrey Yao 2016/Jun/01
#>
#Requires -Version 3.0 add-type -AssemblyName "System.Data";
function BCP-Data { [CmdletBinding()]
Param ( # SQL Server Instance name with default to local servername [Parameter(Mandatory=$false)] [string]$ServerInstance=$env:ComputerName,
# Database name with default to 'master' [Parameter(Mandatory=$false)] [string] $Database='master',
# Query, the query will return a result set [Parameter(Mandatory=$true)] [String] $Query,
# FilePath, the full path name [Parameter(Mandatory=$true)] [String] $FilePath,
# FieldTerminator, the separator between columns, default to "|" [Parameter(Mandatory=$false)] [String] $FieldTerminator="|",
# RowTerminator, the separator between rows, default to a new line "`r`n" [Parameter(Mandatory=$false)] [String] $RowTerminator="`r`n",
# QuoteMark, the quoting marks around columns, default to none [Parameter(Mandatory=$false)] [String] $QuoteMark="",
# NoHeader, a switch parameter to decide whether column header are included in the csv file, by default, there is always a header unless this switch parameter is present [Parameter(Mandatory=$false)] [switch] $NoHeader,
# Trim, decides whether to trim the column of string data type, N = no trim, Y=trim both left and right of the string, L=left trim, R=right trim [Parameter(Mandatory=$false)] [ValidateSet('N', 'Y', 'L', 'R')] [String] $Trim='N' )
#we assume use windows authentication, otherwise, you need to provide User and PassWord for connection $conn = new-object System.Data.SQLClient.SqlConnection ("server=$ServerInstance; database=$Database; trusted_connection=true"); $sqlcmd = New-Object System.Data.SqlClient.SqlCommand($query, $conn) $conn.Open();
$dr = $sqlcmd.ExecuteReader(); $dt = New-Object System.Data.DataTable; $dt.Load($dr);
$sw = new-object System.IO.StreamWriter($FilePath, $false);
#we first write the header
if (-not $NoHeader) { [string]$head=$dt.Columns.columnname -join $FieldTerminator; $sw.Write($head); $sw.write($RowTerminator); }
foreach ( $r in $dt.rows) { [string]$tf = $FieldTerminator; for ([int]$i=0; $i -lt $dt.Columns.count; $i++) { if ($i -eq $dt.Columns.count -1 ) #last column does not need to be followed by $FieldTerminator { [string]$tf='';} if ($r[$i] -ne [System.DBNull]::Value) { if ($r[$i].GetType().name -ne 'Boolean') { $col_val = $r[$i].ToString() ; } else { if($r[$i] -eq $true) { $col_val= '1' } else {$col_val='0'} } $col_val = $QuoteMark + $( switch ($trim) { 'Y' { $col_val.Trim() } 'N' { $col_val } 'R' { $col_val.TrimEnd()} 'L' { $col_val.TrimStart()} } ) + $QuoteMark + $tf; $sw.Write($col_val); } else { $sw.Write($QuoteMark+$QuoteMark+$tf);} } #loop through columns
$sw.Write($RowTerminator);
}#loop through rows
#clean up $sw.Close(); $dr.Close(); } # bcp-data

Examples

Now, I still use the previously created table and demo the use of different combinations of the function parameters

For demo purposes, I started the PS ISE and opened two windows. In the first window, I copy/pasted the BCP-Data script and ran it and in the second window copy/pasted the following script and ran it to get all the txt files and open them with notepad.exe.

#0. using bcp utility
bcp.exe "select  * from dbo.t" queryout "c:\temp\Test_bcp.txt" -t "|" -S localhost -d MSSQLTips -T -c
#1. same as previous bcp utility, but corrects single space for empty string issue (to b varchar(10)) bcp-data -ServerInstance localhost -Database MSSQLTips -FilePath "c:\temp\test_ps.txt" -Query "select * from dbo.t " -FieldTerminator "|" -NoHeader;
#2. adding header bcp-data -ServerInstance localhost -Database MSSQLTips -FilePath "c:\temp\test_header.txt" -Query "select * from dbo.t " -FieldTerminator "|" ;
#3. trimming ALL blank spaces bcp-data -ServerInstance localhost -Database MSSQLTips -FilePath "c:\temp\test_trim_all.txt" -Query "select * from dbo.t " -FieldTerminator "|" -Trim Y; #Y=trim ALL spaces
#4. Adding double quotes to field and also trimming all right spaces bcp-data -ServerInstance localhost -Database MSSQLTips -FilePath "c:\temp\test_quote_trim_righ.txt" -Query "select * from dbo.t " -FieldTerminator "|" -Quote '"' -Trim R; #R=Trim Right spaces

Here is the result in sequence:

bcp-data export results

Summary

In this tip, a new PowerShell function is created to address some weakness in the original BCP.exe utility. Since this is not a compiled utility tool, the performance of this function is 30+ times slower than the BCP utility when big chunks of data need to be exported. For example, for about 20,000 rows in table [AdventureWorks2012].[Person].[Person], using the BCP utility in my environment it takes 0.6 seconds, while using bcp-data function it takes 18 seconds.

So if you are not concerned about performance, or your business requirements cannot be met due to BCP utility's "issues", the bcp-data function is a convenient alternative.

In some rare cases where you do not have bcp.exe installed, you can still use this PS function as it only relies on .Net framework 2.0+, more precisely, System.Data.dll.

Next Steps


Last Updated: 2016-06-29


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, August 31, 2016 - 2:42:01 PM - jeff_yao Back To Top

Hi Krishna, glad to hear that the script is working for you. For [Varbinary] type, I almost never encounter a scenario that I need to export it to a CSV ASCII file and thus I actually did not test this case in my tip (my bad).

I will take a look of this tip and see whether I can add [Varbinary] type into the processing logic.

Thanks again for your interests and feedback.


Wednesday, August 31, 2016 - 10:07:39 AM - Krishna Back To Top

Yes. Finally your script is working just fine. But for Varbinary type columns is giving the value System.Byte[]

Any fix for this please?


Tuesday, August 30, 2016 - 12:45:53 AM - jeff_yao Back To Top

Thanks Krishna for reading / trying the tip.

I notice you are running under

PS SQLSERVER:\>

which more often causes problem when we are dealing with File system path.

So I'd like you to run the following

Set-Location c:\

and then re-run

C:\> bcp-data -ServerInstance SQLNODE01\DBSQL01D -Database master -TSQL "C:\Data_Import\TSQL.txt"  -FilePath C:\Data_Import\mytable.txt 

 

Thanks,

Jeff_yao


Monday, August 29, 2016 - 2:50:03 PM - Krishna Back To Top

Looks like if the column value is empty or null I guess the script threw an error?


Monday, August 29, 2016 - 12:45:20 PM - Krishna Back To Top

Hello...Great script.

Unfortunately when I execute one TSQL script with your script I am getting the following error. But when I executed in query analyzer its running very nice. what is the issue?

========================================================================================

 

PS SQLSERVER:\> bcp-data -ServerInstance SQLNODE01\DBSQL01D -Database master -TSQL "C:\Data_Import\TSQL.txt"  -FilePath C:\Data_Import\mytable.txt 

Exception calling "ExecuteScalar" with "0" argument(s): "The multi-part identifier "c.backup_start_date" could not be bound.

The multi-part identifier "d.backup_start_date" could not be bound."

At line:61 char:5

+     $dr = $sqlcmd.ExecuteScalar();

+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : SqlException

 

Exception calling "Load" with "1" argument(s): "Value cannot be null.

Parameter name: dataReader"

At line:63 char:5

+     $dt.Load($dr);

+     ~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : ArgumentNullException

 

You cannot call a method on a null-valued expression.

At line:115 char:5

+     $dr.Close();

+     ~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull


Learn more about SQL Server tools