PowerShell filtering out empty results?

Question

PowerShell

Good afternoon!
please tell me how to select "$result.Tables" which contain more than one record, since one record is the headers of the query results from the SQLQuery1.sql file, but in fact they do not have a query result.

$Connection = "Server=localhost\SQLEXPRESS;Database=msdb;Trusted_Connection=True;"
  $InputFile  = "C:\Users\TestScripts\SQLQuery1.sql"
  $ExportFile = "C:\Users\TestScripts\Report.xls"
  $Sheatnamber = 0 
  
  $result = Invoke-Sqlcmd -InputFile $InputFile  -OutputAs Dataset -ConnectionString $Connection 

  $t =  $result.Tables.Count

  $t 

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

   $result.Tables.DataRow.Item($i)  | Select-Object -Property * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors  |Export-Excel -WorksheetName $sheatname -Path  $ExportFile -AutoSize

}

Answer 1

so, your query returns several tables
$result.Tables.Count is the number of tables.
They can be listed as
$result.Tables[0], $result.Tables[1], etc.
Each table has rows
$result.Tables[0].DataRow,. $result.Tables[1].DataRow
Each Datarow is a separate object, it also has its own Count - $result.Tables[0].DataRow.Count
In your code you take all rows from all tables at once and write them to Excel. By the way, it is not recommended to do this inside a loop that goes through the lines.
You need to run not by rows, but by tables and check if there is data - write (and without any for, this is the last century :) )
That is, something like:

foreach ($table in $result.Tables) {
  if ($table.DataRow.Count) {
    $table.DataRow | ... | Export-Excel ...
  }
}

I can lie with the exact names of the properties, maybe it’s just Rows, I don’t remember now, but I gave links to one of your last questions.
The easiest way is to do it line by line and see what's inside each of the objects