Manipulate existing CSV file, while keeping columns order. (CsvReader/CsvWriter)

Question

I need to manipulate an existing CSV file via following actions:

1. Read from an existing CSV file -> then Append new row to it. I have following code which is choking over the 3rd row - as the file is already in use by the code from the 1st row. And I'm not sure how to read it properly otherwise, and then append new row to it.

public bool Save(Customer customer)
{
    using (StreamReader input = File.OpenText("DataStoreOut.csv"))
    using (CsvReader csvReader = new CsvReader(input))
    using (StreamWriter output = File.CreateText("DataStoreOut.csv"))
    using (var csvWriter = new CsvWriter(output))
    {
        IEnumerable<Customer> records = csvReader.GetRecords<Customer>();

        List<Customer> customerList = new List<Customer>();
        customerList.Add(customer);

        csvWriter.WriteHeader<Customer>();
        csvWriter.NextRecord();

        foreach (var array in customerList)
        {
            csvWriter.WriteRecord(records.Append(array));
        }
    }
}

2. Each of row in the CSV file contains a customer.CustomerId (which is unique, and read-only). How can I read only row which has specific customerId and then update any values there.

Answer 1

If you want to append a record to a file, the best way to do it is read the items, add the new one to the collection, and write everything back.

public static void Append(Customer customer, string file)
{
    List<Customer> records = null;
    using (var reader = new StreamReader(file))
    {
        using (var csv = new CsvReader(reader))
        {
            records = csv.GetRecords<Customer>().ToList();
        }
    }
    records.Add(customer);

    using (var writer = new StreamWriter(file))
    {
        using (var csv = new CsvWriter(writer))
        {
            csv.WriteRecords(records);
        }
    }
}

As @Dour High Arch mentioned, to be perfectly safe though you might want to take the extra step of using a temp file in case something goes wrong.

If you want to update instead of append, you'd have to look up the specified record, and update it if it exists.

public static void Update(Customer customer, string file)
{
    List<Customer> records = null;
    using (var reader = new StreamReader(file))
    {
        using (var csv = new CsvReader(reader))
        {
            records = csv.GetRecords<Customer>().ToList();
        }
    }
    var index = records.FindIndex(x => x.ID == customer.ID);
    if (index >= 0)
    {
        records[index] = customer;
        using (var writer = new StreamWriter(file))
        {
            using (var csv = new CsvWriter(writer))
            {
                csv.WriteRecords(records);
            }
        }
    }
}

Again, writing to a temp file is advisable.

---

UPDATE

Actually there's a slightly better way to append if you don't want to replace the file. When instantiating a StreamWriter you can do so with append=true. In which case, it will append to the end of the file. The small caveat is that in case the EOF marker is not at a new line but at the last field of the last record, this will append record to the end of the last field messing up your columns. As a workaround I've added a writer.WriteLine(); before using the CSVHelper class' writer.

public static void Append2(Customer customer, string file)
{
    using (var writer = new StreamWriter(file, true))
    {
        writer.WriteLine();
        using (var csv = new CsvWriter(writer))
        {
            csv.WriteRecord(customer);
        }
    }
}

In case the file is in a new line, then this will add an empty line though. That can be countered by ignoring empty lines when you read a file.