Performing bulk export using bcp

This recipe demonstrates how to export contents of a table to a CSV file using PowerShell and bcp.

Getting ready

Make sure you have access to the AdventureWorks2008R2 database. We will export the Person.Person table to a timestamped text file delimited by a pipe (|).

Create a C:\Temp\Exports folder, if you don't already have it on your system.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Add the following script and run the following code:

   $server = "KERRIGAN"
   $table = "AdventureWorks2008R2.Person.Person"
   $curdate = Get-Date -Format "yyyy-MM-dd_hmmtt"
   
   $foldername = "C:\Temp\Exports\"
   
   #format file name
   $formatfilename = "$($table)_$($curdate).fmt"
   
   #export file name
   $exportfilename = "$($table)_$($curdate).csv"
   
   $destination_exportfilename = "$($foldername)$($exportfilename)"
   $destination_formatfilename = "$($foldername)$($formatfilename)"
   
   #command to generate format file
   $cmdformatfile...