Powershell, invoke-sqlcmd, export-csv fails to show data if there is more than one result

Question

I have the below code to get data from a SQL DB and export it into a CSV file:

#Server and Database names
$SQLServer = "Servername"
$DB = "DatabaseName"

#SQL Command
$FullScriptSQL="Select * from MyTable WHERE Column = 'TestData'"

#Invoke the command, rename the column headers and export to CSV file
$FullScriptCallLinked = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $FullScriptSQL | select-object @{ expression={$_.Column1};label='Column1 Name'},@{ expression={$_.Column2};label='Column2 Name'},@{ expression={$_.Column3}; label='Column3 Name' },@{ expression={$_.Column4} ; label='Column4 Name' }
Export-CSV -Path ".\ResultFile\FullScript.csv" -inputobject $FullScriptCallLinked -Append -NoTypeInformation

This works perfectly if there is one result. But if there is more than one result, it will show the below in the csv file

I am at my wits end as to why it is doing this. It's obviously the DB parameter data or something to that effect. Been googling for a few days with no luck. Anyone smarter than I able to assist please?

score 2 · Answer 1 · answered Jul 27 '21 at 01:33

2

Figured it out. I knew I was close!

#Server and Database names
$SQLServer = "Servername"
$DB = "DatabaseName"

#SQL Command
$FullScriptSQL="Select * from MyTable WHERE Column = 'TestData'"

#Invoke the command, rename the column headers and export to CSV file
$FullScriptCallLinked = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $FullScriptSQL 

foreach($i in $FullScriptCallLinked){
  select-object @{ expression={$_.Column1};label='Column1 Name'},@{ expression={$_.Column2};label='Column2 Name'},@{ expression={$_.Column3}; label='Column3 Name' },@{ expression={$_.Column4} ; label='Column4 Name' }
  Export-CSV -Path ".\ResultFile\FullScript.csv" -inputobject $i -Append -NoTypeInformation
}

answered Jul 27 '21 at 01:33

Chris

219
5
18

1

Actually the script from your question would have worked if you piped the object to `Export-Csv`: `$FullScriptCallLinked | Export-Csv -Path ".\ResultFile\FullScript.csv" -NoTypeInformation` instead of using `-InputObject`. – Santiago Squarzon Jul 27 '21 at 01:39
1

Ayyyy! Perfect. I actually came here to correct a different error I made above, but yours is waaay better. Thanks a million! – Chris Jul 27 '21 at 02:09
1

Good to know, why not use `SELECT Column1 AS "Column1 Name", Column2 AS "Column2 Name".....` on your SQL query, by doing that you wouldn't even need to rename your columns with `Select-Object` :) – Santiago Squarzon Jul 27 '21 at 02:54
I'll post it as an answer, hopefully it does more sense. – Santiago Squarzon Jul 27 '21 at 03:02
You just keep giving! Thanks mate, that would make it so much cleaner. If you post it as an answer, I can give it to you :) – Chris Jul 27 '21 at 03:33

Santiago Squarzon · Accepted Answer · 2021-07-27T03:20:23.250

Instead of using Select-Object to rename your columns, which is quite inefficient, you could give the alias to your columns on the query itself:

$SQLServer = "Servername"
$DB = "DatabaseName"

$query = @'
SELECT Column1 AS "Column1 Name",
       Column2 AS "Column2 Name",
       Column3 AS "Column3 Name",
       Column4 AS "Column4 Name"
FROM MyTable
WHERE ColumnX = 'TestData'
'@

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $query |
Export-CSV -Path ".\ResultFile\FullScript.csv" -NoTypeInformation

Also, as in my comment, the code you have on your question is fine and should work, the only problem was using -InputObject instead of piping the results to Export-Csv:

$FullScriptCallLinked | Export-Csv -Path ".\ResultFile\FullScript.csv" -NoTypeInformation

Powershell, invoke-sqlcmd, export-csv fails to show data if there is more than one result

2 Answers2