CSV file import in .NET

I use this CsvReader class for loading CSV data, it's free, fast and easy to use.

For anything like that, I always turn to FileHelpers. Why reinvent the wheel and struggle around with delimiters and all those gnary details when there's a solution out there that deals with all those low-level aspects already??

Just define the class you want to import (the list of fields, basically), define the delimiters and ignored fields etc., and then just call FileHelperEngine<T>.ReadFile() and be done with it.

Highly recommended!

One interesting and elegant option is the LINQToCSV project.

   class DynamicCSV : DynamicObject
{
private Dictionary<String, int> _fieldIndex;
private string[] _RowValues;
public DynamicCSV(string currentRow, Dictionary<string, int> fieldIndex)
{
    _RowValues = currentRow.Split(',');
    _fieldIndex = fieldIndex;
}


public YOURLINQOBject export() {
  return new YOURLINQOBject() 
}

public DynamicCSV(string[] values, Dictionary<string, int> fieldIndex)
{
    _RowValues = values;
    _fieldIndex = fieldIndex;
    //var that = new DynamicObject();
    //that.TrySetMember(
}



private string TransitionName(string inputFieldName)
{
    return Repo.TransitionThisName(inputFieldName);
}

public override bool TryGetMember(GetMemberBinder binder, out Object result)
{
    var fieldName = binder.Name.ToUpperInvariant();
    if (_fieldIndex.ContainsKey(fieldName))
    {
        result = _RowValues[_fieldIndex[fieldName]];
        return true;
    }
    result = null;
    return false;
}


public override bool TrySetMember(SetMemberBinder binder, object value)
{
    var fieldName = binder.Name.ToUpperInvariant();
    if (_fieldIndex.ContainsKey(fieldName))
    {
        _RowValues[_fieldIndex[fieldName]] = value.ToString();
        return true;
    }
    return false;
}

public override string ToString()
{
    var sb = new StringBuilder();
    sb.AppendLine("*******************************************");
    foreach (var field in _fieldIndex)
    {
        sb.AppendLine(field.Key + " --- " + _RowValues[_fieldIndex[field.Key]]);
    }
    sb.AppendLine("*******************************************");
    return sb.ToString();
}

}

Then in order to load all the lines together

 public class DynamicCSVEnum : IEnumerable
{
    private PeopleEnumerator _peopleEnum;
    public DynamicCSVEnum(string filename)
    {
        _peopleEnum = new PeopleEnumerator(filename);
    }
    IEnumerator IEnumerable.GetEnumerator( )
    {
        // return a PeopleEnumerator
        return _peopleEnum;
    }
}


public class PeopleEnumerator : IEnumerator
{
    //private List<MKG> theList;
    //private int _currentIndex;

    private Microsoft.VisualBasic.FileIO.TextFieldParser _FileStream;
    private Dictionary<string, int> _FieldNames;
    private DynamicCSV _CurrentRow;
    private string _filename;

    public PeopleEnumerator( string filename)
    {
        _filename = filename;
        //theList = new List<MKG>();
        //theList.Add(new MKG() { Id = 0 });
        //theList.Add(new MKG() { Id = 1 });
        //theList.Add(new MKG() { Id = 2 });
        //_currentIndex = -1;
        GetEnumerator();
    }

    private void GetEnumerator()
    {
        _FileStream = new Microsoft.VisualBasic.FileIO.TextFieldParser(_filename);
        _FileStream.Delimiters = new String[] { "," };
        _FileStream.HasFieldsEnclosedInQuotes = true;
        _FileStream.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
        var fields = _FileStream.ReadFields();
        _FieldNames = new Dictionary<string, int>();
        for (var i = 0; i <= fields.Length - 1; i++)
        {
            _FieldNames.Add(GetSafeFieldName(fields[i]), i);
        }
        _CurrentRow = new DynamicCSV(_FileStream.ReadFields(), _FieldNames);
    }
    public void Reset()
    {
        //_currentIndex = 0;
        _FileStream.Close();
        GetEnumerator();
    }

    public bool MoveNext()
    {
        //_currentIndex++;
        //if (_currentIndex < theList.Count)
        //{

        //    return true;
        //}
        //return false;
        var line = _FileStream.ReadFields();
        if (line != null && line.Length > 0)
        {
            _CurrentRow = new DynamicCSV(line, _FieldNames);
            return true;
        }
        else
        {
            return false;
        }
    }

    public object Current
    {
        //get
        //{
        //    return theList[_currentIndex];
        //}
        //set
        //{
        //    theList[_currentIndex] = (MKG)value;
        //}
        get { return _CurrentRow; }

    }

    string GetSafeFieldName(string input)
    {
        return input.Replace(" ", "").Replace("_", "").Replace(".","").Replace("#","").Replace("/","").Replace("\\","").ToUpperInvariant();
    }

}

Then all it takes is a simple

            var foo = new DynamicCSVEnum(savedFileName);

            foreach (DynamicCSV item in foo)
            {
                retVal.Add(item.LinqObjectExport());
            }

            foreach (var item in retVal)
            {
                item.ClientSet = dsid;
            }
            repo.InsertDataSet(retVal);