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

PowerShell Help
1

Good Morning! I am completely lost here. I’m thinking I need to use an IF command, where IF multiple values in the same row in specific columns equal zero, then I need a value from a completely different cell in the same row copied into one of the original cells in the same row.

I have ~1500 records that contain a total volume (D$2), and then 3 break down columns of the same value, lets call them AppleJuice (A$2), OrangeJuice(B$2), and Water(C$2). If AppleJuice, OrangeJuice AND Water all equal 0, then I need the value from the TotalVolume cell copied into the Water column.

I don’t even have sample code to share, because my brain is scrambled eggs.

Any help is appreciated!
Melissa

2

This seems like homework, but you are pretty much spelling out the logic:

$csv = @"
AppleJuice,OrangeJuice,Water,TotalVolume
1,0,1,12
0,0,0,15
2,5,4,15
"@ | ConvertFrom-Csv

$csv | 
    Select AppleJuice, 
           OrangeJuice, 
           TotalVolume, 
           @{Name='Water';Expression={
                if ($_.AppleJuice -eq 0 -and $_.OrangeJuice -eq 0 -and $_.Water -eq 0) {
                    $_.TotalVolume
                }
                else {
                    $_.Water
                }
           }}
1 Like
3

Thanks so much Rob. Definitely not homework. I just used Apple juice, Orange Juice and Water for examples. The real values are Oil, Condy and Gas for a production file for the O&G industry. But Its outside my standard scope of script and I knew what I wanted it to do but couldn’t think of the commands to use.

I’ll give this code a look and try to expand my knowledge base of Powershell. This may be the most simplest things, I seriously need to find a good course to teach me.

4

I see what this did here, but not quite what I needed. I will try to use Import CSV to bring in all the rows that I need from my existing file and Export CSV to fill in the values that the solution provides. Fingers crossed something like this works.

5

You could share a few (sanitzed) lines of your input CSV and an example of the expected output.

6

Great Idea Olaf! Let me see here…

File that I want to amend would look like:

"Record Label","Date","Sending 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"
"VOLUME TICKET","20230101","102-07-13-058-05W4-00","Lindbergh Cavern","Jacknife Oilfield Services","S06619","28.70","0.00","0.00","","Boiler Blowdown Water","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","LICAV017498-WT","","","","","","","","","","","","","","","","5046","","","",""

vs:

"Record Label","Date","Sending 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"
"VOLUME TICKET","20230101","102-07-13-058-05W4-00","Lindbergh Cavern","Jacknife Oilfield Services","S06619","28.70","0.00","0.00","","Boiler Blowdown Water","0.00","0.00","0.00","0.00","0.00","0.00","28.70","0.00","LICAV017498-WT","","","","","","","","","","","","","","","","5046","","","",""
7

Oh … wow … hmmm … could you please remove the columns we don’t need and explain the logic again with the labels we have in your CSV? Earlier you said something about Oil, Condy and Gas. I cannot see those names. :man_shrugging:t4:

8

Sure thing!

Volume	Override Oil/Condensate Volume	Override Water Volume	Override Sand Volume
31.0	0	0	0
28.7	0	0	0
30.3	0	0	0
29.2	0	0	0

After:

Volume	Override Oil/Condensate Volume	Override Water Volume	Override Sand Volume
31.0	0	31.0	0
28.7	0	28.7	0
30.3	0	30.3	0
29.2	0	29.2	0

These are truck tickets. The truck tickets should have a total volume (Volume column), and then break down into product. For some tickets in my ginormous file (which rows of data change daily, but headers stay the same), the tickets have provided me a Volume, but not the break down. I have been requested that whereever Override Oil/Condy Volume, Override Water Volume, AND Override Sand Volume are zeroes, to default the Override Water Volume to be equal to the total volume (Volume).

9

Your sample data actually only show one possible case. So I changed your input data slightly to show that it actually works.
And … actually it is EXACTLY the logic Rob suggested in his answer. :man_shrugging:t4: I literally copied his code and only changed the header names.

$InputData = @'
Volume,Override Oil/Condensate Volume,Override Water Volume,Override Sand Volume
31.0,15,6,1
28.7,0,0,0
30.3,12,12,6.3
29.2,0,0,0
'@ | 
ConvertFrom-Csv

