Why does PowerShell add extra information when exporting to Excel?

Question

PowerShell

Good afternoon! there is a code

$Connection = "Server=localhost\SQLEXPRESS;Database=msdb;Trusted_Connection=True;"
  $InputFile  = "C:\Users\user\TestScripts\SQLQuery2.sql"
  $ExportFile = "C:\Users\user\TestScripts\Report1.xls"
  $Sheatnamber = 0 

  $result = Invoke-Sqlcmd -InputFile $InputFile  -OutputAs Dataset -ConnectionString $Connection
  
  
  $t = $result.Tables.Count

  
  for ($i = 0; $i -lt $t; $i++)
{ 
   
   $Sheatnamber = $Sheatnamber + 1
   $sheatname = "Запрос " + $Sheatnamber

   $result.Tables.Item($i) | Export-Excel -WorksheetName $sheatname -Path  $ExportFile -AutoSize -Append

}

it works and everything is fine with it, but the problem is that, in addition to the data, it also exports extra information

. Export-csv has the -NoTypeInformation parameter, Export-Excel doesn’t have this or I didn’t search well. But maybe I somehow incorrectly transfer the array for export.

Answer 1

Because DataRow has these properties
https://docs.microsoft.com/en-us/dotnet/api/system...
They can be excluded like this

$result.Tables.Item($i) | Select-Object -Property * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | Export-Excel