Skip to content

Instantly share code, notes, and snippets.

@Gimly
Created April 15, 2016 07:05
Show Gist options
  • Save Gimly/987708bdec70820d78f428981266e37e to your computer and use it in GitHub Desktop.
Save Gimly/987708bdec70820d78f428981266e37e to your computer and use it in GitHub Desktop.
PowerShell - Export a database table to a CSV file
<#
.SYNOPSIS
Runs a select with on the specified table with the specified columns
and writes the result to a CSV file.
.DESCRIPTION
This function calls the connection passed as a parameter and sends a
SELECT command to it. The table on which the SELECT is run as well as
the selected columns are passed as a parameter.
The results of the select are then saved in a CSV file, at the folder
defined by the TargetFolder parameter with the name corresponding to the
exported table name.
#>
function Export-TableToCsv (
# An opened connection to a SQL database
[Parameter(Mandatory = $true)]
[String] $ConnectionString,
# The folder where the file should be copied
[Parameter(Mandatory = $true)]
[ValidateScript({Test-Path $_ -PathType Container})]
[String] $TargetFolder,
# The name of the database table to export
[Parameter(Mandatory = $true)]
[ValidatePattern("^[a-zA-Z0-9\-_]+$")]
[String] $TableName,
# The list of columns, defined by their names, to export
[Parameter(Mandatory = $true)]
[ValidatePattern("^[a-zA-Z0-9\-_]+$")]
[String[]] $ColumnsToExport)
{
$ofs = ','
$query = "SELECT $ColumnsToExport FROM $TableName"
$connection = New-Object System.Data.SqlClient.SqlConnection
try
{
$connection.ConnectionString = $connectionString
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $Connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | Export-Csv "$TargetFolder/$TableName.csv" -NoTypeInformation -Encoding UTF8
}
finally
{
$connection.Dispose()
}
}
@rferraton
Copy link

Usefull script thanks !
You can go faster for the export-csv if you do
Export-Csv -InputObject $DataSet.Tables[0] "$TargetFolder/$TableName.csv" -NoTypeInformation -Encoding UTF8 instead of using piping at line 52

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment