C# generic CSV import using Dynamic - by Michael Mogensen https://www.linkedin.com/profile/view?id=ADEAAA9m7IYBHB9QYIVPlTUC1S474Ei-0vwM2YY

GeneralImport.cs

using System;
using System.IO;
using System.Dynamic;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ImportTests.Interfaces;

namespace ImportTests.Imports
{
    public abstract class GeneralImport : IGeneralImport
    {
        /// <summary>
        /// Types.
        /// </summary>
        private enum ECRUDAction { eUNKNOWN, eCREATE, eUPDATE, eDELETE }; // N, U, D from first field in import file.
        /// <summary>
        /// Props.
        /// </summary>
        protected string ImportFile { get; set; }
        public bool IsReady { get; set; }
        /// <summary>
        /// Ctor.
        /// </summary>
        /// <param name="file"></param>
        public GeneralImport(string file)
        {
            ImportFile = file;
            IsReady = File.Exists(ImportFile);
        }
        /// <summary>
        /// Abstract methods to implement.
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public abstract void DbInsertEntityObject(ExpandoObject record);
        public abstract void DbUpdateEntityObject(ExpandoObject record);
        public abstract void DbDeleteEntityObject(ExpandoObject record);
        public abstract ExpandoObject CreateEntityObject(string[] data);
        public abstract ExpandoObject DatawashEntityObject(ExpandoObject record);
        public abstract bool IsEntityObjectValid(ExpandoObject record);

        /// <summary>
        /// Get action.
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        private ECRUDAction GetAction(ExpandoObject record)
        {
            object value;
            var gotIt =
                ((IDictionary<String, Object>)record).
                TryGetValue("Action", out value);
            if (gotIt)
            {
                switch (value.ToString())
                {
                    case "N": return ECRUDAction.eCREATE;
                    case "U": return ECRUDAction.eUPDATE;
                    case "D": return ECRUDAction.eDELETE;
                }
            }
            return ECRUDAction.eUNKNOWN;
        }
        /// <summary>
        /// General import.
        /// </summary>
        public void Import()
        {
            if (!IsReady)
                return;

            File.ReadAllLines(ImportFile, ASCIIEncoding.Default).
                Select<string, ExpandoObject>(line =>
                    { return CreateEntityObject(line.Split(';')); }). // Only statement lambda compiles here.
                    ToList().
                    ForEach(record =>
                    {
                        if (IsEntityObjectValid(record))
                        {
                            var washedRecord = DatawashEntityObject(record);
                            // Perform relevant CRUD action.
                            switch(GetAction(record))
                            {
                                case ECRUDAction.eCREATE:
                                    DbInsertEntityObject(washedRecord);
                                    break;
                                case ECRUDAction.eUPDATE:
                                    DbUpdateEntityObject(washedRecord);
                                    break;
                                case ECRUDAction.eDELETE:
                                    DbDeleteEntityObject(washedRecord);
                                    break;
                                default:
                                    LogEntityObjectError(record, "Unknown action");
                                    break;
                            }
                        }
                        else
                        {
                            LogEntityObjectError(record, "Invalid");
                        }
                    });
        }
        /// <summary>
        /// Log record.
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        public void LogEntityObjectError(ExpandoObject record, string message = null)
        {
            var properties = ListProperties(record);
            // TODO.
        }
        /// <summary>
        /// Func to list properties and their values, like MyValue = 123.
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        protected Func<ExpandoObject, string> ListProperties = record =>
        {
            var keyValues = ((IDictionary<String, Object>)record).ToList().Select(prop => string.Format("{0} = {1}", prop.Key, prop.Value)).Aggregate((kv1, kv2) => string.Format("{0}, {1}", kv1, kv2));
            return string.Format("{{ {0} }}", keyValues);
        };
        /// <summary>
        /// Func to trim raw data.
        /// </summary>
        protected Func<string[], int, string> Data = (data, id) => data[id].Trim();
    }
}

IGeneralImport.cs

using System.Dynamic;

namespace ImportTests.Interfaces
{
    public interface IGeneralImport
    {
        ExpandoObject CreateEntityObject(string[] data);
        void Import();
    }
}

SomeSpecificCsv.txt

U;ADCDEF  ;Carmen                   ;Monet                    ;2705841234
N;ADCDEG  ;Vasbinder                ;Lawanda                  ;1206871234
D;ADCDEH  ;Chmiel                   ;Reuben                   ;2807561234

SomeSpecificImport.cs

using System;
using System.Dynamic;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data; //For Database 
using ImportTests.Interfaces;

