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.