Getting additional rows from power-shell Export-Excel command

Question

I have a powershell script below:

$DataVariable = Invoke-Sqlcmd -ServerInstance "B1" -Database "SQL4" -Query "SELECT * FROM Table1"

$DataVariable | Export-Excel  -Path $OutputFile -WorkSheetname "Robert" -Title "MG" -TitleFillPattern DarkDown 

If I run this script, am getting additional rows, i.e. RowError, Table, ItemArray, HasErrors.

How can I get rid of these rows?

Answer 1

tl;dr

I would say that your problem lies more with the Invoke-Sqlcmd command more than the Export-Excel command.

• only select the columns that you are interested in before piping them to Export-Excel.

• or, select everything and use -ExcludeProperty to remove what you
  do not want before piping to Export-Excel

I'm actually quite impressed that Export-Excel get's the values out of System.Data.DataRow type.

Throwing a modified version of your script into a variable and calling Get-Member on that shows us that the TypeName is System.Data.DataRow.

If we open up the MSDN page on that type, we see it has the following properties (highlighting is my own)...

HasErrors, Item()..., ItemArray, RowError, RowState, Table...

We can't see them using Get-Member but if we pipe our variable to Select-Object -Property * then we see those hidden guys!

I would say that your problem lies more with the Invoke-Sqlcmd command more than the Export-Excel command.
The quick workaround for this is to only select the columns that you are interested in before piping them to Export-Excel. Or, as pointed out by @Abhijith pk, select everything and using -ExcludeProperty to remove what you do not want

$dataVar | 
  Select Col1 | 
  Export-excel `
    -Path .\testing\datavar.xlsx `
    -WorkSheetname Doug `
    -Title DougSoCool `
    -TitleFillPatten DarkDown

HTH