namespace ImportTests.Imports
{
    /// <summary>
    /// 
    /// The only class needed to be filled in to make 6B import job work.
    /// 
    /// Call it like this:
    /// 
    /// var someSpecificImport = new SomeSpecificImport(file);
    /// someSpecificImport.Import();
    /// 
    /// </summary>
    public sealed class SomeSpecificImport : GeneralImport
    {
        public SomeSpecificImport(string file) : base(file)
        {}
        /// <summary>
        /// Setup how record looks from datafile.
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public override ExpandoObject CreateEntityObject(string[] data) // TODO: Finish this method.
        {
            var col = 0;
            dynamic record = new ExpandoObject();
            record.Action = Data(data, col++);
            record.UserId = Data(data, col++);
            record.LastName = Data(data, col++);
            record.FirstName = Data(data, col++);
            record.SSN = Data(data, col++);
            
            // ...
            // Etc.

            return record;
        }
        /// <summary>
        /// Validate record using appropriate BL.
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        public override ExpandoObject DatawashEntityObject(ExpandoObject record) // TODO: Finish this method.
        {
            dynamic washedRecord = new ExpandoObject();
            var keyValues = ((IDictionary<String, Object>)record).Select(prop => new { Key = prop.Key, Value = prop.Value.ToString() }).ToList();
            keyValues.ForEach(prop =>
            {
                switch (prop.Key)
                {
                    case "Action":
                        washedRecord.Action = prop.Value.ToUpper();
                        break;
                    case "UserId":
                        washedRecord.UserId = prop.Value.ToUpper();
                        break;
                    case "LastName":
                        washedRecord.LastName = prop.Value;
                        break;
                    case "FirstName":
                        washedRecord.FirstName = prop.Value;
                        break;
                    case "SSN":
                        washedRecord.SSN = prop.Value;
                        break;

                    // ...
                    // Etc.

                }
            });
            return washedRecord;
        }
        /// <summary>
        /// Return T if record is valid and F if not. Use appropriate BL.
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        public override bool IsEntityObjectValid(ExpandoObject record) // TODO: Finish this method.
        {
            return true;
        }

        // ********************************************************************************
        // * INSERT.                                                                      *
        // ********************************************************************************

        /// <summary>
        /// Insert database record.
        /// </summary>
        /// <param name="record"></param>
        public override void DbInsertEntityObject(ExpandoObject record) // TODO: Finish this method.
        {
            var db = DatabaseFactory.CreateDatabase("SomeDb");
            var dbCommand = db.GetStoredProcCommand("InsertMyEntityObject");
            db.AddInParameter(dbCommand, "SomeParam1", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "SomeParam2", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "SomeParam3", DbType.Int32, 0);
            var numRowsAffected = db.ExecuteNonQuery(dbCommand);
        }

        // ********************************************************************************
        // * UPDATE.                                                                      *
        // ********************************************************************************

        /// <summary>
        /// Update database record.
        /// </summary>
        /// <param name="record"></param>
        public override void DbUpdateEntityObject(ExpandoObject record) // TODO: Finish this method.
        {
            var db = DatabaseFactory.CreateDatabase("SomeDb");
            var dbCommand = db.GetStoredProcCommand("UpdateMyEntityObject");
            db.AddInParameter(dbCommand, "SomeParam1", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "SomeParam2", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "SomeParam3", DbType.Int32, 0);
            var numRowsAffected = db.ExecuteNonQuery(dbCommand);
        }

        // ********************************************************************************
        // * DELETE.                                                                      *
        // ********************************************************************************

        /// <summary>
        /// Delete database record.
        /// </summary>
        /// <param name="record"></param>
        public override void DbDeleteEntityObject(ExpandoObject record) // TODO: Finish this method.
        {
            var db = DatabaseFactory.CreateDatabase("SomeDb");
            var dbCommand = db.GetStoredProcCommand("DeleteMyEntityObject");
            db.AddInParameter(dbCommand, "SomeParam1", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "SomeParam2", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "SomeParam3", DbType.Int32, 0);
            var numRowsAffected = db.ExecuteNonQuery(dbCommand);
        }

    }
}

/*
    // TODO.
    System.Diagnostics.Trace.WriteLine(ListProperties(record));
 */

Test.cs

using ImportTests.Imports;

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace ImportTests
{
    [TestClass]
    public class Test
    {

        private string file = "SomeSpecificCsv.txt";

        [TestMethod]
        public void ReadTxt()
        {
            var someSpecificImport = new SomeSpecificImport(file);
            Assert.IsTrue(someSpecificImport.IsReady, string.Format("someSpecificImport.IsReady = {0}", someSpecificImport.IsReady));

            someSpecificImport.Import();
        }
    }
}