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

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 ".ResultFileFullScript.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?

Answer

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 ".ResultFileFullScript.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 ".ResultFileFullScript.csv" -NoTypeInformation