Allowing a user to select column headers to import

Question

I'm using LINQtoCSV within a program that allows the user to import an order from a CSV file. I have all the code working however, if the CSV file doesn't have the exact column headers then it doesn't work.

Below is my class that LINQtoCSV reads into -

public class orderProduct
{
    public orderProduct() { }
    public string product { get; set; }
    public string price { get; set; }
    public string orderQty { get; set; }
    public string value { get; set; }

    public string calculateValue()
    {
        return (Convert.ToDouble(price) * Convert.ToDouble(orderQty)).ToString();
    }
}

If the CSV file doesn't have the exact headers it won't work. The data I actually only need is the first 4 strings.

Below is my function that actually reads in the data.

private void csvParse()
{
    // order.Clear();
    string fileName = txt_filePath.Text.ToString().Trim();

    try
    {
        CsvContext cc = new CsvContext();

        CsvFileDescription inputFileDescription = new CsvFileDescription
        {
            SeparatorChar = ',',
            FirstLineHasColumnNames = true
        };

        IEnumerable<orderProduct> fromCSV = cc.Read<orderProduct>(fileName, inputFileDescription);

        foreach (var d in fromCSV)
        {
            MessageBox.Show($@"Product:{d.product},Quantity:""{d.orderQty}"",Price:""{d.price}""");
            orderReturn.Add(d);                
        }
        this.DialogResult = DialogResult.Yes;
        this.Close();
    }
    catch (Exception ex)
    {
        if (ex.ToString().Contains("being used by another process"))
        {
            MessageBox.Show("Error: Please close the file in Excel and try again");
        }
        else
        {
            MessageBox.Show(ex.ToString());
        }
    }
}

I want the user to be able to just pass in a file and then select the relevant columns which relate to the corresponding values and then read in the data ignoring any columns that haven't been selected.

Hope this all makes sense, is something like this possible within LINQtoCSV

Answer 1

You have to add IgnoreUnknownColumns = true to your CsvFileDescription

CSV:

product,price,someColumn,orderQty,value,otherColumn
my product,$123,xx,2,$246,aa
my other product,$10,yy,3,$30,bb

Working code (I modified your code a little bit, to run it in a console)

using System;
using System.Collections.Generic;
using LINQtoCSV;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            csvParse();
            Console.ReadLine();
        }

        private static void csvParse()
        {
            string fileName = "../../../test.csv"; // provide a valid path to the file

            CsvContext cc = new CsvContext();

            CsvFileDescription inputFileDescription = new CsvFileDescription
            {
                SeparatorChar = ',',
                FirstLineHasColumnNames = true,
                IgnoreUnknownColumns = true // add this line
            };

            IEnumerable<orderProduct> fromCSV = cc.Read<orderProduct>(fileName, inputFileDescription);

            foreach (var d in fromCSV)
            {
                Console.WriteLine($@"Product:{d.product},Quantity:""{d.orderQty}"",Price:""{d.price}""");
            }
        }
    }

    public class orderProduct
    {
        public orderProduct() { }
        public string product { get; set; }
        public string price { get; set; }
        public string orderQty { get; set; }
        public string value { get; set; }

        public string calculateValue()
        {
            return (Convert.ToDouble(price) * Convert.ToDouble(orderQty)).ToString();
        }
    }
}

Output:

Product:my product,Quantity:"2",Price:"$123"
Product:my other product,Quantity:"3",Price:"$10"

If your properties have different names than CSV columns, you should use CsvColumn attribute:

public class OrderProduct
{
    [CsvColumn(Name = "product")]
    public string Product { get; set; }

    [CsvColumn(Name = "price")]
    public string Price { get; set; }

    [CsvColumn(Name = "orderQty")]
    public string OrderQuantity { get; set; }

    public string Value { get; set; }

    public string calculateValue()
    {
        return (Convert.ToDouble(Price) * Convert.ToDouble(OrderQuantity)).ToString();
    }
}

Or if you prefer mapping columns by their indices:

public class OrderProduct
{
    [CsvColumn(FieldIndex = 0)]
    public string Product { get; set; }

    [CsvColumn(FieldIndex = 1)]
    public string Price { get; set; }

    [CsvColumn(FieldIndex = 2)]
    public string OrderQuantity { get; set; }

    public string Value { get; set; }

    public string calculateValue()
    {
        return (Convert.ToDouble(Price) * Convert.ToDouble(OrderQuantity)).ToString();
    }
}

If you have to specify the columns on the fly, the only way seems to be to read raw data and process it yourself (the solution is based on this article):

internal class DataRow : List<DataRowItem>, IDataRow
{
}
...
int productColumnIndex = 0; // your users will provide it
var fromCSV = cc.Read<DataRow>(fileName);
foreach (var row in fromCSV)
{
    var orderProduct = new OrderProduct
    {
        Product = row[productColumnIndex].Value,
    };

    Console.WriteLine(orderProduct.Product);
}