Technical Article

Exporting data with header using Powershell

,

1. Save this script (e.g. csv.ps1)

2. Run .\csv.ps1 <server name> <database name> <SQL script file>

Output files will be generated under same folder this PowerShell script is in.

Note: In SQL Script file, multiple statements can be added and they will be saved into files sequentially. Filenames are sequential numbers.

For example, master.sql:

select * from sys.configurations

go

select * from sys.master_files

go

Run: .\csv.ps1 . master master.sql

2 csv files will be generated:

1.csv

2.csv

They contain results from these 2 queries.

$ErrorActionPreference="Stop"
write-host $args.Count
if ($args.Count -le 2)
{
    throw "Usage: <server name> <db name> <script file>"
}
$servername = $args[0]
$database = $args[1]
$sourcefile = $args[2]

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$server = New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)
$1 = Get-Item($sourcefile)
$ds = $server.Databases[$database].ExecuteWithResults("$(Echo $1.OpenText().ReadToEnd())")
$cnt = 0
Foreach ($dt in $ds.Tables)
{
    $cnt ++
    Write-Host "Writing table: "$dt.Name
    $file = ".\" + $cnt.ToString() + ".csv"
    $dt | export-csv $file -notypeinformation
    Write-Host "Completed!"
}

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating