Loading CSV in C#

Question

I want to search the penultimate row in the first.csv which date is 1975-01-03 and the Lemon value is 17.0, after I search in the second.csv the same date which lemon is 19.0

After catching both values, I compute the difference 17.0 - 19.0 = -2.0

The next step is to sum the difference -2 to all Lemon's values in second.csv from the date 1975-01-03 to the end 1975-01-09

The final step is to write the third.csv where we add the first.csv until the date 1975-01-02 and the sum we've done with second.csv from 1975-01-03 to the end 1975-01-09

first.csv

Date,Lemon
1974-12-31,19.0
1975-01-02,18.0
1975-01-03,17.0
1975-01-06,16.0

second.csv

Date,Lemon
1975-01-02,18.0
1975-01-03,19.0
1975-01-06,19.5
1975-01-07,19.5
1975-01-08,18.0
1975-01-09,17.0

third.csv

Date,Lemon
1974-12-31,19.0
1975-01-02,18.0
1975-01-03,17.0
1975-01-06,17.5
1975-01-07,17.5
1975-01-08,16.0
1975-01-09,15.0

All in all, the read from CSV is not as important as to obtain the third result in an Array, DataTable, Dictionary or whatever. Thanks

Answer 1

This looks like homework, I strongly advice you to do this exercice by yourself by learning about LINQ (just google it). If you are stuck or can't find the solution here is a way to do it :

class LemonAtDate
{
    public DateTime Date { get; set; }
    public double Value { get; set; }

    public LemonAtDate(DateTime Date, double Value)
    {
        this.Date = Date;
        this.Value = Value;
    }

    public static List<LemonAtDate> LoadFromFile(string filepath)
    {
        IEnumerable<String[]> lines = System.IO.File.ReadLines(filepath).Select(a => a.Split(','));

        List<LemonAtDate> result = new List<LemonAtDate>();

        int index = 0;
        foreach (String[] line in lines)
        {
            index++;
            if (index == 1) continue; //skip header

            DateTime date = DateTime.ParseExact(line[0], "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
            double value = Double.Parse(line[1], System.Globalization.CultureInfo.InvariantCulture);

            result.Add(new LemonAtDate(date, value));
        }

        return result;
    }

    public static void WriteToFile(IEnumerable<LemonAtDate> lemons, string filename)
    {
        //Write to file
        using (var sw = new System.IO.StreamWriter(filename))
        {
            foreach (LemonAtDate lemon in lemons)
            {
                sw.WriteLine("Date,Lemon");//Header

                string date = lemon.Date.ToString("yyyy-MM-dd");
                string value = lemon.Value.ToString();

                string line = string.Format("{0},{1}", date, value);
                sw.WriteLine(line);
            }
        }
    }

}

static void Main(string[] args)
{
    //Load first file
    List<LemonAtDate> firstCsv = LemonAtDate.LoadFromFile("first.csv");

    //Load second file
    List<LemonAtDate> secondCsv = LemonAtDate.LoadFromFile("second.csv");

    //We need at least two rows
    if (firstCsv.Count >= 2)
    {
        //Penultimate row in first file
        LemonAtDate lemonSecondLast = firstCsv[firstCsv.Count - 2];

        //Find the value 19 in the second file
        LemonAtDate lemonValue19 = secondCsv.Where(x => x.Value == 19).FirstOrDefault();

        //Value found
        if (lemonValue19 != null)
        {
            double delta = lemonSecondLast.Value - lemonValue19.Value;

            //Get the items between the dates and add the delta
            DateTime dateStart = new DateTime(1975, 1, 3);
            DateTime dateEnd = new DateTime(1975, 1, 9);
            IEnumerable<LemonAtDate> secondFileSelection = secondCsv.Where(x => x.Date >= dateStart && x.Date <= dateEnd)
                                                          .Select(x => { x.Value += delta; return x; });

            //Create third CSV
            List<LemonAtDate> thirdCsv = new List<LemonAtDate>();

            //Add the rows from the first file until 1975-01-02
            DateTime threshold = new DateTime(1975, 1, 2);
            thirdCsv.AddRange(firstCsv.Where(x => x.Date <= threshold));

            //Add the rows from the second file
            thirdCsv.AddRange(secondFileSelection);

            //Write to file
            LemonAtDate.WriteToFile(thirdCsv, "third.csv");
        }
    }
}

Answer 2

There are better ways of doing this, I took a quick and dirty procedural approach instead of an OO one. I also took a peek at the other answer and I see he parsed out the datetimes. I decided not to since you weren't doing any math specifically based on that. However his answer would be more flexible as with datetimes you can do more operations in the future.

