How to invoke-sqlcmd and export WITH headers and dashes/hyphens but tab-delimited

Question

Say I run this:

$HOME_DATABASE = 'mydb' 
$params = @{
    'Database' = $HOME_DATABASE
    'ServerInstance' = 'mydb'
    'Username' = 'myuser'
    'Password' = 'mypass'
    'Query' = 'select * from sometable'
}
$queryresults = Invoke-Sqlcmd @params

I want the output to look like:

Name    Date    Number of Records   95th Percentile 99.5th Percentile   100th Percentile
----    ----    -----------------   --------------- -----------------   ----------------
asdf    2022-10-02 00:00:00.000 1234    5678    9012    12345

where what's separating the fields is a tab.

I tried this, but it doesn't include the hyphen/dash line.

$queryresults |ConvertTo-Csv -NoTypeInformation -delimiter "$query_result_separator" | `
 ForEach-Object {$_ -Replace('"','')} | `
 Out-file c:\temp\$($query_attachment_filename) -fo -en ascii

Somebody else said to export-csv, then go back and replace the quotes in the file. Is there a better way?

Answer 1

Both Export-Csv and ConvertTo-Csv (the former's in-memory counterpart) by design do not create a separator line between their header row and the data rows, given that the sole purpose of such a line would be to make the the data human-friendly, whereas CSV / TSV data is meant for programmatic processing.

• To get such a line, you'll have to insert it manually.

Also, both cmdlets "..."-enclose all field values - invariably in Windows PowerShell, by default in PowerShell (Core) 7+.

• In PowerShell (Core) 7+ you can use -UseQuotes Never as an opt-out of this behavior.

• In Windows PowerShell you need to remove the " chars. after the fact, as you do in your own solution attempt.

---

A solution that works in both PowerShell editions:

$i = 0
$queryresults |
  ConvertTo-Csv -NoTypeInformation -Delimiter "`t" |
  ForEach-Object { 
    $_ -replace '"' # output with " chars. removed
    # If it was the first, i.e. the *header* row that was just output,
    # construct and output the desired separator row.
    if ($i++ -eq 0) {
      ($_ -replace '"' -split "`t").ForEach({ '-' * $_.Length }) -join "`t"
    }
  }  

Pipe to Set-Content as needed (which is preferable to Out-File, given that it is strings that are to be saved - see this answer).