Reading a CSV file in .NET?

Question

How do I read a CSV file using C#?

Buna

https://mcmap.net/q/11192/-import-csv-file-to-strongly-typed-data-structure-in-net-closed https://mcmap.net/q/11401/-import-csv-file-into-c https://mcmap.net/q/11402/-csv-parsing-with-double-quotes #1048031 #1103995 – Demivolt

Answer 1

A choice, without using third-party components, is to use the class Microsoft.VisualBasic.FileIO.TextFieldParser (http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx) . It provides all the functions for parsing CSV. It is sufficient to import the Microsoft.VisualBasic assembly.

var parser = new Microsoft.VisualBasic.FileIO.TextFieldParser(file);
parser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
parser.SetDelimiters(new string[] { ";" });

while (!parser.EndOfData)
{
    string[] row = parser.ReadFields();
    /* do something */
}

Morman

For reading CSV files and providing the parsing yourself, this is ideal. Simple, but full-featured, including quoted values across multiple lines. – Callant

This is my favourite out of the box solution – Tropopause

Answer 2

You can use the Microsoft.VisualBasic.FileIO.TextFieldParser class in C#:

using System;
using System.Data;
using Microsoft.VisualBasic.FileIO;

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

    DataTable csvData = GetDataTableFromCSVFile(csv_file_path);

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

private static DataTable GetDataTableFromCSVFile(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 datacolumn = new DataColumn(column);
                datacolumn.AllowDBNull = true;
                csvData.Columns.Add(datacolumn);
            }

            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;
}

Winni

This library is my favourite out of the box solution. – Tropopause

The only thing I changed from this example was a check for the same column name and added some code to show the exception – Juju

Answer 3

You could try CsvHelper, which is a project I work on. Its goal is to make reading and writing CSV files as easy as possible, while being very fast.

Here are a few ways you can read from a CSV file.

// By type
var records = csv.GetRecords<MyClass>();
var records = csv.GetRecords( typeof( MyClass ) );

// Dynamic
var records = csv.GetRecords<dynamic>();

// Using anonymous type for the class definition
var anonymousTypeDefinition =
{
    Id = default( int ),
    Name = string.Empty,
    MyClass = new MyClass()
};
var records = csv.GetRecords( anonymousTypeDefinition );

Crate

Answer 4

I usually use a simplistic approach like this one:

var path = Server.MapPath("~/App_Data/Data.csv");
var csvRows = System.IO.File.ReadAllLines(path, Encoding.Default).ToList();

foreach (var row in csvRows.Skip(1))
{
    var columns = row.Split(';');

    var field1 = columns[0];
    var field2 = columns[1];
    var field3 = columns[2];
}

Almedaalmeeta

Easy is always the best. – Berri

That's loading the whole file into memory, which could be a problem. Also, what if a field contains a ; or newline? What about the fields that are escaped based on the RFC 4180 spec? Etc. – Crate

Thank you for pointing out the flaws of the implementation. Those are indeed pitfalls of this way of doing it. But as you can see, the implementation is fast and easy - so it might fit well for many use cases. It has for me – Almedaalmeeta

Answer 5

I just used this library in my application. http://www.codeproject.com/KB/database/CsvReader.aspx. Everything went smoothly using this library, so I'm recommending it. It is free under the MIT License, so just include the notice with your source files.

I didn't display the CSV in a browser, but the author has some samples for Repeaters or DataGrids. I did run one of his test projects to test a Sort operation I have added and it looked pretty good.

Garish

Answer 6

You can try Cinchoo ETL - an open source lib for reading and writing CSV files.

Couple of ways you can read CSV files

Id, Name
1, Tom
2, Mark

This is how you can use this library to read it

using (var reader = new ChoCSVReader("emp.csv").WithFirstLineHeader())
{
   foreach (dynamic item in reader)
   {
      Console.WriteLine(item.Id);
      Console.WriteLine(item.Name);
   }
}

If you have POCO object defined to match up with CSV file like below

public class Employee
{
   public int Id { get; set; }
   public string Name { get; set; }
}

You can parse the same file using this POCO class as below

using (var reader = new ChoCSVReader<Employee>("emp.csv").WithFirstLineHeader())
{
   foreach (var item in reader)
   {
      Console.WriteLine(item.Id);
      Console.WriteLine(item.Name);
   }
}

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

Disclaimer: I'm the author of this library

Snifter

