BCP Export and Text Qualified Columns

 Bcp is a pretty efficient way of doing a simple extract of a table or query to a delimited flat file, something which still seems a pretty common task even in today's world of fancy ELTL tools and processes, though it does have it's quirks and limitations.

One of these limitations is that there's no easy way to text qualify string values in columns for cases where the file delimiter might be a valid character in the column value. If there's flexibility around the allowable column delimiter then this isn't too much of a problem, but sometimes the value for the delimiter is set by an 3rd party system or process.

About the Code

This PowerShell script will take an array of tables, determine the column metadata, text qualify and string columns with double quotation marks, and then extract the data to a specified flat file. 

If you want a different text qualifier then change the text "CHAR(34)" in the column query to the qualifier you need. This also uses a pipe delimiter, but this can be changed in the $params variable by altering the value "-t|" to the value you need. These could both be parameters to the script as well.

Parameters

For the parameters:

• $Tables is a string array of the tables to extract. It needs to be an array but could just be a single value if only 1 table needs to be extracted
• $Server is the SQL Server host and instance name if a named instance
• $Database is the database where the table lives
• $OutPath is the folder to extract the files to. The files will have the same name as the table, and a .txt extension. The extension could also be paramaterised or just manually changed on line 42
• $Schema is the schema of the table, default is dbo

 The Script

param(

[Parameter(mandatory=$true)]

[string[]]$Tables,

[Parameter(mandatory=$true)]

$Server,

[Parameter(mandatory=$true)]

$Database,

[Parameter(mandatory=$true)]

$OutPath,

[Parameter(mandatory=$false)]

$Schema = "dbo"

)

$bcp = "bcp.exe"

if(!(Test-Path -Path $outPath))

{

throw "Output path doesn't exist"

exit -1

}

 

$tm_start = (Get-Date) # Just used to get extract timings

foreach($t in $Tables)

{

[string]$columnQuery = "select stuff((

select ',' + case when system_type_name like '%char%' then 

                     'QUOTENAME(' + name + ', CHAR(34))'

when system_type_name = 'geometry' then  

                    '[' + name + '].STAsText() as ' + '[' + name + '_wkt]'

else QUOTENAME(name) end as [text()]

from sys.dm_exec_describe_first_result_set ('select * from $Schema.$t', null, 0)

for xml path (''), type).value('.[1]', 'nvarchar(max)')

,1, 1, '')

as collist"

$metadata = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $ColumnQuery

    

$query = "select {0} from {1}.{2}" -f $metadata.collist, $Schema, $t

    #write-host $query

$outFile = [System.IO.Path]::Combine($OutPath, $("$t.txt"))

$params = "`"$query`"", "queryout", "`"$outFile`"", "-S", $Server, "-d", $Database, "-T", "-c","-t|", "-C", "65001"

#Write-Host $params

$b = & $bcp $params

#$b

}

 

# Extract time

$tm_end = Get-Date

[timespan]$span = $tm_end - $tm_start

$span.Seconds