Export to CSV from SQL Table

Hi All,

Today I am sharing a powershell method for exporting the content of SQL Table to CSV file.

This can be used from remote server or machine as well if you have sql powershell installed.

Download & install sql management tools from below path on your admin server.

http://www.microsoft.com/en-us/download/details.aspx?id=7593

This will install management studio as well as SQL powershell snapins.

Script download Location :-

http://gallery.technet.microsoft.com/scriptcenter/Export-CSV-from-SQL-Table-4bf6cdea

 

Script Code:-

#####################################################################
$date = get-date -format d

$time = get-date -format t

$date = $date.ToString().Replace(“/”, “-”)

$time = $time.ToString().Replace(“:”, “-“)
$time = $time.ToString().Replace(” “, “”)
$output1 = “.\” + “G_EXP_” + $date + “_” + $time + “_.csv”

########################Load SQL Snapin##############################

If ((Get-PSSnapin | where {$_.Name -match “SqlServerCmdletSnapin100”}) -eq $null)
{
Add-PSSnapin SqlServerCmdletSnapin100
}

If ((Get-PSSnapin | where {$_.Name -match “SqlServerProviderSnapin100”}) -eq $null)
{
Add-PSSnapin SqlServerProviderSnapin100
}
############################Invoke Sql Connection#######################
$sql_instance_name = ‘Lab\DEV’
$db_name = ‘testdb’
$sql_user = ‘test_user’
$sql_user_pswd = ‘test_user’
$query = ‘select * from testdb.dbo.testList’

#########################################################################

$expcsv = invoke-sqlcmd -U $sql_user -P $sql_user_pswd -Database $db_name -Query $query -serverinstance $sql_instance_name
if($expcsv -ne $null)
{
$expcsv | Export-Csv $output1

}
else
{
Write-Host “nothing is present in db table to export”

}

#######################################################################

 

Regards

Sukhija Vikas

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s