read csv file c# example - Parsing CSV files in C#, with header



8 Answers

A CSV parser is now a part of .NET Framework.

Add a reference to Microsoft.VisualBasic.dll (works fine in C#, don't mind the name)

using (TextFieldParser parser = new TextFieldParser(@"c:\temp\test.csv"))
{
    parser.TextFieldType = FieldType.Delimited;
    parser.SetDelimiters(",");
    while (!parser.EndOfData)
    {
        //Process row
        string[] fields = parser.ReadFields();
        foreach (string field in fields)
        {
            //TODO: Process field
        }
    }
}

The docs are here - TextFieldParser Class

Question

how to read column values from csv file in c#

Is there a default/official/recommended way to parse CSV files in C#? I don't want to roll my own parser.

Also, I've seen instances of people using ODBC/OLE DB to read CSV via the Text driver, and a lot of people discourage this due to its "drawbacks." What are these drawbacks?

Ideally, I'm looking for a way through which I can read the CSV by column name, using the first record as the header / field names. Some of the answers given are correct but work to basically deserialize the file into classes.




In a business application, i use the Open Source project on codeproject.com, CSVReader.

It works well, and has good performance. There is some benchmarking on the link i provided.

A simple example, copied from the project page:

using (CsvReader csv = new CsvReader(new StreamReader("data.csv"), true))
{
    int fieldCount = csv.FieldCount;
    string[] headers = csv.GetFieldHeaders();

    while (csv.ReadNextRecord())
    {
        for (int i = 0; i < fieldCount; i++)
            Console.Write(string.Format("{0} = {1};", headers[i], csv[i]));

        Console.WriteLine();
    }
}

As you can see, it's very easy to work with.




If you need only reading csv files then I recommend this library: A Fast CSV Reader
If you also need to generate csv files then use this one: FileHelpers

Both of them are free and opensource.




This solution is using the official Microsoft.VisualBasic assembly to parse CSV.

Advantages:

  • delimiter escaping
  • ignores Header
  • trim spaces
  • ignore comments

Code:

    using Microsoft.VisualBasic.FileIO;

    public static List<List<string>> ParseCSV (string csv)
    {
        List<List<string>> result = new List<List<string>>();


        // To use the TextFieldParser a reference to the Microsoft.VisualBasic assembly has to be added to the project. 
        using (TextFieldParser parser = new TextFieldParser(new StringReader(csv))) 
        {
            parser.CommentTokens = new string[] { "#" };
            parser.SetDelimiters(new string[] { ";" });
            parser.HasFieldsEnclosedInQuotes = true;

            // Skip over header line.
            //parser.ReadLine();

            while (!parser.EndOfData)
            {
                var values = new List<string>();

                var readFields = parser.ReadFields();
                if (readFields != null)
                    values.AddRange(readFields);
                result.Add(values);
            }
        }

        return result;
    }






Some time ago I had wrote simple class for CSV read/write based on Microsoft.VisualBasic library. Using this simple class you will be able to work with CSV like with 2 dimensions array. You can find my class by the following link: https://github.com/ukushu/DataExporter

Simple example of usage:

Csv csv = new Csv("\t");//delimiter symbol

csv.FileOpen("c:\\file1.csv");

var row1Cell6Value = csv.Rows[0][5];

csv.AddRow("asdf","asdffffff","5")

csv.FileSave("c:\\file2.csv");

For reading header only you need is to read csv.Rows[0] cells :)




Based on unlimit's post on How to properly split a CSV using C# split() function? :

string[] tokens = System.Text.RegularExpressions.Regex.Split(paramString, ",");

NOTE: this doesn't handle escaped / nested commas, etc., and therefore is only suitable for certain simple CSV lists.




Another one to this list, Cinchoo ETL - an open source library to read and write multiple file formats (CSV, flat file, Xml, JSON etc)

Sample below shows how to read CSV file quickly (No POCO object required)

static void ReadCSV()
{
    using (var stream = new MemoryStream())
    using (var reader = new StreamReader(stream))
    using (var writer = new StreamWriter(stream))
    using (var parser = new ChoCSVReader(reader))
    {
        writer.WriteLine("id,name");
        writer.WriteLine("1,Carl");
        writer.WriteLine("2,Mark");
        writer.WriteLine("3,Tom");

        writer.Flush();
        stream.Position = 0;

        foreach (dynamic dr in parser)
        {
            Console.WriteLine("Id: {0}, Name: {1}", dr.id, dr.name);
        }
    }
}

Sample below shows how to read CSV file using POCO object

public partial class EmployeeRec
{
    public int Id { get; set; }
    public string Name { get; set; } 
}
static void ReadCSV()
{
    using (var stream = new MemoryStream())
    using (var reader = new StreamReader(stream))
    using (var writer = new StreamWriter(stream))
    using (var parser = new ChoCSVReader<EmployeeRec>(reader))
    {
        writer.WriteLine("id,name");
        writer.WriteLine("1,Carl");
        writer.WriteLine("2,Mark");
        writer.WriteLine("3,Tom");

        writer.Flush();
        stream.Position = 0;

        foreach (var dr in parser)
        {
            Console.WriteLine("Id: {0}, Name: {1}", dr.id, dr.name);
        }
    }
}

Please check out articles at CodeProject on how to use it.




Related