Reading CSV files in C#

Question

Does anyone know of an open-source library that allows you to parse and read .csv files in C#?

Answer 1

Here, written by yours truly to use generic collections and iterator blocks. It supports double-quote enclosed text fields (including ones that span mulitple lines) using the double-escaped convention (so "" inside a quoted field reads as single quote character). It does not support:

• Single-quote enclosed text
• \ -escaped quoted text
• alternate delimiters (won't yet work on pipe or tab delimited fields)
• Unquoted text fields that begin with a quote

But all of those would be easy enough to add if you need them. I haven't benchmarked it anywhere (I'd love to see some results), but performance should be very good - better than anything that's .Split() based anyway.

Now on GitHub

Update: felt like adding single-quote enclosed text support. It's a simple change, but I typed it right into the reply window so it's untested. Use the revision link at the bottom if you'd prefer the old (tested) code.

public static class CSV
{
    public static IEnumerable<IList<string>> FromFile(string fileName, bool ignoreFirstLine = false)
    {
        using (StreamReader rdr = new StreamReader(fileName))
        {
            foreach(IList<string> item in FromReader(rdr, ignoreFirstLine)) yield return item;
        }
    }

    public static IEnumerable<IList<string>> FromStream(Stream csv, bool ignoreFirstLine=false)
    {
        using (var rdr = new StreamReader(csv))
        {
            foreach (IList<string> item in FromReader(rdr, ignoreFirstLine)) yield return item;
        }
    }

    public static IEnumerable<IList<string>> FromReader(TextReader csv, bool ignoreFirstLine=false)
    {
        if (ignoreFirstLine) csv.ReadLine();

        IList<string> result = new List<string>();

        StringBuilder curValue = new StringBuilder();
        char c;
        c = (char)csv.Read();
        while (csv.Peek() != -1)
        {
            switch (c)
            {
                case ',': //empty field
                    result.Add("");
                    c = (char)csv.Read();
                    break;
                case '"': //qualified text
                case '\'':
                    char q = c;
                    c = (char)csv.Read();
                    bool inQuotes = true;
                    while (inQuotes && csv.Peek() != -1)
                    {
                        if (c == q)
                        {
                            c = (char)csv.Read();
                            if (c != q)
                                inQuotes = false;
                        }

                        if (inQuotes)
                        {
                            curValue.Append(c);
                            c = (char)csv.Read();
                        } 
                    }
                    result.Add(curValue.ToString());
                    curValue = new StringBuilder();
                    if (c == ',') c = (char)csv.Read(); // either ',', newline, or endofstream
                    break;
                case '\n': //end of the record
                case '\r':
                    //potential bug here depending on what your line breaks look like
                    if (result.Count > 0) // don't return empty records
                    {
                        yield return result;
                        result = new List<string>();
                    }
                    c = (char)csv.Read();
                    break;
                default: //normal unqualified text
                    while (c != ',' && c != '\r' && c != '\n' && csv.Peek() != -1)
                    {
                        curValue.Append(c);
                        c = (char)csv.Read();
                    }
                    result.Add(curValue.ToString());
                    curValue = new StringBuilder();
                    if (c == ',') c = (char)csv.Read(); //either ',', newline, or endofstream
                    break;
            }
            
        }
        if (curValue.Length > 0) //potential bug: I don't want to skip on a empty column in the last record if a caller really expects it to be there
            result.Add(curValue.ToString());
        if (result.Count > 0) 
            yield return result;

    }
}

Answer 2

Take a look at A Fast CSV Reader on CodeProject.

Answer 3

The last time this question was asked, here's the answer I gave:

If you're just trying to read a CSV file with C#, the easiest thing is to use the Microsoft.VisualBasic.FileIO.TextFieldParser class. It's actually built into the .NET Framework, instead of being a third-party extension.

Yes, it is in Microsoft.VisualBasic.dll, but that doesn't mean you can't use it from C# (or any other CLR language).

Here's an example of usage, taken from the MSDN documentation:

Using MyReader As New _
Microsoft.VisualBasic.FileIO.TextFieldParser("C:\testfile.txt")
   MyReader.TextFieldType = FileIO.FieldType.Delimited
   MyReader.SetDelimiters(",")
   Dim currentRow As String()
   While Not MyReader.EndOfData
      Try
         currentRow = MyReader.ReadFields()
         Dim currentField As String
         For Each currentField In currentRow
            MsgBox(currentField)
         Next
      Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
      MsgBox("Line " & ex.Message & _
      "is not valid and will be skipped.")
      End Try
   End While
End Using

Again, this example is in VB.NET, but it would be trivial to translate it to C#.

Answer 4

I really like the FileHelpers library. It's fast, it's C# 100%, it's available for FREE, it's very flexible and easy to use.

Answer 5

I'm implementing Daniel Pryden's answer in C#, so it is easier to cut and paste and customize. I think this is the easiest method for parsing CSV files. Just add a reference and you are basically done.

Add the Microsoft.VisualBasic Reference to your project

Then here is sample code in C# from Joel's answer:

using (Microsoft.VisualBasic.FileIO.TextFieldParser MyReader = new           
       Microsoft.VisualBasic.FileIO.TextFieldParser(filename))
{
    MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
    MyReader.SetDelimiters(",");

    while (!MyReader.EndOfData)
    {
        try
        {
            string[] fields = MyReader.ReadFields();
            if (first) 
            {
                first = false;
                continue;
            }

            // This is how I treat my data, you'll need to throw this out.

            //"Type"    "Post Date" "Description"   "Amount"
            LineItem li = new LineItem();

            li.date        = DateTime.Parse(fields[1]);
            li.description = fields[2];
            li.Value       = Convert.ToDecimal(fields[3]);

            lineitems1.Add(li);
        }
        catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex)
        {
            MessageBox.Show("Line " + ex.Message + 
                            " is not valid and will be skipped.");
        }
    }
}

Answer 6

Besides parsing/reading, some libraries do other nice things like convert the parsed data into object for you.

Here is an example of using CsvHelper (a library I maintain) to read a CSV file into objects.

var csv = new CsvHelper( File.OpenRead( "file.csv" ) );
var myCustomObjectList = csv.Reader.GetRecords<MyCustomObject>();

By default, conventions are used for matching the headers/columns with the properties. You can change the behavior by changing the settings.

// Using attributes:
public class MyCustomObject
{
    [CsvField( Name = "First Name" )]
    public string StringProperty { get; set; }

    [CsvField( Index = 0 )]
    public int IntProperty { get; set; }

    [CsvField( Ignore = true )]
    public string ShouldIgnore { get; set; }
}

Sometimes you don't "own" the object you want to populate the data with. In this case you can use fluent class mapping.

// Fluent class mapping:
public sealed class MyCustomObjectMap : CsvClassMap<MyCustomObject>
{
    public MyCustomObjectMap()
    {
        Map( m => m.StringProperty ).Name( "First Name" );
        Map( m => m.IntProperty ).Index( 0 );
        Map( m => m.ShouldIgnore ).Ignore();
    }
}

Answer 7

You can use Microsoft.VisualBasic.FileIO.TextFieldParser

get below code example from above article

static void Main()
        {
            string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";

            DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);

            Console.WriteLine("Rows count:" + csvData.Rows.Count);

            Console.ReadLine();
        }


private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();

            try
            {

            using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }

                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return csvData;
        }