I prefer this library. Thanks – Mccurdy

This seems like the easiest library, very intuitive. – Millett

Answer 7

I recommend Angara.Table, about save/load: http://predictionmachines.github.io/Angara.Table/saveload.html.

It makes column types inference, can save CSV files and is much faster than TextFieldParser. It follows RFC4180 for CSV format and supports multiline strings, NaNs, and escaped strings containing the delimiter character.

The library is under MIT license. Source code is https://github.com/Microsoft/Angara.Table.

Though its API is focused on F#, it can be used in any .NET language but not so succinct as in F#.

Example:

using Angara.Data;
using System.Collections.Immutable;

...

var table = Table.Load("data.csv");

// Print schema:
foreach(Column c in table)
{
    string colType;
    if (c.Rows.IsRealColumn) colType = "double";
    else if (c.Rows.IsStringColumn) colType = "string";
    else if (c.Rows.IsDateColumn) colType = "date";
    else if (c.Rows.IsIntColumn) colType = "int";
    else colType = "bool";

    Console.WriteLine("{0} of type {1}", c.Name, colType);
}

// Get column data:
ImmutableArray<double> a = table["a"].Rows.AsReal;
ImmutableArray<string> b = table["b"].Rows.AsString;

Table.Save(table, "data2.csv");

Eidetic

Answer 8

I have been maintaining an open source project called FlatFiles for several years now. It's available for .NET Core and .NET 4.5.1.

Unlike most of the alternatives, it allows you to define a schema (similar to the way EF code-first works) with an extreme level of precision, so you aren't fight conversion issues all the time. You can map directly to your data classes, and there is also support for interfacing with older ADO.NET classes.

Performance-wise, it's been tuned to be one of the fastest parsers for .NET, with a plethora of options for quirky format differences. There's also support for fixed-length files, if you need it.

Pushy

Recommended reading: How to offer personal open-source libraries? – Nixie

Answer 9

You might be interested in Linq2Csv library at CodeProject. One thing you would need to check is that if it's reading the data when it needs only, so you won't need a lot of memory when working with bigger files.

As for displaying the data on the browser, you could do many things to accomplish it, if you would be more specific on what are your requirements, answer could be more specific, but things you could do:
1. Use HttpListener class to write simple web server (you can find many samples on net to host mini-http server).
2. Use Asp.Net or Asp.Net Mvc, create a page, host it using IIS.

Religieux

Answer 10

This is just for parsing the CSV. For displaying it in a web page, it is simply a matter of taking the list and rendering it however you want.

Note: This code example does not handle the situation where the input string line contains newlines.

public List<string> SplitCSV(string line)
{
    if (string.IsNullOrEmpty(line))
        throw new ArgumentException();

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

    int index = 0;
    int start = 0;
    bool inQuote = false;
    StringBuilder val = new StringBuilder();

    // parse line
    foreach (char c in line)
    {
        switch (c)
        {
            case '"':
                inQuote = !inQuote;
                break;

            case ',':
                if (!inQuote)
                {
                    result.Add(line.Substring(start, index - start)
                        .Replace("\"",""));

                    start = index + 1;
                }

                break;
            }

            index++;
        }

        if (start < index)
        {
            result.Add(line.Substring(start, index - start).Replace("\"",""));
        }

        return result;
    }
}

Bartonbartosch

It also doesn't handle the use of double-quote characters within a value. CSV has many edge cases, it is best to just use a library. – Rothko

Answer 11

Seems like there are quite a few projects on CodeProject or CodePlex for CSV Parsing. Here is another CSV Parser on CodePlex

http://commonlibrarynet.codeplex.com/

This library has components for CSV parsing, INI file parsing, Command-Line parsing as well. It's working well for me so far. Only thing is it doesn't have a CSV Writer.

Ofay

Just an FYI: The license on the site says that it's MIT, but in the source code itself, all the Csv related files have a prominent LGPL license at the top. – Crumby

Answer 12

you can use this library: Sky.Data.Csv https://www.nuget.org/packages/Sky.Data.Csv/ this is a really fast CSV reader library and it's really easy to use:

using Sky.Data.Csv;

var readerSettings = new CsvReaderSettings{Encoding = Encoding.UTF8};
using(var reader = CsvReader.Create("path-to-file", readerSettings)){
    foreach(var row in reader){
        //do something with the data
    }
}

it also supports reading typed objects with CsvReader<T> class which has a same interface.

Recurrence