SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Extracting data with headers using PowerShell

By Louis Li,

Recently I came across an opportunity to automate a manual extracting process: export a number of tables from a database using CSV format with headers. After some researching (googling), typing and debugging, a PowerShell script is ready to go. This script uses the SQL Server Management Objects to extract data from a SQL Server database and exports the results into CSV files using the PowerShell build-in function export-csv. It’s easy to read and maintain:


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: "$cnt
    $file = ".\" + $cnt.ToString() + ".csv"
    $dt | export-csv $file -notypeinformation
    Write-Host "Completed!"


This script takes 3 parameters: server name, database name and script file.

The first parameter <server name> includes both the server name and the instance name. For example, it will be .\SQL2012 when connecting to instance SQL2012 on the local server.

The second parameter <Database name> is the database that the script uses.

The third parameter <Script File> is a .sql file which contains all the queries required to run. The script could contain more than one query, separated by “GO”. The results will be saved into separated CSV files with numbered filenames accordingly.

Below is one example of the source script file - Master.sql:

select *
 from sys.configurations

select *
 from sys.master_files

The CSV files will be saved to the current folder. It’s a good idea to have a dedicated folder for this purpose. Let’s use it:

.\csv.ps1 .\SQL2012 master master.sql

This command tells SQL Server to run master.sql in master database in SQL2012 instance on local server. As a result, 2 files are generated: 1.csv and 2.csv. They are the results of first query (select * from objects) and second query (select * from sys.tables) accordingly.

If you have the .csv file extension associated with Excel, you will be able to view them directly in Office Excel. Below are 2 screenshots of viewing .csv files in Excel:


Total article views: 4162 | Views in the last 30 days: 0
Related Articles

Exporting database with vbscript?

Need webserver-script to export database


Export diagrams

Export diagrams in current database to an import script.


Export sql server 2000 database as .sql format

Export sql server 2000 database as .sql format


Exports in SQL Server

Exports in SQL Server


Exporting Access database

Exporting Access database

extracting data