How to Read CSV file using LINQ to CSVProvider in C#

Introduction

In this article we will focus, how to read CSV file using LINQ to CSVProvider.I hope we know read CSV file using StreamReader or any other readers in C#. But when we are use the LINQ to CSVProvider, and then we could get additional features to query data in LINQ as like Sql Query.

Implementation

Create CSV File

Let’s create a simple command delimited csv file with few rows     and columns, the file like be below,

Create Demo Application

Open Visual studio and create project called as LINQToCSV.

Add a new class call CSVRow.cs for support to LINQ.

CSVRow.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LINQToCSV
{
    public class CSVRow
    {
        List<object> columns;

        public CSVRow()
        {
            columns = new List<object>();
        }

        internal void AddColumn(object value)
        {
            columns.Add(value);
        }

        public object this[int index]
        {
            get { return columns[index]; }
        }

        public string GetString(int index)
        {
            if (columns[index] is DBNull)
            {
                return null;
            }
            return columns[index].ToString();
        }

        public int Count
        {
            get { return this.columns.Count; }
        }
    }
}

The CSVRow is supportive class for CSV Provider class. Within this class we have list object keep the rows and also few useful methods to query data using LINQ.

Then another a new class to implement CSVProvider for support LINQ Query operations. New class name is LINQToCSVProvider.cs.

LINQToCSVProvider.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace LINQToCSV
{
    public class LINQToCSVProvider : IEnumerable<CSVRow>
    {

        private string delimiter;
        private string filePath;
        private List<CSVRow> rows;

        public LINQToCSVProvider()
        {
            rows = new List<CSVRow>();
        }
        public static LINQToCSVProvider Create(string filePath, string delimiter)
        {
            LINQToCSVProvider csvProvider = new LINQToCSVProvider();
            csvProvider.filePath = filePath;
            csvProvider.delimiter = delimiter;
            return csvProvider;
        }
        private void BuildRows()
        {
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Text;HDR=YES;FMT={1}""";
            connectionString = string.Format(connectionString, System.IO.Path.GetDirectoryName(filePath), delimiter);
            rows.Clear();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM " + System.IO.Path.GetFileName(filePath), conn))
                {

                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            CSVRow newRow = new CSVRow();
                            for (int count = 0; count < reader.FieldCount; count++)
                            {
                                newRow.AddColumn(reader[count]);
                            }
                            rows.Add(newRow);
                        }
                    }
                }
            }
        }

        #region IEnumerable<CSVRow> Members

        public IEnumerator<CSVRow> GetEnumerator()
        {
            BuildRows();
            return rows.GetEnumerator();
        }

        #endregion

        #region IEnumerable Members

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            BuildRows();
            return rows.GetEnumerator();
        }

        #endregion
    }
}

The LINQToCSVProvider is important class to use the LINQ query operation existing records within this object. This class is inherited from IEnumerable generic interface with CSVRow. The LINQToCSVProvider has two methods as well,

1.    Create()

This Method is creating object of the provider class with file name and what is delimiter to use in later part of the code to process query operation.

2.    BuildRows()

This method help to read data from source and add each row in list object to perform querying operation as like shown later part of this article.

And finally implementation of the IEnumerable interface methods. I assume you all know how to read CSV file with OleDbConnection.

How to use LINQToCSVProvider?

C# Code   

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LINQToCSV
{
    class Program
    {
        static void Main(string[] args)
        {
            LINQToCSVProvider provider = LINQToCSVProvider.Create(@".........\source.csv", ",");
            foreach (CSVRow row in (from source in provider select source))
            {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", row.GetString(0), row.GetString(1), row.GetString(2), row.GetString(3), row.GetString(4));
            }
        }
    }
}

Code Explanation

1.    In order query data using LINQ, first we have to create CSVProvider object with source file and delimiter. The first argument is source file path and second argument is what is delimited for the CSV source file to spilt values.

LINQToCSVProvider provider = LINQToCSVProvider.Create(@"....\source.csv", ",");

2.    In next foreach block, I’m query from CSVProvider object and then assign to CSVRow.

foreach (CSVRow row in (from source in provider select source))
    {
	// print records
    }

3.    Within looping print records from CSVRow object as formatted string

Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", row.GetString(0), row.GetString(1), row.GetString(2), row.GetString(3), row.GetString(4));

Now run application and see output as like below,

Output 

Apart from this default querying you can select records for following caes as well,

1.    Select records for exactly match word or character

var data =from source in provider where source.GetString(1) == "Wrox" select source;

2.    Select records for start with word or character

var data = from source in provider where source.GetString(1).StartsWith("Wr") select source

3.    Select records end with word or character

var data= from source in provider where source.GetString(1).EndsWith("ss") select source

4.    Select records for wherever match word or character
 

var data= from source in provider where source.GetString(1).Contains("s") select source

 Note: Just only change the querying way, other data processing still remaining as it.

Download Sample Project

Download source files -32 kb

Conclusion

In this article, we have learned how to read CSV file using LINQ query and manipulate data in easy way. Support different ways for querying data with LINQToCSVProvider. Hope help and thank you for reading.