LINQ to CSV

• Download source code - 189.7 KB 

Introduction 

Hi .. this is the continuation for crazy stuff with LINQ that we have started from years ago since I created LINQ to XML. I come up today with LINQ to CSV as a continuation to the dream LINQ to Everything.

Background

The CSV stands for Comma Separated Values, which is a text-based file that contains a bunch of values separated by commas, it has either .csv or a .txt extension.

The CSV file format is often used to exchange data between disparate applications. The file format, as it is used in Microsoft Excel, has become a pseudo standard throughout the industry, even among non-Microsoft platforms.

The CSV Format

• Each record is one line, but fields may contain embedded line-breaks, so a record may span more than one line
• The first record in a CSV file may be a header record containing column (field) names.
• Fields are separated with commas.
  

  1997,Ford,E350   

• Leading and trailing space-characters adjacent to comma field separators are ignored.

  "luxurious truck" 

•  Fields with embedded commas must be delimited with double-quote characters.
  

  1997,Ford,E350,"Super, luxurious truck"  

•  Fields that contain double quote characters must be surrounded by double-quotes, and the embedded double-quotes must each be represented by a pair of consecutive double quotes.
  

  1997,Ford,E350,"Super, ""luxurious"" truck"  

• A field that contains embedded line-breaks must be surrounded by double-quotes.

  1997,Ford,E350,"Go get one now
  they are going fast" 

This is an example of how the CSV looks like:

Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended 

Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very 

Large""",,5000.00
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00  

I quote the above sample from Wikipedia, so for more details please check this link.

I saw many implementations to read and write into csv files, even LINQ to CSV, nothing but I didn't see much of flexibility during reading the values or querying them also many of the articles which i have seen parse the csv files line by line which is good to achieve their goals but almost fails when we have multi-line values. So i struggle to do it myself in LINQy style. Out of doubt we know that the CSV fields depend on the the file itself and there's no CLR object that contains such properties. Instead some guys have a great job in LINQ to CSV and they create a CLR objects that contain the same properties, it's kind of mapping .. believe me this way it 'll bother the programmers after awhile, coz whenever the fields have changed in the files he/she needs to create another POCO to map these fields.

So i was thinking for a couple of days to do it  in a better way .. it 'll be nice if we write record.Name instead of magic string record["Name"].

And as we know in .NET 4 the Dynamic and DLR becomes increasingly important for almost programmers to do dynamic programming stuff. I think what we have is a kind of dynamic programming, as Anders Hejlsberg said "Use the strongly types when you can, but when you can't the dynamic comes for the rescue" i still remember those words from DevDays 2010 Conference. And that's the big challenge for me personally to do such thing.

In my tiny library i 'll focus mainly into Dynamic Programming & Regular Expressions to accomplish my goal, there 're many materials you can find them on the internet talking about each one of them.

Using the code

The Record class is very important class to represent the CSV record dynamically, in other words it contains all the fields and values for specific record in CSV file, and these fields and vaules become a member for this object at the runtime, which is leverage from the complexity of reflection and related stuff. This class seems as ExpandoObject implementation, which contains a data dictionary to store the keys and values for each record in the CSV file.  

Public Class Record Inherits DynamicObject

    Private dictionary As New Dictionary(Of String, Object)
    Public Sub AddPair(key As String, value As Object)
        dictionary(key) = value
    End Sub
    Public Overrides Function TryGetMember(binder As GetMemberBinder, _
                     ByRef result As Object) As Boolean
        result = dictionary(binder.Name)
        Return True
    End Function

    Public Overrides Function TrySetMember(binder As SetMemberBinder, _
           value As Object) As Boolean
        dictionary(binder.Name) = value
        Return True
    End Function
End Class

If we dig into the Record class we'll notice  that implement DynamicObject class which is enable us to create a dynamic object that can discover its properties and methods at run-time. And simply we override the TryGetMember and TrySetMember methods to implement the getter and setter for the Record class members.

One of the coolest function is AddPair() which allow us to add properties to this class at runtime?!! may be the sound is strange, this and much more is kind o goodness from DLR and dynamic support in the framework itself. Which is something tough in the "Reflection Days".

The CSVDataContext is the container of all the records that 'll be fetch from the CSV file. It's straightforward accept the file path to be parsed. Fetch the fields to the Key property as well as the values into the Records property.

In the code below we notice that we have two properties Keys which store the fields names, and the second one is Records which hold the actual data of each record.

The main logic happen in the constructor which accept the file name for the CSV file, second thing get the fields from the file and iterate through each line for that file and apply the following regular expression

"[" & vbTab & ",](?=(?:[^""]|""[^""]*"")*$)" 

which is return the tokens that matches the above pattern, which means give me all the tokens that separated by commas, and if they 're inside the double quotation please treat them as data. Last thing store them in the Records property which was already created.

Public Class CSVDataContext
    Private Property keys As List(Of String)
    Public Property Records As List(Of Object)

    Public Sub New(filePath As String)
        If File.Exists(filePath) Then
            Try
                Dim lines = File.ReadAllLines(filePath)
                keys = New List(Of String)(lines(0).Replace(" ", "").Split(","))
                Records = New List(Of Object)()
                Dim tokens As IEnumerable(Of String)
                Dim flag As Boolean
                For i As Integer = 1 To lines.Length - 1
                    Dim record As New Record()
                    flag = lines(i).Where(Function(c) c = """").Count() Mod 2 = 1
                    If flag Then lines(i) = String.Concat(lines(i), lines(i + 1))
                    tokens = Regex.Split(lines(i), "[" & vbTab & ",](?=(?:[^""]|""[^""]*"")*$)")
                    For j = 0 To keys.Count - 1
                        record.AddPair(keys(j), tokens)
                    Next
                    If flag Then i += 1
                    Records.Add(record)
                Next
            Catch ex As Exception
                Throw New FormatException()
            End Try
        Else
            Throw New FileNotFoundException()
        End If
    End Sub
End Class

The remaining stuff is instantiate the CSVDataContext and play with the Records property in the LINQy fashion :)

Dim db As New CSVDataContext("test.csv")
Dim query = From r In db.Records
            Select r

For Each q In query
    Console.WriteLine("{0} {1} {2} {3}", q.Year, q.Make.ToString().PadRight(10, _
      " "), q.Model.ToString().PadRight(45, " "), q.Price)
Next

In the above snippet we create an object from our DataContext, after that we create a simple query that fetches all the record from test.csv file. Last thing we iterate the query result and print them in the console. And if we take a deep look to that query we 'll notice that's seem to be a normal LINQ to SQL, there's no magic strings or something like that and that's the beauty of dynamic objects that allow us to create custom properties and discover them at run-time.

Points of Interest 

Dynamic programming makes the developers life much easier than before, and with LINQ the life becomes too sweet :) and the combination for different technologies and features explore the power of the .NET.