Importing Data Files with Linq

In my previous Linq post I discussed using Linq with Regular expressions and how much less code was needed. In this post we’ll again see how Linq can be used to speed up and simplify development.

There are many situations where you need to read data from a file into memory or a database. Lets consider this scenario.

We have a tab delimited data file that has been exported from some web or windows application etc. Below is the sample file.

carrots    4    0.64
beans      3    0.12
oranges    7    1.02
apples     5    0.87

This file includes three columns. Column 1is the name of the product. Column 2 is the quantity and Column 3 is the unit price. We would also like a total price column derived from column 2 being multiplied by column 3.

Our aim here is to load these into our application programmatically and then bind them to a gridview. Normally you would have to read the file in line by line then do a split on each line, enter each line into a prebuilt class or datatable then add a new column and multiply the quantity by the price to obtain the total. However by using a nice little extension helper method, all of this can be rolled into just a few lines of code.

public static IEnumerable<string> ReadLinesFromFile(string filename)
{
    using (StreamReader reader = new StreamReader(filename))
    {
        while (true)
        {
            string s = reader.ReadLine();
            if (s == null)
                break;
            yield return s;
        }
    }
}

Above is the extension method to convert lines of a file into a IEnumerable list of strings.

var products = from line in ReadLinesFromFile(@"c:\import.txt")
               let item = line.Split('\t')
               select new
               {
                   Product = item[0],
                   Quantity = Convert.ToInt32(item[1]),
                   Price = Convert.ToDecimal(item[2]),
                   Total = Convert.ToInt32(item[1]) * Convert.ToDecimal(item[2])
               };
GridView1.DataSource = products;
GridView1.DataBind();

What this gives us is a list of products, with each one having 4 properties associated with it. We can then bind this straight to a gridview by setting the datasource of the gridview to products and calling the DataBind() method. This will display a table with 4 columns with the headers Product, Quantity, Price and Total.

The Linq statement can then be modified in many ways to accommodate different file formats (eg. CSV or fixed width) with a few simple changes.

This is just another way Linq is making things easier!

Schotime

11 Responses to Importing Data Files with Linq

  1. Paul says:

    Is it possible to show us an example of a fixed reading linq?

  2. Paul says:

    fixed width i meant.

  3. Schotime says:


    var products = from line in ReadLinesFromFile(@"c:\import.txt")
    select new
    {
    ProductName = line.Substring(0, 12),
    Quantity = line.Substring(12, 3),
    Price = line.Substring(15, 5),
    Total = line.Substring(20, 10)
    };

  4. Joel says:

    You know, you can simplify your code a bit more by replacing…

    new char[] { '\t' }

    With simply…

    '\t'

  5. Schotime says:

    Good Suggestion mate.
    Updated Post.

  6. Jonathan says:

    Question: if I use the ReadLinesFromFile method, does that read the entire file into memory, and allow it to be accessed as an enumerated list, or does it read one line at a time, and pass each to the query to operate? I have to operate on massive files, and will get out of memory exceptions if it is the former. Thanks!

  7. Chris says:

    Can someone convert the above code into VB please? Thanks!!!

  8. jlon says:

    can have some vb.net importing codes pls… for importing data to the datagridview

  9. Schotime says:

    Don’t know VB sorry. Plenty of resources on the net for this.

  10. tim says:

    Is the code supposed to be put into a windows form application? I get a error with

    GridView1.DataBind();

    ‘System.Windows.Forms.DataGridView’ does not contain a definition for ‘DataBind’ and no extension method ‘DataBind’ accepting a first argument of type ‘System.Windows.Forms.DataGridView’ could be found (are you missing a using directive or an assembly reference?)

  11. Schotime says:

    It was meant for a ASP.net webforms application however you should be able to adjust the code accordingly to pump it into a forms gridview. If I have time, I’ll post a snippet.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>