        List<string> csvfile1Text = System.IO.File.ReadAllLines("file1.csv").ToList();
        List<string> csvfile2Text = System.IO.File.ReadAllLines("file2.csv").ToList();


        Dictionary<string, double> csv1Formatted = new Dictionary<string, double>();
        Dictionary<string, double> csv2Formatted = new Dictionary<string, double>();
        Dictionary<string, double> csv3Formatted = new Dictionary<string, double>();

        foreach (string line in csvfile1Text)
        {
          var temp=  line.Split(',');
            csv1Formatted.Add(temp[0], Double.Parse(temp[1]));

        }
        foreach (string line in csvfile2Text)
        {
            var temp = line.Split(',');
            csv2Formatted.Add(temp[0], Double.Parse(temp[1]));

        }

        //operation 1
        var penultimate = csv1Formatted["1974-01-03"];
        var corrsponding = csv2Formatted["1974-01-03"];
        var difference = penultimate - corrsponding;

        //operation 2
        var start = csv2Formatted["1974-01-03"];
        var end = csv2Formatted["1974-01-09"];
        var intermediate = csv2Formatted.Keys.SkipWhile((element => element != "1974-01-03")).ToList();
        Dictionary<string, double> newCSV2 = new Dictionary<string, double>();

        foreach (string element in intermediate)
        {
           var found = csv2Formatted[element];
           found = found + difference;
            newCSV2.Add(element, found);

        }

        //operation 3
        intermediate = csv1Formatted.Keys.TakeWhile((element => element != "1975-01-03")).ToList();
        foreach (string element in intermediate)
        {
            var found = csv1Formatted[element];
            csv3Formatted.Add(element, found);

        }
        foreach (KeyValuePair<string,double> kvp in newCSV2)
        {
            csv3Formatted.Add(kvp.Key,kvp.Value);

        }

        //writing CSV3
        StringBuilder sb = new StringBuilder();
        foreach (KeyValuePair<string,double> kvp in csv3Formatted)
        {

            sb.AppendLine(kvp.Key + "," + kvp.Value);

        }
        System.IO.File.WriteAllText("C:\\csv3.csv", sb.ToString());

Answer 3

This is my favorite to use with CSV https://github.com/kentcb/KBCsv

and if you want to work with csv entries as model for each row: http://www.filehelpers.net/quickstart/

I hope you find this helpful. Good luck :) Enjoy coding

Answer 4

Start with a handy struct to make the coding easier:

public struct Line
{
    public DateTime Timestamp;
    public decimal Lemon;
}

Then you can write a simple function to load your CSV files:

Func<string, Line[]> readCsv =
    fn =>
        File
            .ReadLines(fn)
            .Skip(1)
            .Select(x => x.Split(','))
            .Select(y => new Line()
            {
                Timestamp = DateTime.Parse(y[0]),
                Lemon = decimal.Parse(y[1])
            })
            .ToArray();

Now the rest is just a reading the files and a couple of LINQ queries before writing out the results:

Line[] first = readCsv(@"C:\_temp\first.csv");
Line[] second = readCsv(@"C:\_temp\second.csv");

Line difference =
(
    from pen in first.Skip(first.Length - 2).Take(1)
    from mtch in second
    where mtch.Timestamp == pen.Timestamp
    select new Line()
    {
        Timestamp = pen.Timestamp,
        Lemon = pen.Lemon - mtch.Lemon
    }
).First();

IEnumerable<string> result =
    new [] { "Date,Lemon" }
        .Concat(
            first
                .Where(x => x.Timestamp < difference.Timestamp)
                .Concat(
                    second
                        .Where(x => x.Timestamp >= difference.Timestamp)
                        .Select(x => new Line()
                        {
                            Timestamp = x.Timestamp,
                            Lemon = x.Lemon + difference.Lemon
                        }))
                .Select(x => String.Format(
                    "{0},{1}",
                    x.Timestamp.ToString("yyyy-MM-dd"),
                    x.Lemon)));

File.WriteAllLines(@"C:\_temp\third.csv", result);

The result I get is:

Date,Lemon
1974-12-31,19.0
1975-01-02,18.0
1975-01-03,17.0
1975-01-06,17.5
1975-01-07,17.5
1975-01-08,16.0
1975-01-09,15.0