Once in a while I’m still surprised by the functionality of LINQ. I saw a question on the Internet from a developer who asked if it is possible to query against Comma Separated Values (CSV) in a file. Based on one of the answers I wrote this post, because it’s really great what you can do with so less source code. As you probably already knew, I’m a big fan of LINQPad, which I will use to demo my examples. One of the nice features is the extension method .Dump(). As you can imagine, this will dump (or write) the data to the Results window of LINQPad. It works only in LINQPad, so, if you want to try this source in Visual Studio, you have to remove the .Dump() statements and write your own code to display the results.

Dim path = GetAndCreateFilePath()
 
Dim results = (From line In File.ReadAllLines(path).Dump("Lines read")
               Let value = line.Split(",").
                                Skip(1). ' Skip the RowNumber
                                Select(Function(x) Integer.Parse(x))
               Select New With {.MinimumValue = value.Min(),
                                .MaximumValue = value.Max(),
                                .Average = value.Average(),
                                .RowTotal = value.Sum()}).ToList().
                                                          Dump("Statistics")
  
File.Delete(path)

As you can see in the VB.NET code above, first we create a file, then we read and query this file and in the last statement we delete this temporary file. Before diving more deep in this code, you have to see what we are writing to disk in the GetAndCreateFilePath() procedure. The lines starts with a row number and four numeric values.

Private Function GetAndCreateFilePath() As String
 
    Dim filePath = Path.GetTempFileName()
 
    Dim stringBuilder As New StringBuilder
    With stringBuilder
        .AppendLine("1, 309, 88, 125, 825")
        .AppendLine("2, 458, 258, 198, 189")
        .AppendLine("3, 87, 587, 675, 136")     
    End With
 
    File.WriteAllText(filePath, stringBuilder.ToString())
 
    Return filePath
 
End Function

In the first code snippet we querying a LINQ statement against a List(Of String), the line variable. With help of the Split() function, we divide this line into an array of String, with in each element a value from the line. Because we are not interested in the first value, the Row Number, we skip this value with Skip(1). As a side note it’s good to mention that starting from Visual Basic.NET 2015 you can use inline comments. In this example it really helps to explain why we are skipping the first element of the array. If we have selected the correct range of data, we can easily project this data, and calculations on it to an anonymous type. And as earlier said, the Dump() method writes the data to the Results window of LINQPad. You can use this method more than once in one LINQ statement. First we are writing the actual lines read and at the end the statistics of the data. The power of LINQ is really amazing!

querycsvwithlinqandvisualbasicnet