Export Query to Tab Delimited Text File with Powershell Even Without Data


 


Import-Module -Name 'SQLPS' -DisableNameChecking
$dt = Get-Date -Format yyyyMMddHHmmss
$workdir='C:\temp'

$svrname = "MC\SQL2014DEV"
 $db ='Northwind'

#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname
$svr.ConnectionContext.StatementTimeout = 0

$myPath0 = "$workdir\myTabfile$($dt)0.txt"
$myPath = "$workdir\myTabfile$($dt).txt"

$q = @"

SELECT  [CustomerID],[CompanyName]
  FROM [Northwind].[dbo].[Customers]
 -- where CustomerID='XXXXXX'
 UNION ALL
Select '' CustomerID, ''CompanyName

"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0
gc $myPath0 | % {$_ -replace '"', ""} | Select -SkipLast 1 | out-file $myPath -Fo -En ascii

 

https://jingyangli.wordpress.com/2015/02/13/export-query-to-tab-delimited-text-file-with-powershell/

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s