Export Query to Tab Delimited Text File with Powershell






Import-Module -Name 'SQLPS' -DisableNameChecking 
$dt = Get-Date -Format yyyyMMddHHmmss
$workdir='C:\temp'
 
$svrname = "MC\MSSQL2014"
 $db ='testcs'
 
  
#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 * from dbo.web_contact1
"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0
gc $myPath0 | % {$_ -replace '"', ""} | out-file $myPath -Fo -En ascii
 
 


 
Advertisements

2 Comments on “Export Query to Tab Delimited Text File with Powershell”

  1. Gary K says:

    Hello Jingyang.
    I am new to Windows PowerShell. I recently received a request to write PowerShell script to automate a process to run a saved SQL query to extract data and then save the data into a .dat file with pipe-delimited and ascii-encoded file.
    Based on my limited knowledge, the big obstacle so far is that if I use Out-File, then the orientation inside the data file is that column head is on the left and data is on the right, which is not what I want. Besides, it seems that -delimiter ‘|’ is not accepted by Out-File.
    If I use Export-Csv, I have no idea what or how many steps followed to have to make the .csv file into .dat file with the correct format I need.
    I am hoping that I can obtain your help to give me your suggestion and direction that I can follow to make this thing happen. Thanks in advance.
    -Gary


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