Click here to Skip to main content
15,850,103 members
Articles / Programming Languages / C#
Tip/Trick

How to Return the Contents of a CSV file as a Generic List

Rate me:
Please Sign up or sign in to vote.
4.71/5 (5 votes)
12 Aug 2014CPOL2 min read 24.9K   12   6
Parsing a CSV file to populate a generic list in C#

More CSV Files Than You Can Shake a Stick At

It has been estimated that there are more CSV files floating around the Internet and stashed within the innards of hard drives than there are longhorns in Laredo or Sylvester Stallone movie sequels. That being the case, you may want to make use of those hordes of data by making the content of those files available in the form of a collection of easily traversable data. So why not parse them and store their contents in a generic list?

It's easy - say you have a class named Platypus defined like so, mirroring the contents of the CSV file:

C#
public class Platypus
{
    // These are the column names in PlatypusN.csv:
    // id, duckbill_name, billLength, poisonToeLength, description
    public int Id { get; set; }
    public String duckbillName { get; set; }
    public double billLength { get; set; }
    public double poisonToeLength { get; set; }
    public String description { get; set; }
}

Just so you can better envision what's happening, the contents of the CSV file may be something like this:

  • id,duckbill_name,billLength,poisonToeLength,description
  • 1,Daffy Duckbill,3.14,0.17,Daffy has a great personality - or should I say platypusality
  • 2,Plato,8.67,5.309,Plato has a platonic relationship with Daffy and has eyes like plates

You can now write a method to open the CSV file, parse it, populate the appropriate generic list, and then return it to the caller, like so:

C#
public List<Platypus> GetPlatypus(string pondNum)
{
    List<Platypus> platypus = new List<Platypus>();
    const String PlatypusBase = @"C:\Duckbills\PlatypusPalace{0}.csv";

    String fileToLoad = String.Format(PlatypusBase, pondNum);
    using (StreamReader r = new StreamReader(fileToLoad))
    {
        string line;
        while ((line = r.ReadLine()) != null)
        {
            string[] parts = line.Split(',');
            // Skip the column names row
            if (parts[0] == "id") continue;
            Platypus dbp = new Platypus();
            dbp.Id = Convert.ToInt32(parts[0]);
            dbp.duckbillName = parts[1];
            dbp.billLength = Convert.ToInt32(parts[2]);
            dbp.poisonToeLength = Convert.ToInt32(parts[3]);
            dbp.description = parts[4];
            platypus.Add(dbp);
        }
    }
    return platypus;
}

Calling it is easy - assuming the CSV files are named in a pattern like "PlatypusPalace1", "PlatypusPalace2", "PlatypusPalace42", etc., this is all you need:

C#
List<Platypus> platypi = new List<Platypus>();
platypi = GetPlatypus("1879");
// Now do whatever you want with the collection of Platypi returned

And of course, spreadsheet files can be saved as CSV, so anything you have in an Excel (or Open Office Calc, etc.) file can be populated into class collections, and from there you can populate database tables, perhaps after scrubbing and normalizing the data.

A "big ball of information overflow mud" (45 gazillion columns in a spreadsheet that has had data added to it since the dawn of digital offices) can be rejuvenated by massaging and renovating the data via analysis, parsing, and normalization (dispatching the raw data into appropriate lookup tables, one-to-many tables, and many-to-many tables). Long live SQL!

Call to Action

If you find this tip useful, consider making a donation to POTWU (Platypi of the World Unite) or DUH (Duckbills Unlimited, Huh?). Alternatively, you could sew a set of mittens (we'd need four) for my pet Platypus.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder Across Time & Space
United States United States
I am in the process of morphing from a software developer into a portrayer of Mark Twain. My monologue (or one-man play, entitled "The Adventures of Mark Twain: As Told By Himself" and set in 1896) features Twain giving an overview of his life up till then. The performance includes the relating of interesting experiences and humorous anecdotes from Twain's boyhood and youth, his time as a riverboat pilot, his wild and woolly adventures in the Territory of Nevada and California, and experiences as a writer and world traveler, including recollections of meetings with many of the famous and powerful of the 19th century - royalty, business magnates, fellow authors, as well as intimate glimpses into his home life (his parents, siblings, wife, and children).

Peripatetic and picaresque, I have lived in eight states; specifically, besides my native California (where I was born and where I now again reside) in chronological order: New York, Montana, Alaska, Oklahoma, Wisconsin, Idaho, and Missouri.

I am also a writer of both fiction (for which I use a nom de plume, "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006: http://www.lulu.com/spotlight/blackbirdcraven

Comments and Discussions

 
SuggestionCSV files aren't that simple, though. Pin
Mike Cattle13-Aug-14 7:42
Mike Cattle13-Aug-14 7:42 
I would have to agree with George Swan, the CSV format isn't that simple to read, due to:

1. String fields can be wrapped in quotes (which are not considered a part of the string data).
2. Quoted string fields can contain the delimiter as a part of the string.
3. Quoted string fields can also contain the quote character (escaped).
4. CSV files may or may not have a header row at the top.
5. CSV files may use alternate delimiters (comma, tab, others), or they may use fixed-width columns, although those aren't technically CSV anymore.

This is all defined in RFC4180.

There are a number of different approaches to reading CSV files, though:

1. Use good ol' fashioned OLEDB to read the .csv as if it were a database source into a Recordset.
2. Use the TextFieldParser class found in the .NET Framework.
3. Use an already written (and proven) third party library such as the one by Andrew Rissing.
GeneralRe: CSV files aren't that simple, though. Pin
B. Clay Shannon13-Aug-14 7:54
professionalB. Clay Shannon13-Aug-14 7:54 
GeneralRe: CSV files aren't that simple, though. Pin
Corvin Rok13-Aug-14 21:07
Corvin Rok13-Aug-14 21:07 
QuestionWhat about commas within fields? Pin
George Swan12-Aug-14 20:54
mveGeorge Swan12-Aug-14 20:54 
AnswerRe: What about commas within fields? Pin
ColinBurnell13-Aug-14 6:06
professionalColinBurnell13-Aug-14 6:06 
AnswerRe: What about commas within fields? Pin
jimpar13-Aug-14 8:50
jimpar13-Aug-14 8:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.