How to get PowerShell convertto-html cmdlet to output the same columns as convertto-csv

I've got a table of data in powershell. When I inspect it directly, it has exactly the columns I want. When I output it with convertto-csv, it has exactly the columns I want. However, when I output it with convertto-html, it has six unwanted columns ( Database, RowError, RowState, Table, ItemArray, HasErrors ). How do I get it to output only the columns I want WITHOUT specifying the wanted columns by name?

NOTE: I kept this example simple, but the list of wanted names is usually quite long and changes often, so updating the list of WANTED columns every time would be a huge (error-prone) pain.

Details on the queries and examples are below in the code section.
PS C:\Monitoring> $alltables[5] | get-member


   TypeName: System.Data.DataRow

Name              MemberType            Definition
----              ----------            ----------
AcceptChanges     Method                System.Void AcceptChanges()
BeginEdit         Method                System.Void BeginEdit()
CancelEdit        Method                System.Void CancelEdit()
ClearErrors       Method                System.Void ClearErrors()
Delete            Method                System.Void Delete()
EndEdit           Method                System.Void EndEdit()
Equals            Method                bool Equals(System.Object obj)
GetChildRows      Method                System.Data.DataRow[] GetChildRows(string relationName), System.Data.DataRow...
GetColumnError    Method                string GetColumnError(int columnIndex), string GetColumnError(string columnN...
GetColumnsInError Method                System.Data.DataColumn[] GetColumnsInError()
GetHashCode       Method                int GetHashCode()
GetParentRow      Method                System.Data.DataRow GetParentRow(string relationName), System.Data.DataRow G...
GetParentRows     Method                System.Data.DataRow[] GetParentRows(string relationName), System.Data.DataRo...
GetType           Method                type GetType()
HasVersion        Method                bool HasVersion(System.Data.DataRowVersion version)
IsNull            Method                bool IsNull(int columnIndex), bool IsNull(string columnName), bool IsNull(Sy...
RejectChanges     Method                System.Void RejectChanges()
SetAdded          Method                System.Void SetAdded()
SetColumnError    Method                System.Void SetColumnError(int columnIndex, string error), System.Void SetCo...
SetModified       Method                System.Void SetModified()
SetParentRow      Method                System.Void SetParentRow(System.Data.DataRow parentRow), System.Void SetPare...
ToString          Method                string ToString()
Item              ParameterizedProperty System.Object Item(int columnIndex) {get;set;}, System.Object Item(string co...
% Free            Property              System.Decimal % Free {get;set;}
% Used            Property              System.Decimal % Used {get;set;}
CustomerName      Property              System.String CustomerName {get;set;}
CustomerNumber    Property              System.String CustomerNumber {get;set;}
Database          Property              System.String Database {get;set;}
Free (MB)         Property              System.Decimal Free (MB) {get;set;}
Size (MB)         Property              System.Decimal Size (MB) {get;set;}
Tablespace        Property              System.String Tablespace {get;set;}

*************************************
* NOTE THE LIST OF PROPERTIES ABOVE *
*************************************
PS C:\Monitoring> $alltables[5] | convertto-csv
#TYPE System.Data.DataRow
"Tablespace","Size (MB)","Free (MB)","% Free","% Used","CustomerNumber","CustomerName","Database"
"UNDOTBS1","1000","977.6250","98","2","1249","A Fake Database","AFD"

**********************************************************************
* THE CSV LISTED THE PROPERTIES SHOWN BY GET-MEMBER AND NOTHING ELSE *
**********************************************************************


PS C:\Monitoring> $alltables[5] | convertto-html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
</colgroup>
<tr><th>Tablespace</th><th>Size (MB)</th><th>Free (MB)</th><th>% Free</th><th>% Used</th><th>CustomerNumber</th><th>CustomerName</th><th>Database</th><th>RowError</th><th>RowState</th><th>Table</th><th>ItemArray</th><th>HasErrors</th></tr>
<tr><td>UNDOTBS1</td><td>1000</td><td>977.6250</td><td>98</td><td>2</td><td>1249</td><td>A Fake Database</td><td>AFD</td><td></td><td>Modified</td><td>Table</td><td>System.Object[]</td><td>False</td></tr>
</table>
</body></html>

***********************************************
* CONVERTTO-HTML ADDED SIX "EXTRA" PROPERTIES *
***********************************************

Open in new window

jhutchinst2systemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason WatkinsIT Project LeaderCommented:
Howabout; ConvertTo-Html Name,MemberType,Definition
jhutchinst2systemsAuthor Commented:
Those are just the columns on get-member. I included that to show the properties in the object.
The columns I want in the HTML table are the same ones in the CSV ("Tablespace","Size (MB)","Free (MB)","% Free","% Used","CustomerNumber","CustomerName","Database"). However, as I noted, this was only an example. My problem is that the convertto-html cmdlet adds the six extra columns to every instance. It isn't practical to add the columns by name each time, particularly when there are 50 or 100 instead of the 8 in this _example_.
Jason WatkinsIT Project LeaderCommented:
SD-WAN: Making It Work for You

As bandwidth requirements and Internet costs grow, businesses naturally want to manage budgets by reducing reliance on their most expensive connection types. Learn more about how to make SD-WAN work for your business in our on-demand webinar!

jhutchinst2systemsAuthor Commented:
That page is a great reference. I've used it in the creation of a number of my scripts. Unfortunately it doesn't address this issue. At the heart of it is that I've got a collection of objects that produce different output with the convertto-csv and convertto-html cmdlets. I want convertto-html to produce the same output (though in a different format) as convertto-csv. In other words, without the "extraneous" columns that don't show up in get-member or convertto-csv.
Chris DentPowerShell DeveloperCommented:

How are you generating $alltables? Via SqlClient?

I want to see if I can reproduce this.

Chris
jhutchinst2systemsAuthor Commented:
The background on this is that I'm querying 100's of separate Oracle databases to gather statistics. The entire script is below. The good news is that I've found a workaround that produces EXACTLY what I want, but it is an UGLY workaround. There HAS to be a more elegant way to fix this than converting to/from CSV. I'm hoping that somebody on Experts-Exchange can suggest a better way to do this.


$query = "SELECT /* + RULE */  df.tablespace_name `"Tablespace`",`r`n"
$query += "       df.bytes / (1024 * 1024) `"Size (MB)`",`r`n"
$query += "       SUM(fs.bytes) / (1024 * 1024) `"Free (MB)`",`r`n"
$query += "       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) `"% Free`",`r`n"
$query += "       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) `"% Used`"`r`n"
$query += "  FROM dba_free_space fs,`r`n"
$query += "       (SELECT tablespace_name,SUM(bytes) bytes`r`n"
$query += "          FROM dba_data_files`r`n"
$query += "         GROUP BY tablespace_name) df`r`n"
$query += " WHERE fs.tablespace_name (+)  = df.tablespace_name`r`n"
$query += " GROUP BY df.tablespace_name,df.bytes `r`n"
$query += "UNION ALL `r`n"
$query += "SELECT /* + RULE */ df.tablespace_name tspace, `r`n"
$query += "       fs.bytes / (1024 * 1024),`r`n"
$query += "       SUM(df.bytes_free) / (1024 * 1024),`r`n"
$query += "       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),`r`n"
$query += "       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)`r`n"
$query += "  FROM dba_temp_files fs,`r`n"
$query += "       (SELECT tablespace_name,bytes_free,bytes_used`r`n"
$query += "          FROM v`$temp_space_header`r`n"
$query += "         GROUP BY tablespace_name,bytes_free,bytes_used) df`r`n"
$query += " WHERE fs.tablespace_name (+)  = df.tablespace_name`r`n"
$query += " GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used`r`n"
$query += " ORDER BY 4 DESC`r`n"

$databases=import-csv "DATABASE_LIST.csv"

foreach($row in $databases)
{
 
    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | out-null

    $connection = new-object system.data.oracleclient.oracleconnection( `
        "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="+$row.server+")(PORT=1521)) `
        (CONNECT_DATA=(SERVICE_NAME="+$row.database+")));User Id="+$row.username+";Password="+$row.password+";");

    $set = new-object system.data.dataset
    $adapter = new-object system.data.oracleclient.oracledataadapter ($query, $connection)
    $adapter.Fill($set)
    $table = new-object system.data.datatable
    $table = $set.Tables[0]

    $table.columns.add("CustomerNumber")
    $table.columns.add("CustomerName")
    $table.columns.add("Database")

    foreach($tablerow in $table)
    {
        $tablerow.CustomerNumber=$row.CustomerNumber
        $tablerow.CustomerName=$row.CustomerName
        $tablerow.database=$row.database
    }

    $alltables+=$table
}

#Fails (has extra columns):
$BadHTML = $alltables | convertto-html

#Works (ugly workaround that produces what I want):
$GoodHTML = $alltables | convertto-csv | convertfrom-csv | convertto-html

$BadHTML > BadResult.htm
$GoodHTML > GoodResult.htm

Open in new window

Chris DentPowerShell DeveloperCommented:

Ah, okay, to be unhelpful, I don't have any Oracle servers I can use the class with.

Have you tried initialising $alltables with "$alltables = @()" first at all? I don't imagine it'll make a lot / any difference, but it's nice to make sure.

Otherwise I can't help but thinking it must be inheriting properties from the base object. With that in mind, can you see if the properties it's picking up are here:

$alltables[0].PsBase | Get-Member

I'm curious to see if those it lists above come from this class:

http://msdn.microsoft.com/en-us/library/system.data.datarow_members%28v=VS.100%29.aspx

Obviously this kind of behaviour isn't desirable. Unfortunately we can only guess about the decisions made by each of the ConvertTo CmdLets.

Once we've established where they come from we can look at dropping them as neatly as possible.

Chris
jhutchinst2systemsAuthor Commented:
Looks like that's where they came from. Details are in the snippet. So now the question is how to get the convertto-html cmdlet to omit them the way the convertto-csv cmdlet does.

BTW... Sorry for the slow reply. We had our annual company meeting last week. That had me occupied from about 7A-10P each day.
PS C:\Users\administrator.T2HOSTED> $alltables[0].PsBase | Get-Member


   TypeName: System.Management.Automation.PSMemberSet

Name              MemberType            Definition
----              ----------            ----------
AcceptChanges     Method                System.Void AcceptChanges()
BeginEdit         Method                System.Void BeginEdit()
CancelEdit        Method                System.Void CancelEdit()
ClearErrors       Method                System.Void ClearErrors()
Delete            Method                System.Void Delete()
EndEdit           Method                System.Void EndEdit()
Equals            Method                bool Equals(System.Object obj)
GetChildRows      Method                System.Data.DataRow[] GetChildRows(string relationName), System.Data.DataRow...
GetColumnError    Method                string GetColumnError(int columnIndex), string GetColumnError(string columnN...
GetColumnsInError Method                System.Data.DataColumn[] GetColumnsInError()
GetHashCode       Method                int GetHashCode()
GetParentRow      Method                System.Data.DataRow GetParentRow(string relationName), System.Data.DataRow G...
GetParentRows     Method                System.Data.DataRow[] GetParentRows(string relationName), System.Data.DataRo...
GetType           Method                type GetType()
HasVersion        Method                bool HasVersion(System.Data.DataRowVersion version)
IsNull            Method                bool IsNull(int columnIndex), bool IsNull(string columnName), bool IsNull(Sy...
RejectChanges     Method                System.Void RejectChanges()
SetAdded          Method                System.Void SetAdded()
SetColumnError    Method                System.Void SetColumnError(int columnIndex, string error), System.Void SetCo...
SetModified       Method                System.Void SetModified()
SetParentRow      Method                System.Void SetParentRow(System.Data.DataRow parentRow), System.Void SetPare...
ToString          Method                string ToString()
Item              ParameterizedProperty System.Object Item(int columnIndex) {get;set;}, System.Object Item(string co...
HasErrors         Property              System.Boolean HasErrors {get;}
ItemArray         Property              System.Object[] ItemArray {get;set;}
RowError          Property              System.String RowError {get;set;}
RowState          Property              System.Data.DataRowState RowState {get;}
Table             Property              System.Data.DataTable Table {get;}

Open in new window

Chris DentPowerShell DeveloperCommented:

Apologies for the late reply.

I've not had much luck getting rid of these, I can offer is an explicit exclusion of the properties:

$HTML = $alltables | Select-Object * -Exclude HasErrors, ItemArray, RowError, RowState, Table | ConvertTo-Html

Or potentially expansion of the ItemArray property (although I haven't tested this one):

$HTML = $alltables | Select-Object -Expand ItemArray | ConvertTo-Html

It's somewhat similar to the handling of the DataSet objects returned in this question I was working on the other day:

http://www.experts-exchange.com/Q_26107235.html

Chris

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.