Wednesday, 18 November 2015

C# - Easiest CSV parser built in .NET Framework

Today it came to my surprise to know that there is a built in CSV parser in the .NET Framework library. Throughout the years, I have written many CSV parsers myself; but if I knew about this hidden parser, I might not have written any by myself.
Its hidden inside "Microsoft.VisualBasic" namespace.

Below is a sample usage of the class and methods to read a file "Person.csv".
In the sample, the line commenting character is "#"; the delimiting character is ",".
The class exposes property to configure delimiting character (comma in our sample) and comment token (# in our sample).
Any field which are enclosed within double quotes will escape delimiting characters and double quotes within the data field. It beautifully handles with by just setting the HasFieldsEnclosedInQuotes property to true.

using Microsoft.VisualBasic.FileIO;

var path = @"C:\Person.csv"; // Habeeb, "Dubai Media City, Dubai"
using (TextFieldParser csvParser = new TextFieldParser(path))
{
 csvParser.CommentTokens = new string[] { "#" };
 csvParser.SetDelimiters(new string[] { "," });
 csvParser.HasFieldsEnclosedInQuotes = true;

 // Skip the row with the column names
 csvParser.ReadLine();

 while (!csvParser.EndOfData)
 {
  // Read current line fields, pointer moves to the next line.
  string[] fields = csvParser.ReadFields();
  string Name = fields[0];
  string Address = fields[1];
 }
}

The parser has many more options. It can read a complete line as a single string. It can even read the remaining file content as a single string as well if needed in any case.

I will definitely rely on this parser rather than writing my own again.

16 comments:

  1. Excellent!!! It even handles line breaks within cells.
    Others may also need to set the Encoding. For me this works:
    using (TextFieldParser csvParser = new TextFieldParser(path,System.Text.Encoding.Default))

    ReplyDelete
  2. Thanks for this, helped me with a custom export format file I've been struggling to parse.

    ReplyDelete
  3. Thanks for your comments Sascha, Alan

    ReplyDelete
  4. Hi,
    im trying to read a CSV file which consists of 1 column and a lot of rows with data. I want to use this single data points for visualization in a simple line plot. I'm not able to read and plot my data with this CSV file reader. Can you help me store my data in a public variable?

    ReplyDelete
    Replies
    1. you can read the values in the while loop as in the sample in the article and then you can assign it to variables and use it as you like.

      Delete
  5. hello, i'm trying to read csv file where line endings must consist of \r\n. there are other \r and other \n in the CSV file and if they are along they have to be skipped as line endings. Is there a way to acomplish that with this?

    ReplyDelete
  6. How can i read the values in each row and move to loop to the next ?

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Guys plz help me out to save the below csv file using c# or VB into the sql database.

    CSV:

    Account Number: 9301000003
    Meter ID: EC5, Location: METERING_POINT_LOCATION_3 01/09/2002,1,0,3729.692,FRI,1,1.006012 01/09/2002,2,0,3570.756,FRI,1,1.006012

    ReplyDelete
  9. exponential value are not converting

    ReplyDelete