Parsing a CSV file into a datatable using LINQ and Regex

Published:
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex.

A simple solution to parsing a customized CSV file is to use this function which returns the datatable. You will have to first setup the datatable, and in this simplified scenario, all the fields of the file are imported into the table. (here is a snapshot of the file).
Date,Description,FITID,Amount
30/10/2011,First transaction,001,9.99
01/11/2011,"Second transaction, Withdraw",002,-3.26
03/11/2011,Third transaction,003,1.08
And since we intend to load this data into a datatable, here is the setup of the table in code. (You can achieve the same using the designer).
Dim gTable As New DataTable("MyTable")
                      With gTable
                          .Columns.Add("Date").DataType = GetType(System.DateTime)
                          .PrimaryKey = New DataColumn() {.Columns("DelayID")}
                          .Columns.Add("Description").DataType = GetType(System.String)
                          .Columns.Add("FITID").DataType = GetType(System.String)
                          .Columns.Add("Amount").DataType = GetType(System.Double)
                          .Columns("FITID").AllowDBNull = False
                          .Columns("Amount").DefaultValue = 0
                      End With

Open in new window

If you are wondering why the "overkill" in using regex, then the answer lies on the second line of the file which contains a qualified field (a field enclosed in quotes) that contains a comma (which in turn is the file delimiter). The regex ensures that this line is parsed properly, and the code for this is:
Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
                      Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)

Open in new window

Since the file has a header row, we need to define this and exclude it from the data using LINQ, but firstly, we should declare it as a string constant to later incorporate into the LINQ query.
Dim header As String = "Date,Description,FITID,Amount"

Open in new window

Though the title says using LINQ and Regex to parse a file, we still have to depend on the System.IO namespace to read the contents of the file and also to split the records in the file in order for LINQ to be used. Additionally, it is not un-common for CSV files to contain a blank / empty row, and we need to bear that in mind when constructing the LINQ query.
Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
                      Dim custs = From line In lines Where line <> header AndAlso Not String.IsNullOrEmpty(line) Let data = r.Split(line)
                              Select New With {.Date = data(0), .Description = data(1), .FITID = data(2), .Amount = CDbl(data(3).Trim)}

Open in new window

NOTE: We use LINQ to "declare" new columns with names corresponding to the file header (these are the same names we applied to the columns in the datatable above, though you can choose different names).

Finally, we can iterate through the results of the LINQ query to populate the datatable, first declaring a datarow.
Dim xRow As DataRow
                      For Each row In custs
                          xRow = gTable.NewRow()
                          xRow.ItemArray = {row.Date, row.Description, row.FITID, row.Amount}
                          gTable.Rows.Add(xRow)
                      Next

Open in new window

Note that you can reference the items columns in from the LINQ query by their names.
Finally, here is the complete function (including datatable setup) that you can call to return a populated datatable.
Function readLINQ(ByVal strCustomerFile As String) As DataTable
                          Dim gTable As New DataTable("MyTable")
                          With gTable
                              .Columns.Add("Date").DataType = GetType(System.DateTime)
                              .PrimaryKey = New DataColumn() {.Columns("DelayID")}
                              .Columns.Add("Description").DataType = GetType(System.String)
                              .Columns.Add("FITID").DataType = GetType(System.String)
                              .Columns.Add("Amount").DataType = GetType(System.Double)
                              .Columns("FITID").AllowDBNull = False
                              .Columns("Amount").DefaultValue = 0
                          End With
                          Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
                          Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
                          Dim header As String = "Date,Description,FITID,Amount"
                          Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)
                          Dim custs = From line In lines Where line <> header AndAlso Not String.IsNullOrEmpty(line) Let data = r.Split(line)
                                  Select New With {.Date = data(0), .Description = data(1), .FITID = data(2), .Amount = CDbl(data(3).Trim)}
                          Dim xRow As DataRow
                          For Each row In custs
                              xRow = gTable.NewRow()
                              xRow.ItemArray = {row.Date, row.Description, row.FITID, row.Amount}
                              gTable.Rows.Add(xRow)
                          Next
                          Return gTable
                      End Function

Open in new window

3
7,489 Views

Comments (6)

Author

Commented:
thanks for the comment kaufmed. I shall address your questions below.
1) Isn't it bad practice to rely on implicit conversion when turning text into DateTime objects?
Bad practice? I do not agree with you on that.
how does your code know if the value "01/11/2011" is "January 11, 2011" or "November 1, 2011"?
The code will parse the string to date based on the culture of the thread it is running on. Thus the answer to your question is, it will depend on the thread culture.

To add to the above specific responses, I hope you appreciate that the article addresses parsing a CSV file using regex and LINQ to a datatable, and NOT handling of dates. Indeed, I could have used a file example that did not contain date fields.
2) Does your regex handle all permutations of comma placment? What happens if a quote is left off of a qualified field?
The regex WILL handle all permutations of a comma delimited file with qualified fields without any issue. For your contraption of a record missing a closing quote on a "qualified" field.... well, that is not a qualified field, is it?
3) Would it not be less overhead to do a Skip(1)  ...
Less overhead? Absolutely not. More concise and less descriptive, possibly. But then again, the article is meant to be instructive and detailed yet concise. Thus, an overhead in word count, MOST DEFINITELY!
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
nepaluz,

Bad practice? I do not agree with you on that.
Option String On seems to disagree with you  : \

The intention of my comment is to address possible error scenarios one could encounter when parsing CSV files. It is in no way safe to assume that a computer was always the entity creating the CSV file--your code seems to make this assumption. Would it not be possible for an end user to upload a "hand-built" CSV file to a website? If the user forgot to properly qualify the field (i.e. the aforementioned missing quote), will your code handle that? You seemed to imply that this code was directed at "newbies". You and I may know that the date is parsed based on the current thread's culture: would a newbie?

It is not my intention to offend. I am simply trying to ask questions form the point of view of a newbie.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
*sheesh*  I cannot seem to type the word "strict" today...

Option String On seems to disagree with you
Option Strict*

Author

Commented:
At the risk of sounding like I am banging on about the obvious, may I add that aside from the fictitious situation whereby a newbie user forgot to include a closing quotation character on a field that is supposed to be a qualified field because he had no access to a computer, the article does address and provide a solution for parsing of a CSV file using Regex and LINQ. I am sure you agree with that.

And last time I checked, there was NO option strict errors with the code, or am I missing something here?

Commented:
Nepaluz, thank you for taking the time and effort to post this. I found it to be very informative and a good alternative to the method I've been using.

I do have a follow up question. The CSV files I'm dealing with are huge, in the order of 10's of GigaBytes. Rather than readalllines, can you adapt this function to read one line at a time and return the an array as a data table would be over-kill in this situation.

One last thing. Any pointers to a good LINQ tutorial?

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.