How to map columns from CSV to the class properties in C#?

Question

TO DO

To create a console application, which can read a CSV file and auto determine to which of the three CSV files(varies in column data) it belongs to.

I have created a console application, which is currently reading all the csv files from the directory using SmartXLS library.

How can I read in the data for each file into a list and have a simple mechanism for mapping each "column" of data with the associated class property (i.e. column name) I have defined in my class?

File structure 1:

Usage Date,Product Name,User Id,Tokens Used

1. 20160428,Samsung,dorman@stackoverflow.com

File structure 2:

Reason, Month, Adjustment Date, Transaction ID, Tokens Adjusted, Product Name, Comment Added

1. Off network, 15-Oct, 31-Oct-15,73820274918-230934049372,045, Xbox, Bridge Design for InfraWorks 360.

File structure 3:

Usage Date, Product Name, Product Version, User Id, Machine Name, Server Name, Tokens Used, Hours Used

1. 20161027, Gamepro, 2016, 11597, BC318010, LA847012, 6, 0.1

Kindly, help me in re-writing the function.

Class:

public class Token
     {
            public DateTime Usagedate { get; set; }
            public string Product_name { get; set; }
            public string Product_Version { get; set; }
            public string Userid { get; set; }
            public string User_name { get; set; }
            public string Machine_name { get; set; }
            public string Server_name { get; set; }
            public string Tokens_used { get; set; }
            public string Use_count { get; set; }

        }

App:

namespace ConsoleApp
{
    class token
    {
        public List<Token> Tokens { get; set; }
        public void TokenData()
        {
            Console.WriteLine("Begin Harvesting");

            CategorizeData();//function
        }

 public void CategorizeData()
        {
            int count = 0;

            Tokens = new List<Token>();

            var files = Directory.EnumerateFiles(@"C:\Users\Projects", "*.csv");

            foreach (string file in files)
            {

                SmartXLS.WorkBook WB = new WorkBook();
                WB.readCSV(file);

                DataTable dt = WB.ExportDataTable();

                string dtSTR;
                DataRow dr;
                DataColumn dc;
                for (int i = 1; i < dt.Rows.Count; i++)
                {

                    dr = dt.Rows[i];

                   try
                   {
                       // Map Columns with the class properties
                       // read them to the list
                     dtSTR = dr[0].ToString();
                    if (string.IsNullOrEmpty(dtSTR)) continue;

                    var tkn = new Token();


                    tkn.Usagedate = ParseDateTime(dtSTR);
                    tkn.Product_name = dr[1].ToString();
                    tkn.Userid = dr[2].ToString();
                    tkn.Tokens_used = dr[3].ToString();
                    tkn.Reason = dr[4].ToString();


                    Tokens.Add(tkn); 
                    count++;    


                    }
                    catch (Exception ex)
                    { }


                }

            }
        }
    } 

}

Answer 1

I have written a CSV mapper to class before, but there is a Component called CSVHelper which I found that is very fast and actually works better than mine. and it has lots of features

string content = "";
var files = Directory.EnumerateFiles(@"C:\Users\Projects", "*.csv");
foreach (string file in files)
    content += System.IO.File.ReadAllText(file) + Environment.NewLine;

using(TextReader sr = new StringReader(content))
{
   var csv = new CsvReader(sr);
      csv.Configuration.RegisterClassMap<TokenMap>();
      var records = csv.GetRecords<Token>();
}





public class TokenMap : CsvClassMap<Token>
{
    public TokenMap()
    {
        Map(m => m.Product_name );
        Map(m => m.Product_Version);
        Map(m => m.Userid);
        Map(m => m.User_name);
        Map(m => m.Machine_name);
        Map(m => m.Server_name);
        Map(m => m.Tokens_used);
        Map(m => m.Use_count);
    }
}