How to output multiple regex matches from single string on one line

Our inventory application doesn’t provide way to produce the desired report.

By user, pc, pc specs, monitor1
…monitor2

The last field, monitor(s), will be in a child table because there are one or two monitors at each desk and, in addition to a report like the above, we wish to produce reports along the lines of how many of what type of monitor. A single table with two columns for monitors would make that difficult to produce.

CSV exported from the inventory application does have the data, but manipulation is needed to get monitor make and model info.

Most of the export data elements are a single value in a column. Monitors are multiple values in a single column and the export has five columns for monitors.

e.g. “Displays 1”, “Displays 2”, … , “Displays 5”.

There are label / value pairs in each Displays column. Labels are delimited with a leading ‘~’ and a trailing ': ', except the first label which has no leading ‘~’. Values are delimited with ‘”’ at each end.

Here’s a sample for column “Displays 1” from row 1 of the export file.

PS > $f = Import-Csv c:\path\source.csv

PS > $f[0].'Displays 1'

name: "VA2452 SERIES"~manufacturer: "ViewSonic Corporation"~description: "VSC.7931.01010101 (14/2017)"~serial_number: "UPY999999999"~display_type: "non-RGB multicolor"

I’ve been stumped by one thing and would appreciate any help or suggestions.

I want to create a monitors table from the source CSV that has columns for monitor details like Name, Manufacturer, Serial Number.

The problem I’m having is when I extract multiple values from a single line of text I cannot output it as a single line of text.

I’m using regex to parse the text of “Displays n” to get the info needed from the column. The difficulty I’m having is each value parsed from a column is output on a separate line. I need all parsed values for a given column to be on one line.

Using the sample column value above

PS > $regex = '(?<=name: ")([^"]+)|(? $f[0].'Displays 1' | Select-String -Pattern $regex -AllMatches | %{$_.Matches} | %{$_.Value}

VA2452 SERIES

ViewSonic Corporation

HOW to get this on one line??

Once that’s done, I’ll also want to put delimiters around each value since it needs to be in a format ready to import to a table.

you can either add the strings together or, as you mentioned delimiters, you can use -join:

$matchedstrings -join ', '

Thanks for your suggestion. I’ll try and see if I can make it work.

My post was altered before it was approved though (no idea why). My second code sample was changed. Don’t know if that would affect your answer.

The code originally was…

PS > $regex = '(?<=name: ")([^"]+)|(?<=manufacturer: ")([^"]+)'
PS > $f[0].'Displays 1' | Select-String -Pattern $regex -AllMatches | %{$_.Matches} | %{$_.Value}
VA2452 SERIES
ViewSonic Corporation

Joel,

I wanted to get back and thank you for pointing me in the right direction. -join was definitely what was needed.

Also, since there was more to what I was trying to make happen I figured I’d post the solution in case it might help someone else.

The inventory app exports a csv, one row per host, but some of the columns contain text strings that need to be parsed for discrete values. Like, the Displays column contains make, model, sn and more. And then there’s more than one Displays column, there’s five. The columns only have values if there’s a monitor. A one monitor system “Displays 1” has values and “Displays 2” through “Displays 5” are empty. A two monitor system “Displays 1” and “Displays 2” have values, and so on.

Needed to normalize that so there would be only one row per display with columns for the display’s attributes. And still wanted one row per host so all the other host data (hostname, RAM, CPU, …) doesn’t get duplicated when there’s more than one monitor.

Your -join suggestion was the final piece that let me put it all together.

