Fastest way to convert/process Datatable to Custom PSObject

Question

I'm able to fill a datatable, $devices from a MySql query (for example):

PS C:\Users\MKANET\Desktop\dev\lab> $devices.table[10]

name                                    ip                                          mac                                    vendor
----                                    --                                          ---                                    ------
ComputerTest                            10.51.18.6                                  fd1969ff4cb9                           HewlettP

I'd like to convert that datatable type into a custom PSObject; where, the mac column is converted to PSObject NoteProperty name "Mac-Address"; and, respective values converted to 00-00-00-00 string format:

PS C:\Users\MKANET\Desktop\dev\lab> $devices[1]

name                               ip                                      MAC-Address                             vendor
-------                            --                                      -----------                             ------------
ComputerTest                       10.51.18.6                              fd-19-69-ff-4c-b9                       HewlettP

Considering these datatables may be relatively large (may have a couple hundred rows), I need the fastest processing method to do this in Powershell.

If it'll make the converting/modifying process significantly faster, I'd be happy with keeping $Devices as a Datatable; only modifying/processing the values for the "mac" column to: 00-00-00-00-00-00 text format, permanently.

Answer 1

Using Select-Object, it should auto-convert a DataRow / DataTable to a PSCustomObject in Powershell. Here's what I would run to do the conversion to PSCustomObject and handle the MAC address formatting in one line. This can be applied to a single row at specific index (like the example code) or against the entire DataTable to convert to an array of PSObjects.

The MAC address formatting removes any non-alphanumeric characters in the original, casts to lowercase, then inserts the hyphens at the appropriate indexes.

$devices.table[10] | Select-Object name,ip,@{N="MAC-Address";E={((((($_.mac -replace '[^a-zA-Z0-9]','').ToLower().insert(2,"-")).insert(5,"-")).insert(8,"-")).insert(11,"-")).insert(14,"-")}},vendor

Answer 2

Just to be clear for some readers: within PowerShell, a DataTable is a PS object, just one with a [System.Data.DataTable] type. This type (class) provides a number of methods for manipulating the object such as adding a new column (shown in the answer provided by @e-z-hart).

Converting an object to a generic 'PSObject' effectively means stripping away all the additional methods, etc. associated with the [DataTable] class and leaving just a simple object. You could do this as follows:

$obj = $devices.table[10] | foreach {

  $macaddress = ($_.mac -replace '(..)','$1-').TrimEnd("-")

  New-Object -TypeName PSObject -Property @{
    "name         = [System.String]        $_.name
    "ip"          = [System.Net.IPAddress] $_.ip
    "mac-address" = [System.String]        $macaddress
    "vendor"      = [System.String]        $_.vendor
  }
}

Hopefully the -replace operator with a regex is a little more elegant (cf. answer from @e-z-hart) than having to chop the mac address up with substring functions. Someone may be able to improve on the regex so that it isn't necessary to Trim the trailing "-" at the end.

This should be pretty fast for the "couple of hundred" rows you mention.

Note that, for bonus points, the 'ip' column is converted to a [System.Net.IPAddress] type, which includes a ToString() method to return the address in the dotted format we usually see them in. I suppose this is overkill so you'd probably want this to be a [System.String] throughout.

Finally, if you really want the MAC-Address column to be a NoteProperty, then I'd exclude that column from the list of properties in the New-Object cmdlet and use Add-Member. You'll could assign the output of New-Object to a variable and then pass that to Add-Member or just try to do it in one go with the output of New-Object pipelined to Add-Member:

New-Object ... | Add-Member -MemberType NoteProperty -Name "Mac-Address" -Value $macaddress

powershell datatable object psobject