Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:

$ErrorActionPreference="Stop"

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

go
select *
 from sys.master_files
go

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:

Enjoy!

Total article views: 4008 | Views in the last 30 days: 30
 
Related Articles
FORUM

Exporting database with vbscript?

Need webserver-script to export database

FORUM

Export sql server 2000 database as .sql format

Export sql server 2000 database as .sql format

FORUM

Exports in SQL Server

Exports in SQL Server

FORUM

Exporting Access database

Exporting Access database

FORUM

How can we export all data of one database server.......

How can we export all data of one database server.......

Tags
extracting data    
powershell    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones