Search code examples
c#export-to-csvcsvhelper

CSVHelper Dynamic number of columns

I just started working on csvhelper library today and new help with how to add entries in the next column. Say I have three datasets composed of (ABC Office, Sarah's office and Brian's office) These are Building names...For each building I have TaskName, PointName, DataName and then Local and Value data of rows. Each building will have 2 columns, There can be 10 or 20 buildings (Dynamic) and each building can be 1000's of data rows below local and Value row.

I have the following code

using (var writer = new StreamWriter(filePath))
                            using (var csv1 = new CsvWriter(writer))
                            {
    foreach (var point in this.Points)
                                    {
                                       csv1.WriteField(point.BuildingName);
                                       csv1.WriteField(" ");
                                        csv1.NextRecord();
                                        csv1.WriteField(point.TaskName);
                                        csv1.WriteField(" ");
                                        csv1.NextRecord();
                                        csv1.WriteField(point.PointName);
                                        csv1.WriteField(" ");
                                        csv1.NextRecord();
                                        csv1.WriteField(point.DataName);
                                        csv1.WriteField(" ");
                                        csv1.NextRecord();
                                    }
}

The problem is that it puts all the points information in first column and then ' ' in the second field.

What I need is something like this eventually. enter image description here

After the point information added, I want to be able to append a data set of Local and Value column under each point.

Is this formatting in CSV possible using CSVHelper?

Update:

So far I have gotten this far.. Using String builder added first 5 rows of data. Now I need to be able to append a dataset of columns Local and Value under each Office. Is there a way in CSVHeper library that I can specify where to append the dataset. First 5 rows I am getting like this.

var row1 = new StringBuilder();
 var row2 = new StringBuilder();
var row3 = new StringBuilder();
var row4 = new StringBuilder();


                                    foreach (var point in this.Points)
                                    {
                                        row1.Append($"{ point.BuildingName},,");
                                        row2.Append($"{point.TaskName},,");
                                        row3.Append($"{ point.PointName},,");
                                        row4.Append($"{ point.DataName},,");

                                    }
                                    //StreamWriter sw = new StreamWriter("filePath", true);
                                    using (StreamWriter sw = new StreamWriter(filePath1))
                                    {
                                        sw.Write(row1.ToString());
                                        sw.WriteLine();
                                        sw.Write(row2.ToString());
                                        sw.WriteLine();
                                        sw.Write(row3.ToString());
                                        sw.WriteLine();
                                        sw.Write(row4.ToString());
                                        sw.WriteLine();

                                        sw.Close();
                                    }
Solution

  • What you are trying create is not the standard CSV file that CsvHelper was built to create. A CSV file has one row of headers followed by rows of data. You appear to be creating an Excel report using comma separated values. CsvHelper can help you write the fields, but you are going to have to supply the logic for writing the report format. The following is one way that you could do it.

    public static void Main(string[] args)
{
    var points = new List<Point>
    {
        new Point
        {
            BuildingName = "ABC Office",
            TaskName = "Temperature",
            PointName = "14",
            DataName = "Temperature: Degrees F",
            Results = new List<Result> {
                new Result { Local = new DateTime(2019, 1,1), Value = 2},
                new Result { Local = new DateTime(2019, 1, 2), Value = 23}
            }
        },
        new Point
        {
            BuildingName = "Sarah's Office",
            TaskName = "Fan",
            PointName = "33",
            DataName = "0=Stop;1=Run",
            Results = new List<Result> {
                new Result { Local = new DateTime(2019, 1,1), Value = 2},
                new Result { Local = new DateTime(2019, 1, 2), Value = 23},
                new Result { Local = new DateTime(2019, 1, 3), Value = 45},
                new Result { Local = new DateTime(2019, 1, 4), Value = 34},
                new Result { Local = new DateTime(2019, 1, 5), Value = 36}
            }
        },
        new Point
        {
            BuildingName = "Brian's Office",
            TaskName = "Fan",
            PointName = "35",
            DataName = "Humidity",
            Results = new List<Result> {
                new Result { Local = new DateTime(2019, 1,1), Value = 2},
                new Result { Local = new DateTime(2019, 1, 2), Value = 23},
                new Result { Local = new DateTime(2019, 1, 3), Value = 45},
                new Result { Local = new DateTime(2019, 1, 4), Value = 34},
                new Result { Local = new DateTime(2019, 1, 5), Value = 36},
                new Result { Local = new DateTime(2019, 1, 6), Value = 56},
                new Result { Local = new DateTime(2019, 1, 7), Value = 92}
            }
        },
    };

    using (var writer = new StreamWriter(filePath))
    using (var csv = new CsvWriter(writer))
    {
        // Print buildings
        foreach (var point in points)
        {
            csv.WriteField(point.BuildingName);
            csv.WriteField("");
        }
        csv.NextRecord();

        // Print Tasks
        foreach (var point in points)
        {
            csv.WriteField(point.TaskName);
            csv.WriteField("");
        }
        csv.NextRecord();

        // Print Points
        foreach (var point in points)
        {
            csv.WriteField(point.PointName);
            csv.WriteField("");
        }
        csv.NextRecord();

        // Print DataNames
        foreach (var point in points)
        {
            csv.WriteField(point.DataName);
            csv.WriteField("");
        }
        csv.NextRecord();

        // Print value titles
        foreach (var point in points)
        {
            csv.WriteField("Local");
            csv.WriteField("Value");
        }
        csv.NextRecord();

        var endReached = false;
        var pointIndex = 0;

        // Print values
        while (!endReached)
        {
            endReached = true;
            foreach (var point in points)
            {
                if (point.Results.Count > pointIndex)
                {
                    csv.WriteField(point.Results[pointIndex].Local);
                    csv.WriteField(point.Results[pointIndex].Value);

                    if (point.Results.Count > pointIndex + 1)
                    {
                        endReached = false;
                    }
                }
                else
                {
                    csv.WriteField("");
                    csv.WriteField("");
                }
            }
            csv.NextRecord();

            pointIndex += 1;
        }
    }
}

public class Point
{
    public string BuildingName { get; set; }
    public string TaskName { get; set; }
    public string PointName { get; set; }
    public string DataName { get; set; }
    public List<Result> Results { get; set; }

}

public class Result
{
    public DateTime Local { get; set; }
    public int Value { get; set; }
}