If "date" in date column matches "date" in another row, add the values together

PowerShell Help
1

Good Afternoon,

I have a CSV file that I am trying to manipulate. I have data coming to me for one location but it is coming to be from two physical sites in the same file (an example of this below). I need a script that will say “if the date in F2 equals date in any other cell in column F, add the values in G2 to the G row where the date matches, add the values in H2 to the H row where the date matches, etc…” Im sure this is confusing but I am not sure how else to explain it. I am happy to answer any questions!

Thanks! Any advice is appreciated.

Company,Field,Facility,Well Name,UWI,Date,Oil vol,Water vol,Cond vol,Gas vol,BSW,Hours On,Tubing Prs,Casing Prs,Flow Prs,SPM,RPM,Stroke Ln,Choke Sz,Flow Tmp,Prod Notes,Spec Notes
Distinction Energy Corp.,Placid,UGR Negus Plant 11-3-60-25w5,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/25/2022,0,0,0,16.58,0,24,0,0,0,0,0,0,,0,none,                                                                                                                                                                                                                                                               
Distinction Energy Corp.,Placid,UGR Negus Plant 11-3-60-25w5,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/26/2022,0,0,0,11.5,0,24,0,0,0,0,0,0,,0,none,                                                                                                                                                                                                                                                               
Distinction Energy Corp.,Placid,UGR Negus Plant 11-3-60-25w5,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/27/2022,0,0,0,16.67,0,24,0,0,0,0,0,0,,0,none,                                                                                                                                                                                                                                                               
Distinction Energy Corp.,Placid,UGR Negus Plant 11-3-60-25w5,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/28/2022,0,0,0,17.89,0,24,0,0,0,0,0,0,,0,none,                                                                                                                                                                                                                                                               
Distinction Energy Corp.,Placid,UGR Negus Plant 11-3-60-25w5,02/15-10-060-24W5/0,102/15-10-060-24W5/00,3/1/2022,0,0,0,4.74,0,24,0,0,0,0,0,0,,0,none,                                                                                                                                                                                                                                                               
Distinction Energy Corp.,Bigstone Montney Field,Bigstone 7-11-60-23w5 Sour Battery,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/25/2022,12.08,7.24,0,1.26,39,24,1083.43,3547.58,0,0,0,0,,0,none,.Import Start ---- End                                                                                                                                                                                                                                         
Distinction Energy Corp.,Bigstone Montney Field,Bigstone 7-11-60-23w5 Sour Battery,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/26/2022,26.03,8.56,0,6.92,23,24,910.59,3446.59,0,0,0,0,,0,none,.Import Start ---- End                                                                                                                                                                                                                                         
Distinction Energy Corp.,Bigstone Montney Field,Bigstone 7-11-60-23w5 Sour Battery,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/27/2022,5.19,8.27,0,1.26,62,24,978.04,3240.38,0,0,0,0,,0,none,.Import Start ---- End                                                                                                                                                                                                                                         
Distinction Energy Corp.,Bigstone Montney Field,Bigstone 7-11-60-23w5 Sour Battery,02/15-10-060-24W5/0,102/15-10-060-24W5/00,2/28/2022,6.7,8.77,0,1.26,58,24,1612.5,3324.55,0,0,0,0,,0,none,.Import Start ---- End                                                                                                                                                                                                                                         
Distinction Energy Corp.,Bigstone Montney Field,Bigstone 7-11-60-23w5 Sour Battery,02/15-10-060-24W5/0,102/15-10-060-24W5/00,3/1/2022,5.43,6.36,0,13.72,53,24,746.18,3278.25,0,0,0,0,,0,none,.Import Start ---- End
2

What have you tried so far? Please share your code.

3

Hi,

When you talk of F2, G2 etc. you’re talking in terms of Excel references, rather than thinking in PowerShell.

Are you expecting something that updates the cell reference in Excel, or are you looking to work with PowerShell to import the CSV, manipulate the data and save it? The approaches are very different.

Are there always just two entries for each date or multiple? Will you have two dates the same for different locations?

Have you any sample code so far that you’re struggling with?

Matt

1 Like
4

Hi Matt,

Thanks for you reply! When I mentioned F2, G2, etc… I was referencing the Excel data, my apologies. I would like to create a new csv and have the summed values displayed in the new file with only one days date for each location. I am in control of the amount of locations that will be supplied in the file. I can have one location per file if needed where I just need the same days production summed in one row. I have 6 locations in total that need this done to them and would rather do it in one script if possible.

I tried to do some research to find where to start with the code and the best I can see is doing an import csv command, followed by a for-each loop but I am not sure how to do this on a file that will have different dates. To be honest, I just got overwhelmed trying to find some hints on line and have no idea where to start.

Thanks!
Melissa

5

If I got it right you could use

and

to get what you need.

1 Like