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 :

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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:

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
[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:

?
01
02
03
04
05
06
07
08
09
10
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/