C# CSV Filehelpers compare two rows in the same file, add and remove rows

Question

I want to write a simple program to ease my life at work, but I'm not too familiar with C# so I'm trying to read the documentation and some examples, but I don't find the documentation too good, and can't find any examples to feed off.

I have a CSV file (wireless temperature sensor that sometimes loses connection or sends multiple readouts in quick succession) and I want to compare two DateTimes (TimeSpan) from 2 subsequent rows, and depending on result remove that row, or add another in between these. If TimeSpan between 2 DateTimes is less than 10 minutes, delete that row. If it's longer than 10 minutes create a new row with time that is 10 minutes after the first one. All readouts are between themselves in multiples of 10 minutes.

Example file:

[DelimitedRecord(","), IgnoreFirst(1)] public class CSVDataFields{ [FieldQuoted('"')] [FieldConverter(ConverterKind.Date, "MM/dd/yyyy h:mm tt")] public DateTime Date; [FieldQuoted('"')] public float Value; } "Date","Value" "03/19/2019 3:10 PM","20.5" "03/19/2019 3:10 PM","20.5" "03/19/2019 3:10 PM","20.4" "03/19/2019 3:20 PM","20.2" "03/19/2019 3:50 PM","20.0" "03/19/2019 4:00 PM","19.8"

So the first check is [0] and [1], it's less than 10, so delete that row, [0] and [2] less than 10 - delete, [0] and [3] it's ok,

[3] and [4] is longer than 10 minutes, create a new row with time([3] + 10) and value average (20.2, 20.0),

new [4] and [5] is longer than 10 minutes, create a new row with time ([4] + 10) and value average (20.1, 20.0), and so on.

In the example on FileHelpers.net there is only this example. Here you can only access 1 row at a time I think, while I need to have access to 2 rows at the same time.

private void DetectDupes(ref CSVDataFields[] csv){ foreach(CSVDataFields csvData in csv){ } }

I've also not gotten that far as how to save the new file with the modified rows.

Hi @Pangu, welcome to SO. I am curious about this: create a new row with time([3] + 10). Why + 10? What's the rationale behind that? Is 10 arbitrary? Would it be more desirable to find a mean date/time between the two entries? Or are you looking to create a sequence separated by 10? — JuanR
– JuanR, Commented May 29, 2019 at 17:31
@JuanR yes, I want to modify a csv file to have constant 10 minutes intervals. As stated before the readouts are multiples of 10 but sometimes data is duplicated or there wasn't transmission in that time so I need to fill in the gaps. — Pangu
– Pangu, Commented May 30, 2019 at 8:28

Community · Accepted Answer · 2020-06-20 09:12:55Z

You can accomplish this with Linq's Aggregate method:

var engine = new FileHelperEngine<CSVDataFields>(); var result = engine.ReadFile(@"c:\temp\some_source_file.txt"); List<CSVDataFields> newRows = new List<CSVDataFields>(); newRows.Add(result.First()); result.Aggregate((a, b) => { var diff = Math.Abs((a.Date - b.Date).Minutes); if (diff < 10) { return a; } else if (diff == 10) { newRows.Add(b); return b; } else { var newRow = new CSVDataFields() { Date = a.Date.AddMinutes(10), Value = (a.Value + b.Value) / 2 }; newRows.Add(newRow); return newRow; } }); engine.WriteFile(@"C:\temp\destination_file_deduped.txt", newRows);

Output:

03/19/2019 3:10 PM, 20.5

03/19/2019 3:20 PM, 20.2

03/19/2019 3:30 PM, 20.1

03/19/2019 3:40 PM, 19.95

Explanation: The Aggregate method iterates through an enumerable, executing a function delegate which takes as parameters the current value (a) and the next value (b) for each item in the enumerable. On each iteration, it decides if it should skip the new item b (diff < 10), add it to dedupped list (diff==10) or merge it (diff > 10). The key thing to understand here is that b is always the next item in the enumerable and the value that is returned from the current iteration becomes the current value (a) of the next iteration. In other words, a represents the result of the execution of the function for each iteration.

It's generally not a good idea to overwrite the source file (unless you have it backed up somewhere) so I am outputting to a new file but you can change that if you have to.

Also, this is a simplistic example. Make sure to account for situations like the file having no rows and what not.

Thank you, it seems to work, even though I have no idea how. If you could add some comments so it would be easier for me to understand it line by line. If you don't want to do that, or don't have the time I understand, I will debug the code line by line and try to understand it. Again Thank you.
@Pangu: No worries. I get confused with this method all the time, too. It's fairly simple once you "see it". I will add some comments.

Collectives™ on Stack Overflow

C# CSV Filehelpers compare two rows in the same file, add and remove rows

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related