If multiple CSV columns equal 0, replace other column with value from the same CSV

PowerShell Help
21

You have to import your CSV data with Import-Csv!! :wink:

22

Durrr… Of course, silly me. Changes made. File gets created from the following code, but changes still aren’t made and there is a new row added to the top of my file that reads,

#TYPE Selected.System.Management.Automation.PSCustomObject

Here’s the new code. I also changed the value to “0.00” from 0 just in case it made a difference, because the actual value in the file is 0.00

Import-Csv -Path "D:\autoloader\StrathconaTT\Strathcona Truck Tickets.csv"|
Select-Object -ExcludeProperty 'Override Water Volume' -Property *, 
@{
    Name       = 'Override Water Volume'
    Expression = {
        if ($_.'Override Oil/Condensate Volume' -eq "0.00" -and 
            $_.'Override Water Volume' -eq "0.00" -and 
            $_.'Override Sand Volume' -eq "0.00") {
            $_.'Volume'
        }
        else {
            $_.'Override Water Volume'
        }
    }
}|
Export-Csv -Path "D:\autoloader\StrathconaTT\new.csv"
23

Fixed the first line, because I’m a bonehead and didn’t put -notypeinformation after the export path, but the values still aren’t getting amended.

Import-Csv -Path "D:\autoloader\StrathconaTT\Strathcona Truck Tickets.csv"|
Select-Object -ExcludeProperty 'Override Water Volume' -Property *, 
@{
    Name       = 'Override Water Volume'
    Expression = {
        if ($_.'Override Oil/Condensate Volume' -eq "0.00" -and 
            $_.'Override Water Volume' -eq "0.00" -and 
            $_.'Override Sand Volume' -eq "0.00") {
            $_.'Volume'
        }
        else {
            $_.'Override Water Volume'
        }
    }
}|
Export-Csv -Path "D:\autoloader\StrathconaTT\new.csv" -notypeinformation
24

OMG, Ok I am a real bonehead! @rob-simmers @Olaf

Ok, so Rob’s code totally worked. BUT it created the Override Water Volume column at the end of the file (last column). Is there a way I can get it back into the original location? The import definition of this file needs the header in exact order.

Thanks in advance! Then I will disappear under a rock and learn more Powershell so I don’t have bother you guys with rudimentry problems.

Melissa

25

Actually no. At least not with this approach. You could bring it to the front by swapping the * with the calculated expression. But if you need the original order you have to provide the properties in this order.

Edit:

You could try to add another Select-Object and provide it with the $Headers from the original file.

26

So list them back out rather than *, and in that case I dont Exclude that column, correct? Like this:

Import-Csv -Path "D:\autoloader\StrathconaTT\Strathcona Truck Tickets.csv"|
 Select-Object "Record",
           "Label",
           "Date",
           "Sendind Location",
           "Receiving Location",
           "Truck Company",
           "Ticket Number",
           "Volume",
           "BSW Cut",
           "Sand Cut",
           "Free Water",
           "Remarks",
           "Observed Temperature",
           "Pressure of Sample",
           "Observed Density",
           "Temperature of Opening Level",
           "Opening Level Pressure",
           "Override Oil/Condensate Volume",
           "Override Water Volume",
           "Override Sand Volume",
           "Ticket Sub-Number",
           "User Text 1",
           "User Text 2",
           "User Text 3",
           "User Text 4",
           "User Text 5",
           "User Number 1",
           "User Number 2",
           "User Number 3",
           "User Number 4",
           "User Number 5",
           "User Date 1",
           "User Date 2",
           "User Date 3",
           "User Date 4",
           "User Date 5",
           "Truck Unit Number",
           "Exclude from Production?",
           "Seal Name",
           "Seal Off",
           "Seal On",  
@{
    Name       = 'Override Water Volume'
    Expression = {
        if ($_.'Override Oil/Condensate Volume' -eq "0.00" -and 
            $_.'Override Water Volume' -eq "0.00" -and 
            $_.'Override Sand Volume' -eq "0.00") {
            $_.'Volume'
        }
        else {
            $_.'Override Water Volume'
        }
    }
}|
Export-Csv -Path "D:\autoloader\StrathconaTT\new.csv" -notypeinformation
27

No.

Next time before you post a question you try the code, ok? :wink: If it does not do what you expect you play a little bit with it. If you get really stuck you come here and ask.

You place the calculated property at that place where you had the original column. :man_shrugging:t4:

Or you try the solution with the -Exclude and add another Select-Object with the extracted $Headers we’ve had before in this thread. :wink:

28

Ok, I will learn how to communicate on this forum one day yet. The last thing I want to do is make anyone upset with my questions. The only time I come here is when I am at my witts end banging my head against a wall and nobody I work with can even begin to help because they don’t understand any of it at all. So I appreciate all the help you’ve given me.

I ended up using the original code that put it at the end of the file and then using a Select-Object with the original header list and it put it all back in order and ready to rock.

I am going into the weekend feeling much lighter now that this is completed. Thank you again @rob-simmers and @Olaf for all your help and patience.

Melissa

29

Great. :+1:t4:

Have a nice Weekend. :love_you_gesture:t4: