Click here to Skip to main content
Click here to Skip to main content

LINQ to Text or CSV Files - Part 1

, 1 Jan 2009
Rate this:
Please Sign up or sign in to vote.
A simple utility to LINQ against CSV files

Introduction

With the introduction of LINQ in C# 3.0, the life of a developer has become very easy when it comes to querying collections. In this article, I will show you a quick and easy way to query large CSV files.

Background

Imagine you have a very large CSV file. Loading the entire file into memory (e.g. DataSet) and querying it with LINQ would be a huge overhead. So I thought why not use StreamReader on the file instead.

Using the Code

I have implemented IEnumerable<string[]> and for every call to GetEnumerator, a line is read from CSV file. This class returns an enumerable string[].

public class TextFileReader : IEnumerable<string[]>
{
    private string _fileName = string.Empty;
    private string _delimiter = string.Empty;

    public TextFileReader(string fileName, string delimiter)
    {
        this._fileName = fileName;
        this._delimiter = delimiter;
    }

    #region IEnumerable<string[]> Members

    IEnumerator<string[]> IEnumerable<string[]>.GetEnumerator()
    {
        using (StreamReader streamReader = new StreamReader(this._fileName))
        {
            while (!streamReader.EndOfStream)
            {
                yield return streamReader.ReadLine().Split(new char[] { ',' });
            }
        }
    }

    #endregion

    #region IEnumerable Members

    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IEnumerable)((IEnumerator<t />)this)).GetEnumerator();
    }

    #endregion
}

Using this class is very simple. Just initialize the class with the CSV file name and you are all set to LINQ it using standard operator. The following code shows how to query all the rows in the CSV file with the first column text starting with 'a'.

TextFileReader reader1 = new TextFileReader(@"Sample.txt", ",");

            var query1 = from it1 in reader1
                        where it1[0].StartsWith("a")
                        select new { Name = it1[0], Age = it1[1], 
			EmailAddress = it1[2] };

            foreach (var x1 in query1)
            {
                Console.WriteLine(String.Format("Name={0} Age={1} 
		EmailAddress = {2}", x1.Name, x1.Age, x1.EmailAddress));
            }
}

Let's make things a little bit more interesting. Suppose you know the format of the CSV file and have defined business classes corresponding to the CSV files. In this case, the above class is not very useful. Let's modify the 'TextFilereader' to have generic support.

public class TextFileReader<T> : IEnumerable<T> where T : new()
{
    private string _fileName = string.Empty;
    private string _delimiter = string.Empty;
    private Dictionary<String, PropertyInfo> _headerPropertyInfos = 
		new Dictionary<string, PropertyInfo>();
    private Dictionary<String, Type> _headerDaytaTypes = new Dictionary<string, Type>();

    public TextFileReader(string fileName, string delimiter)
    {
        this._fileName = fileName;
        this._delimiter = delimiter;
    }

    #region IEnumerable<string[]> Members

    IEnumerator<T> IEnumerable<T>.GetEnumerator()
    {
        using (StreamReader streamReader = new StreamReader(this._fileName))
        {
            string[] headers = streamReader.ReadLine().Split(new String[] 
		{ this._delimiter }, StringSplitOptions.None);
            this.ReadHeader(headers);

            while (!streamReader.EndOfStream)
            {
                T item = new T();

                string[] rowData = streamReader.ReadLine().Split(new String[] 
		{ this._delimiter }, StringSplitOptions.None);

                for (int index = 0; index < headers.Length; index++)
                {
                    string header = headers[index];
                    this._headerPropertyInfos[header].SetValue
			(item, Convert.ChangeType(rowData[index], 
			this._headerDaytaTypes[header]), null);
                }
                yield return item;
            }
        }
    }

    #endregion

    #region IEnumerable Members

    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IEnumerable)((IEnumerator<T>)this)).GetEnumerator();
    }

    #endregion

    private void ReadHeader(string[] headers)
    {
        foreach (String header in headers)
        {
            foreach (PropertyInfo propertyInfo in (typeof(T)).GetProperties())
            {
                foreach (object attribute in propertyInfo.GetCustomAttributes(true))
                {
                    if ( attribute is ColumnAttribute )
                    {
                        ColumnAttribute columnAttribute = attribute as ColumnAttribute;
                        if (columnAttribute.Name == header)
                        {
                            this._headerPropertyInfos[header] = propertyInfo;
                            this._headerDaytaTypes[header] = columnAttribute.DataType;
                            break;
                        }
                    }
                }
            }
        }
    }
}
}

