C# MVC using a CSV file

Question

I have a model that contains some pretty generic properties:

    public class ProductItem
{
    public int      ID { get; set; }
    public string   Description { get; set; }
    public char     Item { get; set; }
    public decimal  Price { get; set; }
    public string   ImagePath { get; set; }
    public string   Barcode { get; set; }
}

I have instances of this model populated from a CSV file using the following:

            DataTable dt = new DataTable();
        dt.Columns.Add("ID", typeof(System.Int32));
        dt.Columns.Add("Description", typeof(System.String));
        dt.Columns.Add("Item", typeof(System.Char));
        dt.Columns.Add("Price", typeof(System.Decimal));
        dt.Columns.Add("ImagePath", typeof(System.String));
        dt.Columns.Add("Barcode", typeof(System.String));

        String[] csv = File.ReadAllLines(csvPath);

        foreach (string csvrow in csv)
        {
            var fields = csvrow.Split(',');
            var row = dt.NewRow();

            row.ItemArray = fields;
            dt.Rows.Add(row);
        }

        return dt;

This returns a Datatable that is then used in the following function to get the list:

        private List<ProductItem> GetAllProductsCSV()
    {
        var filePath = Server.MapPath(@"~/CSV/products.csv");
        var products = new ProductsCSV();
        DataTable results = products.GetProductsFromCSV(filePath);

        List<ProductItem> productItems = (from DataRow dr in results.Rows
                                          select new ProductItem()
                                          {
                                              ID = Convert.ToInt32(dr["ID"]),
                                              Description = dr["Description"].ToString(),
                                              Item = Convert.ToChar(dr["Item"]),
                                              Price = Convert.ToDecimal(dr["Price"]),
                                              ImagePath = dr["ImagePath"].ToString(),
                                              Barcode = dr["Barcode"].ToString(),
                                          }).ToList();

        return productItems;
    }

Firstly, this is all starting to seem a little convoluted for something I think should be a lot easier... Am I making a lot more work for myself than I need to here?

Secondly, I am having a bit of trouble reversing this process when I make changes to a model instance and need to write it back to the CSV file. I understand that I need to re-write the whole lot back in (as I have read that you cant just update 1 row in a CSV)... But if anyone has some examples of how best to achieve this would be greatly appreciated.

It is not clear why you load a datatable with your csv and not directly a `List` — Steve, Jul 29 '17 at 21:31
Sorry, just realised I have put half the story there... will add some (probably more) convoluted code now... — CJH, Jul 29 '17 at 21:37
This is not a good method. The CSV format is [more complicated than that](http://www.secretgeek.net/csv_trouble); `Split(',')` and `Join(',')` will parse only a tiny subset of CSV files. There are [many](https://stackoverflow.com/questions/9642055/) tested, working, debugged CSV parsers ready for you to drop into your app. — Dour High Arch, Jul 29 '17 at 21:39

score 2 · Accepted Answer · answered Jul 29 '17 at 21:42

I would go directly to a List<ProductItem> instead of loading a DataTable and then convert it to a List.

public List<ProductItem> LoadData(csvPath)
{
    String[] csv = File.ReadAllLines(csvPath);
    List<ProductItem> result = new List<ProducItem>();
    foreach (string csvrow in csv)
    {
        var fields = csvrow.Split(',');
        ProductItem prod = new ProductItem()
        {
            ID = Convert.ToInt32(fields[0]),
            Description = fields[1],
            Item = fields[2][0],
            Price = Convert.ToDecimal(fields[3]),
            ImagePath = fields[4],
            Barcode = fields[5]
        });
        result.Add(prod);
    }
    return result;
}

The inverse process will be a loop over your List and build a proper csv line to write in your file

public void WriteToCsv(string csvPath, List<ProductItem> products)
{
    List<string> lines = new List<string>();
    foreach(ProductItem item in products)
    {
        string line = string.Join(",", item.ID.ToString(), 
                                       item.Description, 
                                       item.Item.ToString(),
                                       item.Price.ToString(), 
                                       item.ImageCode,
                                       item.BarCode);
        lines.Add(line);
   }
   File.WriteAllLines(csvPath, lines);
}

Also keep in mind that this is a very simplicistic way to load an write a CSV file. It is better to use a specialized library for that. A library will handle cases like comma inside the description field or some kind of delimiter between fields

Thank you so much Steve!! Not only have you given me clarity on this but it has also helped tidy my exiting code up tremendously! I have done most of my work in EF so this has been a bit of a dark art for me! Appreciated! — CJH, Jul 29 '17 at 23:12

C# MVC using a CSV file

1 Answers1