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.
