file helpers

More than one time you need to let users / application import bulk data in your system .

I have had many cases  – data is old data from old software, that is exported in some text files, usually csv.

In order to import fast the data, I used FileHelpers from http://www.filehelpers.com/

Let’s say you have the following data for an car evidence :

Date, StartMiles, Destination, EndMiles

And the data comes from an Excel  that has those columns. The requirements is the user can copy/paste from Excel data

When they copy the data comes in this form

01/01/2010     1000 Washington 1550

02/01/2010     1550 Dallas 2550

and so on.

It is clear that you :

  1. have a class with Date, StartMiles, Destination, EndMiles
  2. accomodate for space – but how to perform various data separator ( we suppose that first is the day, then comes the month).

Now the code in logical steps :

1. Accomodate for dates :

internal class ConvertDate : ConverterBase
{

        /// <summary>
        /// different forms for date separator : . or / or space
        /// </summary>
        /// <param name="from">the string format of date - first the day</param>
        /// <returns></returns>

        public override object StringToField(string from)
        {
            DateTime dt;

            if (DateTime.TryParseExact(from, "dd.MM.yyyy", null, DateTimeStyles.None, out dt))
                return dt;

            if (DateTime.TryParseExact(from, "dd/MM/yyyy", null, DateTimeStyles.None, out dt))
                return dt;

            if (DateTime.TryParseExact(from, "dd MM yyyy", null, DateTimeStyles.None, out dt))
                return dt;

            throw new ArgumentException("can not make a date from " + from, "from");

        }
}

2. Create the class that will hold one record:

    [IgnoreEmptyLines(true)]
    [DelimitedRecord(",")]
    internal class DestinationReader
    {
        //[FieldConverter(ConverterKind.Date,"dd.MM.yyyy")]
        [FieldConverter(typeof(ConvertDate))]
        public DateTime Date;
        [FieldConverter(ConverterKind.Int32)]
        public int StartMiles;

        [FieldQuoted(QuoteMode.OptionalForBoth)]
        public string Destination;

        [FieldConverter(ConverterKind.Int32)]
        public int  EndMiles;
    }

3. Now read the entire string:

            string Text = text that comes from the user
            string TextDelim = Text.Substring(10, 1);// the date has 10 chars - so the eleven is the separator
            while (Text.IndexOf(TextDelim + TextDelim) > 0)//consolidate for 2 delimiters
            {
                Text = Text.Replace(TextDelim + TextDelim, TextDelim);
            }
            DelimitedFileEngine<DestinationReader> flh=new DelimitedFileEngine<DestinationReader>();
            flh.Options.Delimiter = TextDelim;

            var data =flh.ReadString(Text);

In data you have a list of DestinationReader
So for any structured import of data use FileHelpers from http://www.filehelpers.com/