How can I create a Powershell custom object like 'Import-CSV'

Question

I'm trying to pull a table from Microsoft Access and have it look like the Import-CSV tables in PowerShell, where each column in the CSV file becomes a property of the custom object and the items in rows become the property values. The only close success I've had is this:

$ColumnCount = 0;
$contactObject = new-object PSObject;
if ($rs.RecordCount -ne 0) {
    do { 
        $rs.MoveFirst()
        $valueArray = @();
        do {
            foreach ($columnHeader in $rs.fields.item($ColumnCount).name) {
                $value = $rs.Fields.Item($columnHeader).Value;
                $valueArray += $value      
            }
            $rs.MoveNext()
        } until ($rs.EOF -eq $True)
        $contactObject | Add-Member -MemberType NoteProperty -Name $columnHeader -Value $valueArray;    
        $ColumnCount++
    } until ($ColumnCount -eq $rs.fields.count)
}

And it outputs this:

EquipmentPackageID      ElementID           ArchitectureID    Delete                         

------------------      ---------           --------------    ------                         

{117, 117, 126, 126...} {32, 32, 32, 32...} {-1, 1, -1, 1...} {False, False, False, False...}

But I don't want individual arrays.

I want it all to be one table basically. And this function has to be able to accept different sized and named tables from access. So I can't create a custom object tailored to this 4 column table.

Answer 1

$resultsArray =  New-Object -TypeName System.Collections.ArrayList;
$ColumnCount = 0;
$contactObject = New-Object PSObject;

if ($rs.RecordCount -ne 0){

$rs.MoveFirst()

Do {
$ColumnCount = 0;
Do{ 
$columnHeader = $rs.fields.item($ColumnCount).name
$value = $rs.Fields.Item($columnHeader).Value;

$contactObject | add-member -membertype NoteProperty  -name $columnHeader  -
Value $value;  

$columncount++

}until($ColumnCount -eq $rs.fields.count)
  
 $resultsArray.add($contactObject) 
$rs.MoveNext()         
 $contactObject= New-Object PSObject; 
  
} until ($rs.EOF -eq $True)
      
   
}

}
   $resultsarray 

I did it!!!! The trick was to add the record to an array, and then clear the object, add-member, add to array, clear object..... Awesome!

Answer 2

The basic pattern for PowerShell v3.0+ is something like this:

$MyFiles = Get-ChildItem C:\Windows\ | ForEach-Object {
    [pscustomobject]@{
        Name = $_.Name;
        FullName = $_.FullName;
        CreationDate = $_.CreationTime.Date;
    }
}

Also, avoid this pattern:

$Array = @()
$x | ForEach-Object {
   $Record = [...]
   $Array += $Record
}

Basic .NET arrays can't be resized. That means $Array += [...] has to make a completely new array, copy everything, and then destroy the old array. The bigger the array gets, the more expensive this operation, the more memory it consumes, and the slower the script will be. It turns any ordinary O(n) complexity loop into an O(n^2) complexity loop.

That means you should either do this:

$Array = $x | ForEach-Object { [...] }

Or this:

$Array = New-Object -TypeName System.Collections.ArrayList;
$x | ForEach-Object {
   $Record = [...]
   $Array.Add($Record)
}

---

Here's an example of something that pulls headers dynamically. In this case, it pulls all properties of type String and DateTime for the first 10 files in C:\Windows\.

# Get the data
$Data = Get-ChildItem C:\Windows\ -File | Select-Object -First 10;

# Get the headers that meet our criteria
# It's not important what this code is doing, it's just establishing headers 
# dynamically
$Headers = $Data[0].PSObject.Properties |
    Where-Object TypeNameOfValue -in @('System.String', 'System.DateTime') | 
    Select-Object -ExpandProperty Name;

# Iterate through the Data
$Results = $Data | ForEach-Object {
    # Create a hash table to save the properties in.
    $Record = @{};

    # For each header, create a property in the hash table and assign it the corresponding value from the object in the stream
    foreach ($Header in $Headers) {
        $Record.$Header = $_.$Header;
    }

    # Convert the hash table we've saved to a PSCustomObject
    [PSCustomObject]$Record;
}

Unfortunately, I don't know what $rs is or how to create one. I assume it's something like an ADO RecordSet, but your logic escapes me somewhat. (Namely, $rs.MoveFirst() looks like it's in the wrong loop if it does what I remember RecordSet.MoveFirst() doing, but I may be misremembering.)

The idea here is the same, however.