How to place data to new columns in CSV using CsvWriter in .NET?

Question

I have some data which is written to CSV file. When I open them in Excel file, I see them in 2 columns. Is there a way I can add multiple headers and write the values to that columns?

Random Data:

public class DataTimeSeriesValues
{
    public string Name { get; set; }
    public string Unit { get; set; }
    public ICollection<DataValue> Values { get; set; }

    public static List<DataTimeSeriesValues> Get()
    {
        var tags = new List<DataTimeSeriesValues>();
        var random = new Random();
        var oil = new DataTimeSeriesValues
        {
            Name = "Oil",
            Unit = "bbl",
            Values = new List<DataValue>()
        };
        var gas = new DataTimeSeriesValues
        {
            Name = "Gas",
            Unit = "mmscf",
            Values = new List<DataValue>()
        };
        var water = new DataTimeSeriesValues
        {
            Name = "Water",
            Unit = "bbl",
            Values = new List<DataValue>()
        };

        for (int i = 0; i < 2; i++)
        {
            oil.Values.Add(new DataValue
            {
                Timestamp = DateTime.UtcNow.AddMinutes(i),
                Value = random.Next(1, 100).ToString()
            });
            gas.Values.Add(new DataValue
            {
                Timestamp = DateTime.UtcNow.AddMinutes(i),
                Value = random.Next(1, 100).ToString()
            });
            water.Values.Add(new DataValue
            {
                Timestamp = DateTime.UtcNow.AddMinutes(i),
                Value = random.Next(1, 100).ToString()
            });
        }

        tags.Add(oil);
        tags.Add(gas);
        tags.Add(water);

        return tags;
    }
}


public class DataValue
{
    public DateTime Timestamp { get; set; }

    public string Value { get; set; }
}

This is how I write data to the CSV file.

string csvPath = Path.Combine(folderPath, $ "{task.Name}_{DateTime.UtcNow:ddMMyyyyHHmmss}.csv");
var streamWriter = new StreamWriter(csvPath);

var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture);
List < DataTimeSeriesValues > tags = DataTimeSeriesValues.Get();

foreach(DataTimeSeriesValues tag in tags) {
  csvWriter.WriteField("Timestamp (UTC)");
  csvWriter.WriteField($ "{tag.Name} ({tag.Unit})");
  csvWriter.NextRecord();

  foreach(DataValue value in tag.Values) {
    csvWriter.WriteField(value.Timestamp);
    csvWriter.WriteField(value.Value);
    csvWriter.NextRecord();
  }
}

streamWriter.Flush();
streamWriter.Close();

This produces output fine as follows:

Timestamp UTC,Oil (bbl)
09/12/2023 05:52:03,72
09/12/2023 05:53:03,98
Timestamp UTC,Gas (mmscf)
09/12/2023 05:52:03,21
09/12/2023 05:53:03,20
Timestamp UTC,Water (bbl)
09/12/2023 05:52:03,37
09/12/2023 05:53:03,45

But, I need the new tags to be added to next column than in same column when as follows:

Timestamp UTC,Oil (bbl),Timestamp UTC,Gas (mmscf),Timestamp UTC,Water (bbl)
09/12/2023 05:52:03,72,09/12/2023 05:52:03,21,09/12/2023 05:52:03,37
09/12/2023 05:53:03,98,09/12/2023 05:53:03,20,09/12/2023 05:53:03,45

Is there a way to achieve this?

Answer 1

Without changing much of your code here is a working solution. First write the headers (header refers to oil, gas, etc), then iterate through the number of headers, writing the first data value of each header, after which you will add a new line and do the same until you reach the end of all your data values. You will also need to change the ICollection to IList so that you can use the index.

using var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture);
// HEADER WRITING:
// For each time series data (e.g., Oil, Gas, Water), we'll have two columns: 
// one for the timestamp and one for the value. 
// So, we iterate over the tags and write the headers accordingly.
foreach (DataTimeSeriesValues tag in tags)
{
    csvWriter.WriteField("Timestamp (UTC)"); // Write the timestamp header
    csvWriter.WriteField($"{tag.Name} ({tag.Unit})"); // Write the name of the tag with its unit (e.g., Oil (bbl))
}
csvWriter.NextRecord(); // Move to the next row after writing headers
    
// DATA WRITING:
// Determine the number of time series data available.(oil, gas)
// We are also assuming that for every header there is the same amount of data rows
int numberOfHeaders = tags[0].Values.Count;

// Loop through each header value.
for (int i = 0; i < numberOfHeaders; i++)
{
    // For each timestamp, loop through each tag (e.g., Oil, Gas, Water)
    foreach (DataTimeSeriesValues tag in tags)
    {
        csvWriter.WriteField(tag.Values[i].Timestamp); // Write the timestamp for the tag
        csvWriter.WriteField(tag.Values[i].Value);     // Write the corresponding value
    }
    csvWriter.NextRecord(); // Move to the next row after writing data for a particular Timeseries value
}

Answer 2

I recommend you to transform your series object into a list of simple objects (flatten), and write it using 'WriteRecords' method. You can try to use dynamic objects to do that:

var records = new List<dynamic>();

dynamic record = new ExpandoObject();
record.Id = 1;
record.Name = "one";
records.Add(record);

using (var writer = new StringWriter())
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
    csv.WriteRecords(records);
    
    writer.ToString().Dump();
}