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

It fails for any CSV file that doesn't have the column headers that are contained in the orderProduct class. — benjiiiii, Nov 02 '18 at 14:12
What about changing `FirstLineHasColumnNames = true` to `FirstLineHasColumnNames = false`? How is the user going to select the relevant columns? — Pedro Gaspar, Nov 02 '18 at 14:14

Andrzej Gis · Accepted Answer · 2018-11-02T15:25:17.720

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);
}

What my issue is though, is what if the user only has columns for product, price and qty named slightly different to what they are in my class. How can I allow the user to specify a column index to correspond to each value? — benjiiiii, Nov 02 '18 at 14:26
@benjiiiii Look at the last code sample, does it solve your problem? — Andrzej Gis, Nov 02 '18 at 14:29
Ooo that's cool - I'll double check that ensuring the users put things in the correct columns will solve the problem — benjiiiii, Nov 02 '18 at 14:35
This works, the only issue with it is the fact that columns have to be in the exact order now. I'm trying to come up with a way of reading the top row, then allowing a user to specify which column relates to what. Then reading the data in. I'm thinking that I could create a new class on the fly with the data the user inputs and then read into this class? — benjiiiii, Nov 02 '18 at 14:53
Perfect, I'll test this tomorrow but from what I cans see it's what I'm after. Once I've tested I'll upvote and accept the answer. — benjiiiii, Nov 02 '18 at 16:25
sorry for the delay in getting back to you. This is definitely what I want, the only bit I'm struggling on is writing the column names to a list. If I set FirstLineHasColumn names to false it will read them as the first row. However, I can't work out how to get a count of columns. Is there another method you know for reading the colun names seperately. Or for getting a count of them? — benjiiiii, Nov 05 '18 at 10:42

Allowing a user to select column headers to import

1 Answers1