Powershell – Manipulating CSV Columns

January 9, 2024

I am trying to manipulate a csv file column and can not get it to find the column data.

My CSV file looks like this:

"From","To","Meter Number","Meter Name","Volume","Energy","1","2","3","4","5","6","7"
1,"32,360.0",10.601100,"343,051.6","73,553.0",10.462900,"769,577.7",0.0 ,10.470900,0.0,,,
2,"32,230.0",10.604000,"341,766.9","74,824.0",10.471300,"783,504.6",0.0 ,10.468100,0.0,,,
...
30,"17,769.0",10.440000,"185,508.4","63,591.0",10.452500,"664,684.9",0.0 ,10.445900,0.0,,,
31,"26,138.0",10.451000,"273,168.2","79,722.0",10.458300,"833,756.6",0.0 ,10.451400,0.0,,,

The first column shows the day of the month and I want to turn it into a full datetime field.

My code goes something like this:

 Import-Csv $RH | ForEach-Object {
        write-host $RH.'From'
            $DateTime = (date).AddMonths(-1).Month.ToString() + '/' + $RH.'From'.ToString() + '/' +  (date).AddMonths(-1).Year.ToString() + ' 09:00:00'
            
#            Write-Host $DateTime

            }

The first Write-host produces a blank column (should be ‘1’) and the $DateTime variable fails with error: You cannot call a method on a null-valued expression.

What am I doing wrong?

>Solution :

Your Import-CSV isn’t quite right, and as a result, neither is your ForEach-Object.

Assume, arguendo, that your CSV file is in the file READINGS.CSV.

If you want to process it via the pipeline, you would use

Import-CSV "READINGS.CSV" | ForEach-Object {
    Do-Something $_.From
    Do-SomethingElse $_."Meter Name"
...etc.
...etc.
}

The pseudovariable $_ represents "the current object passed into the pipeline".

If you wanted to save the CSV into an array, and then process the array, you would use

$MyArray = Import-CSV "READINGS.CSV"
Do-SomethingWithArray -Array $MyArray
...etc.
...etc.
$MyArray | ForEach-Object {
   Do-Something -From $_.From -To $_.To -Energy $_.Energy -One $_.1
   ...etc.
   ...etc.
}