$InputData |
Select-Object -Property Volume, 'Override Oil/Condensate Volume', 
@{
    Name       = 'Override Water Volume'
    Expression = {
        if ($_.'Override Oil/Condensate Volume' -eq 0 -and 
            $_.'Override Water Volume' -eq 0 -and 
            $_.'Override Sand Volume' -eq 0) {
            $_.Volume
        }
        else {
            $_.'Override Water Volume'
        }
    }
},
'Override Sand Volume'

The output looks like this:

Volume Override Oil/Condensate Volume Override Water Volume Override Sand Volume
------ ------------------------------ --------------------- --------------------
31.0   15                             6                     1
28.7   0                              28.7                  0
30.3   12                             12                    6.3
29.2   0                              29.2                  0
1 Like
10

Yes, this works if I want the output to ONLY be those four columns of data, but the original file needs to stay in tact. All columns and data that the script runs on needs to come out with the changes to the Override Water Volume column, where if the other three values are zero, it copies what is in the Volume (total volume) column and pastes it to the Override Water Column. As you saw in my previous post the file I am working on has many columns and this example only shows the 4 that I am focusing on for the script.

Thanks so much!
Melissa

11

I know. And what’s the question? The code does not change 4 columns - it only changes one. :wink: 3 columns remain unchanged!! ;- :point_up_2:t4:

12

How do I get that code to reflect on a larger file that I already have access to, with many more columns, and to export the same file with the changes? I tried this code, as well as an import and export CSV and only ever get out the 4 columns in question, not the whole file. Sorry, I still say I’m new to this and although I can articulate what I want it to do, I have a hard time explaining :confused:

13

Hmmm … no no … I got exactly what you mean. You explained it very clearly. But I think you have a hard time to understand that you have to treat the columns you want to keep as they are just like the columns we kept in this code example. You will have to provide ALL of them at least once in your code. If the file you have to deal with is always the same you can just write them as an array to your code or you can extract them by code if they change occasionally.
For my code example from above you could use

$InputData | 
    Get-Member |
        Where-Object {$_.membertype -eq 'Noteproperty'} | 
            Select-Object -Property Name

to get the complete list of headers. :wink:

14

Ok, I think my brain is catching on. This puts it on the screen. If I use this script with the full array of header names, and import CSV/Export CSV this should bring in the data and export to a file that I can then send to a system for loading, right? I’m on hour 6 of looking at this, and yes I feel embarrassed to say that.

15

Another option rather than specifying all columns is to use Select * with Exclude which would get all columns except the one we are building:

$InputData = @'
Volume,Override Oil/Condensate Volume,Override Water Volume,Override Sand Volume
31.0,15,6,1
28.7,0,0,0
30.3,12,12,6.3
29.2,0,0,0
'@ | 
ConvertFrom-Csv

$InputData |
Select-Object -ExcludeProperty 'Override Water Volume' -Property *, 
@{
    Name       = 'Override Water Volume'
    Expression = {
        if ($_.'Override Oil/Condensate Volume' -eq 0 -and 
            $_.'Override Water Volume' -eq 0 -and 
            $_.'Override Sand Volume' -eq 0) {
            $_.Volume
        }
        else {
            $_.'Override Water Volume'
        }
    }
}
1 Like
16

I thought I had it all figured out, but it didnt work. The volume in the Override Water Volume column is still 0, not equal to the Volume column when all Override volumes are equal to zero. The code I am using is:

$Csv = Import-csv -Path 'D:\autoloader\StrathconaTT\Strathcona Truck Tickets.csv' 
$Headers = $Csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name;
   

          Select "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 -and $_."Override Sand Volume" -eq 0 -and $_."Override Water Volume" -eq 0) {
                    $_."Volume"
                }
                else {
                    $_."Override Water Volume"
                }
           }}
         

$Csv | Export-Csv -Path "D:\autoloader\StrathconaTT\new.csv" -NoTypeInformation;
17

Why don’t you use Rob’s approach recommended is his last answer?

Whatfor do you use the variable $headers?

And what are you selecting here?

18

I removed the Headers line after I made this post, and added Select-Object to the selection of the headers as in Robs first example. When I user Robs second example all I get is:

Length Override Water Volume
------ ---------------------
    55

as a result. I am trying to make sure an export file is created with the data that originally existed but with the change to the Override Water Volume column where applicable. I am just lost sadly.

19

Could you please share the code you used?

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