<

Go Premium for a chance to win a PS4. Enter to Win

x

Parsing a CSV file into a datatable using LINQ and Regex

Published on
11,815 Points
5,115 Views
2 Endorsements
Last Modified:
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

2
Comment
Author:nepaluz
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
Questions

    1) Isn't it bad practice to rely on implicit conversion when turning text into DateTime objects? Along the same token, how does your code know if the value "01/11/2011" is "January 11, 2011" or "November 1, 2011"?
    2) Does your regex handle all permutations of comma placment? What happens if a quote is left off of a qualified field?
    3) Would it not be less overhead to do a Skip(1) call on the ReadAllLines call rather than line <> header for every line in the file?
0
 
LVL 17

Author Comment

by:nepaluz
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!
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:käµfm³d 👽
*sheesh*  I cannot seem to type the word "strict" today...

Option String On seems to disagree with you
Option Strict*
0
 
LVL 17

Author Comment

by:nepaluz
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?
0
 

Expert Comment

by:layer7
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?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Join & Write a Comment

Suggested Articles

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month