Invoke-sqlcmd output without "quotes" and headers

Question

Output example:

#TYPE System.Data.DataRow <---
"PersonalNr" <---
"00001005"
"00001008"
"00001009"
"00001013"
"00001019"
"00001024"

Requirements:

I want a output without the 2 first lines and without the quote symbols, how can i do that?

For the headers I tried the options -h -1 but the output is still the same. Is it even possible with Sqlcmd?

Current Code:

Invoke-Sqlcmd -ServerInstance SERVER -h -1 -Query $QueryFmt | Export-CSV $AttachmentPath

Just wanted to note that `Invoke-Sqlcmd` =/= sqlcmd.exe – Matt Mar 27 '17 at 14:54 — Matt, Mar 27 '17 at 14:54

dbso · Accepted Answer · 2017-03-28T10:43:57.900

9

This code should work:

Invoke-Sqlcmd -ServerInstance "" -Database "" -Query "" | ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File ("C:\test.csv") -Force -Encoding ascii

Invoke-Sqlcmd produces:

With Export-Csv, the file looks like:

#TYPE System.Data.DataRow
"PersonalNr"
"00001005"
"00001001"
"00001006"
"00001007"
"00001008"

Using my above mentioned code, I get the file looking like this:

edited Mar 28 '17 at 10:43

answered Mar 27 '17 at 13:50

dbso

636
1
5
14

Doesn't work for me. Output is still the same for me, are you sure it should work with the csv export at the end? – Moek Mar 27 '17 at 14:00
Output: #TYPE System.String "Length" "19" "19" "19" ......... – Moek Mar 27 '17 at 14:03
Yes, sorry. I realized just after I pressed answer that it would not work. Did you try my updated answer? – dbso Mar 27 '17 at 14:04
I would use a trim before a replace. That way if there are any embedded quotes you will not have to worry about removing them. Also would `Select-Object -Skip 2` also remove the first row of actual data? – Matt Mar 27 '17 at 14:08
Yes, but the first row of data was to be excluded here. I've added trim in the replace, thanks – dbso Mar 27 '17 at 14:16
Where did you get the idea the first row of _data_ was to be excluded? I meant I would used `trim('"')` instead of `-replace` – Matt Mar 27 '17 at 14:25

score 2 · Answer 2 · answered Mar 13 '19 at 12:49

2

Try this,

$query_result | Format-Table -AutoSize -Wrap -HideTableHeaders

answered Mar 13 '19 at 12:49

Nirav Mistry

949
5
15

1

This is the BEST answer, SHORTEST yet effective, should have more likes ! – Eddie Kumar Sep 09 '22 at 12:29

Mihail Kuznesov · Answer 3 · 2017-03-27T14:21:30.650

0

Try this:

## You data for export to csv
$csv = Invoke-Sqlcmd -ServerInstance SERVER  -Query $QueryFmt
# 

##Will remove type information  and will remove header
$csv | Export-CSV -NoTypeInformation | select -Skip 1 | Set-Content $AttachmentPath 
#

edited Mar 27 '17 at 14:21

answered Mar 27 '17 at 14:02

Mihail Kuznesov

555
2
13

`-h -1` is for sqlcmd.exe. That does not have a mapping to `Invoke-SQLcmd` as per https://learn.microsoft.com/en-us/sql/powershell/invoke-sqlcmd-cmdlet. Even if it did work you would be removing data with `select -Skip 1` – Matt Mar 27 '17 at 14:06

Invoke-sqlcmd output without "quotes" and headers

3 Answers3

Linked