C# Add lines with same Date.Time in .csv file

Question

I'm currently making a program that tracks certain things (basic INT Values and the Date when they were saved).

My goal is to add up the INT values with the same Date.

20.11.2018 00:00:00; 1;1;1;1;1
20.11.2018 00:00:00; 1;1;1;1;1
22.11.2018 00:00:00; 1;1;1;1;1

Should basically look like this

20.11.2018 00:00:00; 2;2;2;2;2
22.11.2018 00:00:00; 1;1;1;1;1

The Saving Data and even the adding the 2 "Lines" together is working perfectly fine.

The problem is that When I add the Lines together, the 2 Lines with the 1 obviously don't get deleted.

This is the Method that Compares the Date and adds the lines together:

public static Dictionary<DateTime, int[]> CompareDateMethod(Dictionary<DateTime, int[]> oDateTimeAndIntDictionary,string[][] ReadData)
{
    Dictionary<DateTime, int[]> oPrintRealData = new Dictionary<DateTime, int[]>();
    Dictionary<DateTime, int[]> oAddRealData = new Dictionary<DateTime, int[]>();

    for (int i = 0 ; i < ReadData.Length; i++)
    {
        DateTime dtDateValue;
        if (DateTime.TryParse(ReadData[i][0], out dtDateValue))     
        {
            int[] iValuesToAdd = ConvertArrayToInt(ReadData[i]);

            if (dtDateValue.Date == DateTime.Now.Date)                   
            {
                for (int j = 0; j < iValuesToAdd.Length; j++)
                {
                    oDateTimeAndIntDictionary[dtDateValue.Date][j] += iValuesToAdd[j];      
                }
            }
            else if (dtDateValue.Date != DateTime.Now.Date)                              
            {
                goto Endloop;                                   
            }
        }
    }
    Endloop:
    return oDateTimeAndIntDictionary;  

This is the method that Writes the Data into the .CSV file

    Dictionary<DateTime, int[]> oDateTimeAndIntDictionary = new Dictionary<DateTime, int[]>();
    string[][] OldData= AddVariables.ReadOldData();
    int[] iNewDataArray = new int[] { iVariable1, iVariable2, iVariable3, iVariable4, iVariable5};

    oDateTimeAndIntDictionary.Add(DateTime.Now.Date, iNewDataArray);

    using (System.IO.FileStream fileStream = new System.IO.FileStream(@"C: \Users\---\Csvsave\SaveDatei.csv", System.IO.FileMode.Append, System.IO.FileAccess.Write))
    using (System.IO.StreamWriter streamWriter = new System.IO.StreamWriter(fileStream))
    {
        foreach (KeyValuePair<DateTime, int[]> kvp in AddVariables.CompareDateMethod(oDateTimeAndIntDictionary, OldData))
        {
                streamWriter.WriteLine("{0}; {1}", kvp.Key, string.Join(";", kvp.Value));
        }
    }

I tried so hard to come up with something but nothing worked (I tried deleting lines from the .csv which seems really hard, I tried reading the file in backwards which didnt work etc.)

If someone can give me some pointers I would really appreciate it.

Answer 1

I think the problem with the original code is that it's a bit confused about what happens when. I've restructured it so that things happen in a logical order (and updated it a bit, simplifying variable names, etc). There's one function for combining rows with the same date, which is separate from the CSV writing code (which hasn't changed)

static void Main(string[] args)
    {
        var oldData = ReadOldData();

        // Do the work
        var results = SumValuesForSameDate(oldData);

        // Write the file
        using (System.IO.FileStream fileStream = new System.IO.FileStream(@"C: \Users\---\Csvsave\SaveDatei.csv", System.IO.FileMode.Append, System.IO.FileAccess.Write))
        using (System.IO.StreamWriter streamWriter = new System.IO.StreamWriter(fileStream))
        {
            foreach (KeyValuePair<DateTime, int[]> kvp in results)
            {
                streamWriter.WriteLine("{0}; {1}", kvp.Key, string.Join(";", kvp.Value));
            }
        }
    }

    public static Dictionary<DateTime, int[]> SumValuesForSameDate(string[][] readData)
    {
        var oDateTimeAndIntDictionary = new Dictionary<DateTime, int[]>();

        var currentDate = DateTime.MinValue;

        foreach (var row in readData)
        {
            DateTime dateValue;
            if(!DateTime.TryParse(row[0], out dateValue)) continue;

            dateValue = dateValue.Date;

            var intValues = ConvertArrayToInt(row);

            if (dateValue == currentDate)
            {
                for (var j = 0; j < intValues.Length; j++)
                {
                    oDateTimeAndIntDictionary[dateValue][j] += intValues[j];
                }
            }
            else
            {
                oDateTimeAndIntDictionary.Add(dateValue, intValues);
                currentDate = dateValue;
            }
        }

        return oDateTimeAndIntDictionary;
    }

    static int[] ConvertArrayToInt(string[] strings)
    {
        var output = new int[strings.Length - 1];
        for (var i = 1; i < strings.Length; i++)
        {
            output[i - 1] = int.Parse(strings[i]);
        }

        return output;
    }

    static string[][] ReadOldData()
    {
        // Fake data
        var data = new string[][]
        {
            new string[] { "20.11.2018 00:00:00", "1", "1", "1", "1", "1"  },
            new string[] { "20.11.2018 00:00:00", "1", "1", "1", "1", "1"  },
            new string[] { "22.11.2018 00:00:00", "1", "1", "1", "1", "1"  },
        };
        return data;
    }
}