Now let's define a business class 'Person'. This class will hold the information stored in the CSV file. Its properties are marked with 'ColumnAttribute' to map the columns with CSV columns. The code is self explanatory.

public class Person
{
    private string _name;

    [Column(Name = "Name", DataType = typeof(String))]
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }
    private int _age;

    [Column(Name = "Age", DataType = typeof(Int32))]
    public int Age
    {
        get { return _age; }
        set { _age = value; }
    }
    private string _emailAddress;

    [Column(Name = "EmailId", DataType = typeof(String))]
    public string EmailAddress
    {
        get { return _emailAddress; }
        set { _emailAddress = value; }
    }
}

[AttributeUsage(AttributeTargets.Property)]
public class ColumnAttribute : Attribute
{
    private string _name;

    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }
    private Type _dataType;

    public Type DataType
    {
        get { return _dataType; }
        set { _dataType = value; }
    }
}

Using this class is again very simple. Just initialize the class with the CSV file name and the delimiter and LINQ it. The following code shows how to query all the rows in the CSV file with the first column Age greater than 2.

    TextFileReader<Person> reader2 = 
	new TextFileReader<Person>(@"SampleWithHeader.txt", ",");
    var query2 = from it2 in reader2
                 where it2.Age > 2
                 select it2;

    foreach (var x2 in query2)
    {
        Console.WriteLine(String.Format("Name={0} Age={1} 
		EmailAddress = {2}", x2.Name, x2.Age, x2.EmailAddress));
    }

Points of Interest

Well, this may not be the best way to implement this. I am also into the process of learning LINQ. Please feel free to give your comments or suggestions. In my next article, I will show you how to write a custom query provider for CSV files. Please stay tuned.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Alomgir Miah A

United States United States
No Biography provided

Comments and Discussions

 
Questionthousand separator issue PinmemberCyclone5520-Jun-12 17:27 
GeneralMy vote of 5 PinmemberRJPalkar26-Oct-11 8:55 
QuestionOne question about headers Pinmemberanderdw27-Aug-11 5:55 
AnswerRe: One question about headers PinmemberAlomgir Miah A7-Aug-11 6:11 
GeneralRe: One question about headers Pinmemberanderdw28-Aug-11 5:21 
GeneralReally, really amazing utility. Pinmembersmt5217-Mar-11 12:27 
GeneralRe: Really, really amazing utility. PinmemberAlomgir Miah A24-Mar-11 13:04 
GeneralAwesome - thanks. Pinmembercheartwell1-Feb-10 7:32 
GeneralRe: Awesome - thanks. PinmemberAlomgir Miah A1-Mar-10 4:21 
GeneralGood to see you here! PinmemberDigvijay Chauhan11-Sep-09 4:33 
GeneralThanks for the codez PinmemberMycroft Holmes20-Dec-08 14:56 
GeneralRe: Thanks for the codez [modified] PinmemberAlomgir Miah Abdul20-Dec-08 20:56 
GeneralCSV and field separator PinmemberTobiasP14-Oct-08 1:52 
GeneralElegant! PinmemberPat Tormey14-Oct-08 1:06 
GeneralJust a comment... PinmemberSeth Morris13-Oct-08 22:17 
GeneralRe: Just a comment... PinmemberTobiasP14-Oct-08 3:07 
GeneralRe: Just a comment... PinmemberAlomgir Abdul Miah14-Oct-08 4:00 
GeneralRe: Just a comment... PinmemberMycroft Holmes20-Dec-08 14:51 
GeneralExcellent, but how about... PinmemberJohn O'Halloran13-Oct-08 17:23 
GeneralRe: Excellent, but how about... PinmemberAlomgir Abdul Miah13-Oct-08 17:55 
GeneralGreat article!!! PinmemberAchintya Jha12-Oct-08 14:16 
GeneralGreat article! PinmemberIamBond00712-Oct-08 2:25 
GeneralNice! PinmemberDale Visser12-Oct-08 1:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 1 Jan 2009
Article Copyright 2008 by Alomgir Miah A
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid