Powershell dynamically move through field names and values

Question

For all the fields and values, starting with "LOAD" and ending with Weight_KGS, I want to create one html table (2 columns: Description & Value/content). But I don't want to hardwire 22 lines of code (x 2)

My array looks as follows: (below is just 1 line)

PS C:\WINDOWS\system32> $Emails

-----------------------------------------
ID                     : Xtest

Subject                : LOAD 85465494557 - TO 37654004

LOAD                   : 8594557

TransportOrder         : 37941004

DeliveryNote           : 

BusinessCase           : Full

ServiceProvider        : Me

PickupCountry          : FR

Pickup_ID              : 2546s

PickupName             : YoKo

PickupStreet           : Mani 12

PickupZipCode          : 25320

PickupCity             : Frankfurt

DeliveryName           : ManiMani

DeliveryDock           : LeftGreen

ReferenceNumber        : 

ServiceLevel           : Prio

TransportMode          : FTL

PickupDate             : 7/8/2020 00:00:00

PickupTime             : 30/12/2020 00:00:00

DeliveryDate           : 7/8/2020 00:00:00

DeliveryTime           : 30/12/2020 14:34:00

LoadingMeter_mtr       : 11.5

Weight_kgs             : 2560

Comment_TO             : I am new to Powershell

Comment_LOAD           : PowershellTest

--------------end data for the first record

For all the fields and values, starting with "LOAD" and ending with Weight_KGS, I want to create one html table (2 columns: Description & Value/content).

I can hardcode the field and the value 22 times: e.g. Write-output "LOAD" $emails.Load but that seems stupid. I am looking for a snippet that for example looks like this:

$startingfield=3
Do while $startingfield<25
     $FieldNM = $emails.FieldName[$startingfield]
     $FieldVAL = $emails.FieldValue[$startingfield]
     Write-output $emails.fieldNM
     Write-output $emails.fieldVAL
     $startingfield=$startingfield+1
   Loop

I apologize as I am new to both writing power-shell scripts and writing questions (I hope someone can help me and point me in the right direction as I have been reading and searching for hours, but I can find the proper search words, ending in the same useless videos)

Many thanks for taking the time to read this far

What does `$emails.GetType()` give, and what do you get from `$emails | Get-Member -Type Properties`? Iterating over members or collections in PowerShell is fairly simple, but depends a little on the exact nature of the object. Depending on an exact order that you index is inadvisable in any case -- you should define what you want in terms of all the properties you want, or the properties you do not want, depending on what set is more appropriate (for example, "leave out `ID`, `Subject` and everything that starts with `Comment_`" is something you can code). — Jeroen Mostert, Aug 17 '20 at 22:09

Doug Maurer · Answer 1 · 2020-08-18T03:32:42.863

Here is one way you can parse the data you've specified. It will treat the data as a single string and match from LOAD until before Comment_TO. We then prepare the data by replacing the semicolons with equals signs and let ConvertFrom-StringData do it's magic.

For the first test we'll store the data in a here-string.

$data = @'
-----------------------------------------
ID                     : Xtest

Subject                : LOAD 85465494557 - TO 37654004

LOAD                   : 8594557

TransportOrder         : 37941004

DeliveryNote           : 

BusinessCase           : Full

ServiceProvider        : Me

PickupCountry          : FR

Pickup_ID              : 2546s

PickupName             : YoKo

PickupStreet           : Mani 12

PickupZipCode          : 25320

PickupCity             : Frankfurt

DeliveryName           : ManiMani

DeliveryDock           : LeftGreen

ReferenceNumber        : 

ServiceLevel           : Prio

TransportMode          : FTL

PickupDate             : 7/8/2020 00:00:00

PickupTime             : 30/12/2020 00:00:00

DeliveryDate           : 7/8/2020 00:00:00

DeliveryTime           : 30/12/2020 14:34:00

LoadingMeter_mtr       : 11.5

Weight_kgs             : 2560

Comment_TO             : I am new to Powershell

Comment_LOAD           : PowershellTest
'@

Now let's process it

$data -match '(?s)^LOAD.*(?=Comment_TO)' | foreach {

    $ht = [ordered]@{}

    $Matches.0 -split [Environment]::NewLine |
        Foreach-Object{$_ -Replace "(?<=\D):","=" |
            ConvertFrom-StringData | Foreach-Object{$ht += $_}}

    [PSCustomObject]$ht

} -OutVariable results

Output

LOAD             : 8594557
TransportOrder   : 37941004
DeliveryNote     : 
BusinessCase     : Full
ServiceProvider  : Me
PickupCountry    : FR
Pickup_ID        : 2546s
PickupName       : YoKo
PickupStreet     : Mani 12
PickupZipCode    : 25320
PickupCity       : Frankfurt
DeliveryName     : ManiMani
DeliveryDock     : LeftGreen
ReferenceNumber  : 
ServiceLevel     : Prio
TransportMode    : FTL
PickupDate       : 7/8/2020 00:00:00
PickupTime       : 30/12/2020 00:00:00
DeliveryDate     : 7/8/2020 00:00:00
DeliveryTime     : 30/12/2020 14:34:00
LoadingMeter_mtr : 11.5
Weight_kgs       : 2560

The output is shown but also stored in $results - to access each property just use dot notation

PS C:\> $results.load
8594557

PS C:\> $results.weight_kgs
2560

PS C:\> $results.PickupDate
7/8/2020 00:00:00

Here's the same thing but reading from a file - note the -Raw parameter.

(get-content $filestoprocess -Raw) -match '(?s)^LOAD.*(?=Comment_TO)' | foreach {

    $ht = [ordered]@{}

    $Matches.0 -split [Environment]::NewLine |
        Foreach-Object{$_ -Replace "(?<=\D):","=" |
            ConvertFrom-StringData | Foreach-Object{$ht += $_}}

    [PSCustomObject]$ht

} -OutVariable results

Now that it’s in objects you can focus on making the HTML

Powershell dynamically move through field names and values

1 Answers1