The hosts table (-join not needed) is produced by importing a pc-list.txt file made as follows:
PS > $path = “”
PS > $f = Import-Csv $path.csv
PS > $delim = ‘,’
PS > $regexdrv = '(? $result = foreach ($r in 0…$($f.length-1)) {$f[$r].id+$delim+$f[$r].Name+$delim+$f[$r].‘IP Address’+$delim+$f[$r].OS+$delim+$f[$r].CPU+$delim+$f[$r].‘Last Update’+$delim +$f[$r].‘Computer Domain’+$delim+$f[$r].Memory+$delim+$f[$r].User+$delim+$f[$r].Site+$delim+$f[$r].SN+$delim+$f[$r].Model+$delim+$( $(foreach ($i in 1…5) { if ($f[$r].('Storages ’ + $i) -like ‘"~model: “//./PHYSICALDRIVE0”’) { ($f[$r].('Storages ’ + $i))}; } ) | Select-String -Pattern $regexdrv | %{$.Matches} | %{$.Value})}
PS > $result > pc-list.txt

Then the monitors file, where the -join is needed, is created:
PS > $path = “”
PS > $f = Import-Csv $path.csv
PS > $delim = ‘,’
PS > $regexdisp = '(?<=name: “)([^”]+)|(? $displays = foreach ($r in 0…($f.length-1)) { foreach ($c in 1…5) {if ($f[$r].('Displays ’ + $c) -ne ‘’) {($f[$r].id, $f[$r].name, $(($f[$r].('Displays ’ + $c) | Select-String -Pattern $regexdisp -AllMatches | %{$.Matches} | %{$.Value}) -join ‘,’)) -join ‘,’}} }
PS > $displays > displays.txt

Joel,

I wanted to get back and thank you for pointing me in the right direction. -join is definitely what was needed.

Also, since there was more to what I was trying to make happen I figured I’d post the solution in case it might help someone else.

The inventory app exports a csv, one row per host, but some of the columns contain text strings that need to be parsed for discrete values. Like, the Displays column contains make, model, sn and more. And then there’s more than one Displays column, there’s five. The columns only have values if there’s a monitor. A one monitor system “Displays 1” has values and “Displays 2” through “Displays 5” are empty. A two monitor system “Displays 1” and “Displays 2” have values, and so on.

Needed to normalize that so there would be only one row per display with columns for the display’s attributes. And still wanted one row per host so all the other host data (hostname, RAM, CPU, …) doesn’t get duplicated when there’s more than one monitor.

Your -join suggestion was the final piece that let me put it all together.

The hosts table (-join not needed) is produced by importing a pc-list.txt file made as follows:
PS > $path = “”
PS > $f = Import-Csv $path.csv
PS > $delim = ‘,’
PS > $regexdrv = '(? $result = foreach ($r in 0…($f.length-1)) {$f[$r].id+$delim+$f[$r].Name+$delim+$f[$r].‘IP Address’+$delim+$f[$r].OS+$delim+$f[$r].CPU+$delim+$f[$r].‘Last Update’+$delim +$f[$r].‘Computer Domain’+$delim+$f[$r].Memory+$delim+$f[$r].User+$delim+$f[$r].Site+$delim+$f[$r].SN+$delim+$f[$r].Model+$delim+$( $(foreach ($i in 1…10) { if ($f[$r].('Storages ’ + $i) -like ‘"~model: “//./PHYSICALDRIVE0”’) { ($f[$r].('Storages ’ + $i))}; } ) | Select-String -Pattern $regexdrv | %{$.Matches} | %{$.Value})}
PS > $result > pc-list.txt

Then the monitors file, where the -join is needed, is created:
PS > $path = “”
PS > $f = Import-Csv $path.csv
PS > $delim = ‘,’
PS > $regexdisp = ‘(?<=name: “)([^”]+)|(?<=manufacturer: “)([^”]+)’
PS > $displays = foreach ($r in 0…($f.length-1)) { foreach ($c in 1…5) {if ($f[$r].('Displays ’ + $c) -ne ‘’) {($f[$r].id, $f[$r].name, $(($f[$r].('Displays ’ + $c) | Select-String -Pattern $regexdisp -AllMatches | %{$.Matches} | %{$.Value}) -join ‘,’)) -join ‘,’}} }
PS > $displays > displays.txt