I am trying to add a column to data I have imported (and will export) as a CSV.

My data looks like this:

I am importing a CSV:

What I want to do add another column, perhaps "10/15/22" when the process runs, and then update the values under that date.

In effect, the document will grow to the right, adding a column each time it is run.

I have an array called $test. It will have values like

$test.users = "7"
$test.SomeBSValue = "22"
$test.Computers = "52"

When all is said and done, my output should look like:

Adding to the list any values I have that are not part of the list already, but recording the values I have under the heading for the date.

So, if the script is run and collects 100 data points, those data point would all be in the CSV under the date.

I would have thought this would be easy, but now I am drawing a complete blank.

I've considered (but have not coded) even trying to put into a GUI grid view and then reading the data back and writing the CSV (but there should be an easier way, right?)

CodePudding user response：

Since you don't actually use it as a CSV we can treat it like regular content.

Say we have a file in C:\test called test.csv that looks as follows:

"Settings","08/15/22","09/15/22"
"Users",0,0
"Computers",0,1
"SomeValue1",0,2
"SomeValue2",0,2
"SomeValue3",0,2
"Stat1",0,10
"Stat2",7,0
"Stat3",0,0
"SomeBSValue",1,2

We can import it, add the row from the object to each corresponding row and right the file to test2.csv.

$test = @{
    Settings = "10/15/22"
    users = "7"
    Computers = "52"
    SomeValue1 = "22"
    SomeValue2 = "24"
    SomeValue3 = "25"
    Stat1 = "4"
    Stat2 = "3"
    Stat3 = "2"
    SomeBSValue = "1"
}

$content = Get-Content "C:\test\test.csv"
$newContent = @()
foreach($row in $content){
    foreach($key in $test.Keys){
        if($row -like "*$key*"){
            $row = $row   ","   $test."$key"
            $newContent  = $row
        }
    }
}

$newContent | Out-File "C:\test\test2.csv"

After running the script it will have added the values from the object:

"Settings","08/15/22","09/15/22",10/15/22
"Users",0,0,7
"Computers",0,1,52
"SomeValue1",0,2,22
"SomeValue2",0,2,22
"SomeValue3",0,2,22
"Stat1",0,10,4
"Stat2",7,0,4
"Stat3",0,0,4

Edit: If you want the date between quotes, replace $row = $row "," $test."$key" with this:

if($key -eq "Settings"){
    $row = $row   ","   '"'   $test."$key"   '"'
}else{
    $row = $row   ","   $test."$key"
}

CodePudding user response：

This idea is pretty terrible idea, as you stated, "grow to the right" is definitely not a good approach and you should consider a better way of doing it, data should always expand vertically.

As for the solution, you can create new columns easily with Select-Object and dynamically generated calculated properties.

Note, this should definitely not be considered an efficient approach. This will be slow because Select-Object is slow.

function Add-Column {
    param(
        [Parameter(ValueFromPipeline, DontShow, Mandatory)]
        [object] $InputObject,

        [Parameter(Mandatory)]
        [string] $ColumnName,

        [Parameter(Mandatory)]
        [string] $ReferenceProperty,

        [Parameter(Mandatory)]
        [hashtable] $Values
    )

    begin {
        $calculatedProp = @{ N = $ColumnName }
    }
    process {
        $calculatedProp['E'] = { 0 }
        if($value = $InputObject.$ReferenceProperty) {
            if($Values.ContainsKey($value)) {
                $calculatedProp['E'] = { $Values[$value] }
            }
        }
        $InputObject | Select-Object *, $calculatedProp
    }
}

• Usage

Import-Csv path\to\csv | Add-Column -ColumnName '09/15/22' -ReferenceProperty Settings -Values @{
    users       = "7"
    SomeBSValue = "22"
    Computers   = "52"
}

• Result

Settings    08/15/22 09/15/22
--------    -------- --------
Users       0        7
Computers   0        52
SomeValue1  0        0
SomeValue2  0        0
SomeValue3  0        0
Stat1       0        0
Stat2       7        0
Stat3       0        0
SomeBSValue 1        22

This function allows then pipe into Export-Csv at ease:

Import-Csv path\to\csv | Add-Column ... | Export-Csv path\to\newCsv