csvHelper add new column beside last one

Question

Edit, thank you for the suggestion of using csvhelper, this is actually helping quite a lot.

What I have done is create a new method like so:

    public static void AppendFile<T>(FileInfo fi, List<T> report)
    {
        var settings = new CsvConfiguration(new CultureInfo("en-GB"))
        {
            //Delimiter = ";"
        };


        using var stream = File.Open(fi.FullName, FileMode.Append);

        using var writer = new StreamWriter(stream);

        using (var csv = new CsvWriter(writer, settings))
        {
            csv.WriteRecords(report);
        }

    }

And gone through the example on the csvhelper site, creating a new class:

    public class Foo
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

and then creating a new list:

    public class Foo
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

and calling it like so:

AppendToFile.AppendFile(exportFile, records1);

This is working better than what I had before but, instead adding the new columns beside the last column, they are getting added at the bottom of the file.

For clarification,

what I'm trying to do:

what I'm getting:

As you'll be able to see, it's just being added as new rows rather than being separate columns, what do I need to change?

Answer 1

I managed to figure out a way that worked for me, might not be the most efficient but it does work.

    public static void AppendFile(FileInfo fi, List<string> newColumns, DataTable newRows)
    {
        var settings = new CsvConfiguration(new CultureInfo("en-GB"))
        {
            Delimiter = ";"

        };


        var dt = new DataTable();
        using (var reader = new StreamReader(fi.FullName))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            using (var dataReader = new CsvDataReader(csv))
            {

                dt.Load(dataReader);

                foreach (var title in newColumns)
                {
                    dt.Columns.Add(title);
                }
                dt.Rows.Clear();

                foreach (DataRow row in newRows.Rows)
                {
                    dt.Rows.Add(row.ItemArray);
                }
            }

        }

        using var streamWriter = new StreamWriter(fi.FullName);
        using var csvWriter = new CsvWriter(streamWriter, settings);
        // Write columns
        foreach (DataColumn column in dt.Columns)

        {
            csvWriter.WriteField(column.ColumnName);
        }
        csvWriter.NextRecord();

        // Write row values
        foreach (DataRow row in dt.Rows)
        {
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                csvWriter.WriteField(row[i]);
            }
            csvWriter.NextRecord();
        }


    }

I start by getting the contents of the csv file into a data table and then adding in the new columns that I need. I then clear all the rows in the datatable and add new ones in (the data that is removed is added back in via the newRows parameter) and then write the datatable to the csv file