Answer 2

For overwriting the previous CSV just use System.IO.FileMode.Create instead of Append. This will overwrite any previous data.

Answer 3

You need to overwrite the csv anyways to get rid of the written row. So instead of returning oDateTimeAndIntDictionary from CompareDateMethod method, return ReadData and overwrite the parsed values of ReadData.

Something like this,

public static Dictionary<DateTime, int[]> CompareDateMethod(Dictionary<DateTime, int[]> oDateTimeAndIntDictionary,string[][] ReadData)
{
    Dictionary<DateTime, int[]> oPrintRealData = new Dictionary<DateTime, int[]>();
    Dictionary<DateTime, int[]> oAddRealData = new Dictionary<DateTime, int[]>();

    for (int i = 0 ; i < ReadData.Length; i++)
    {
        DateTime dtDateValue;
        if (DateTime.TryParse(oDateTimeAndIntDictionary[0][0], out dtDateValue))     
        {
            int[] iValuesToAdd = ConvertArrayToInt(ReadData[i]);

            if (dtDateValue.Date == DateTime.Now.Date)                   
            {
                for (int j = 0; j < iValuesToAdd.Length; j++)
                {
                    //Add the ReadData values here and store at ReadData[i][j]
                }
            }
        else if (dtDateValue.Date != DateTime.Now.Date)                              
        {
            goto Endloop;                                   
        }
    }
}
Endloop:
return ReadData;
}

Hope this helps...

Answer 4

I readed your comment about not using linq and 3rd part lib too late.
But let me show you what you are missing.
Here it's a little Linq + CSVHelper

First Lest define your data, and define how to map them in the CSV

public sealed class data
{
    public DateTime TimeStamp { get; set; }
    public List<int> Numbers { get; set; }
}

public sealed class dataMapping : ClassMap<data>
{
    public dataMapping()
    {
        Map(m => m.TimeStamp).Index(0);
        Map(m => m.Numbers)
            .ConvertUsing(
                row =>
                new List<int> {
                    row.GetField<int>(1),
                    row.GetField<int>(2),
                    row.GetField<int>(3)
                }
            );
    }
}

And now this is a short demo:

class CsvExemple
{
    string inputPath = "datas.csv";
    string outputPath = "datasOut.csv";

    List<data> datas;
    public void Demo()
    {
        //no duplicate row in orginal input
        InitialiseFile();

        LoadExistingData();

        //add some new row and some dupe
        NewDatasArrived();

        //save to an other Path, to Compare. 
        SaveDatas();
    }

    private void SaveDatas()
    {
        using (TextWriter writer = new StreamWriter(outputPath))
        using (var csvWriter = new CsvWriter(writer))
        {
            csvWriter.Configuration.RegisterClassMap<dataMapping>();
            csvWriter.Configuration.Delimiter = ";";
            csvWriter.Configuration.HasHeaderRecord = false;
            csvWriter.WriteRecords(datas);
        }
    }

    static List<int> SuperZip(params List<int>[] sourceLists)
    {
        for (var i = 1; i < sourceLists.Length; i++)
        {
            sourceLists[0] = sourceLists[0].Zip(sourceLists[i], (a, b) => a + b).ToList();
        }
        return sourceLists[0];
    }

    private void NewDatasArrived()
    {
        var now = DateTime.Today;

        // New rows
        var outOfInitialDataRange = Enumerable.Range(11, 15)
                            .Select(x => new data { TimeStamp = now.AddDays(-x), Numbers = new List<int> { x, x, x } });
        // Duplicate rows
        var inOfInitialDataRange = Enumerable.Range(3, 7)
                            .Select(x => new data { TimeStamp = now.AddDays(-x), Numbers = new List<int> { x, x, x } });

        //add all of them them together
        datas.AddRange(outOfInitialDataRange);
        datas.AddRange(inOfInitialDataRange);

        // all this could have been one Line
        var grouped = datas.GroupBy(x => x.TimeStamp);

        var temp = grouped.Select(g => new { TimeStamp = g.Key, ManyNumbers = g.Select(x => x.Numbers).ToArray() });

        // We can combine element of 2 list using Zip. ListA.Zip(ListB, (a, b) => a + b)
        datas = temp.Select(x => new data { TimeStamp = x.TimeStamp, Numbers = SuperZip(x.ManyNumbers) }).ToList();
    }

    private void LoadExistingData()
    {
        if (File.Exists(inputPath))
        {
            using (TextReader reader = new StreamReader(inputPath))
            using (var csvReader = new CsvReader(reader))
            {
                csvReader.Configuration.RegisterClassMap<dataMapping>();
                csvReader.Configuration.HasHeaderRecord = false;
                csvReader.Configuration.Delimiter = ";";

                datas = csvReader.GetRecords<data>().ToList();
            }
        }
        else
        {
            datas = new List<data>();
        }
    }

    private void InitialiseFile()
    {
        if (File.Exists(inputPath))
        {
            return;
        }

        var now = DateTime.Today;
        var ExistingData = Enumerable.Range(0, 10)
                            .Select(x => new data { TimeStamp = now.AddDays(-x), Numbers = new List<int> { x, x, x } });

        using (TextWriter writer = new StreamWriter(inputPath))
        using (var csvWriter = new CsvWriter(writer))
        {
            csvWriter.Configuration.RegisterClassMap<dataMapping>();
            csvWriter.Configuration.Delimiter = ";";
            csvWriter.Configuration.HasHeaderRecord = false;
            csvWriter.WriteRecords(ExistingData);
        }
    }
}