0

I am trying to use TextFieldParser with c# to parse a csv file and get the values of rows for specific columns.

For example a csv with..

Name,Age,Color,Sport
Tom,24,Blue,Football
Dick,21,Red,Hockey
Jane,19,Green,Basketball

string fullpath = "C:\myfile.csv"
using (TextFieldParser parser = new TextFieldParser(fullPath))
{
     parser.SetDelimiters(",");
     while (!parser.EndOfData)
     {
        // Processing row
        string[] fields = parser.ReadFields();
        int sportcount = 0;
        for (int i = 0; i < fields.Length; ++i)
        {
            string column = fields[i];
            DataColumn datecolumn = new DataColumn(column);
            datecolumn.AllowDBNull = true;
            if (column.Contains("Sport"))
            {
                Console.WriteLine("Found Sport at {0}", i);
                sportcount = i;
            }
        }
        string sportCol = fields[sportCount];

        // if that column contains a value
        if (!(sportCol.Equals("")) {
            // ignore the column header row
            if (parser.LineNumber > 2) {
                if (sportCol.Equals("Football")) {
                    Console.WriteLine("Found somebody playing football");
                }
            }
        }
    }
}

So idealy i'd like a list or array containing all the sports of those in the csv file. So it should contain (Football, Hockey, Basketball). Bearing in mind, the actual csv file has hundreds of columns and thousands of rows.

Update: I've update the above to something that is working for me. By assigning i to a variable when it finds a 'Sport' column, then I can search for that columns value down through the csv file.

John
  • 787
  • 4
  • 11
  • 28
  • `TextFieldParser` in wich library and namespace(i searched and found its something popular in vb)? consider [CsvHelper](https://joshclose.github.io/CsvHelper/) – Lei Yang Mar 02 '22 at 09:13
  • Correct, i did a few searches for parsing csv files and TextFieldParser popped up as a potential solution in Microsoft.VisualBasic. I just added the reference to my visual studio project. – John Mar 02 '22 at 09:24

1 Answers1

0

I guess by "trying" you mean it's something you've come across that can read csv and you're not wedded to it. As such I wanted to point out how it would look if you were to instead read this file with CsvHelper:

public class Person
{
    public int Age { get; set; }
    public string Name { get; set; }
    public string Color { get; set; }
    public string Sport { get; set; }
}

//in a method somewhere
using var reader = new StreamReader("path\\to\\file.csv"));
using var csv = new CsvReader(reader, CultureInfo.InvariantCulture));

foreach(var person in  csv.GetRecords<Person>()){
    //put code here
    Console.WriteLine(person.Name);

}

the actual csv file has hundreds of columns

You'll need to make a property for each column

idealy i'd like a list or array containing all the sports of those in the csv file

A distinct list of every sport?

Instead do:

csv.GetRecords<Person>().Select(p => p.Sport).Distinct().ToArray()

Bear in mind that reading a CSV in this way is forward only, so you can't call getRecords a second time without resetting the reader to the start of the file. Perhaps then if you have more to do you should:

var people = csv.GetRecords<Person>().ToList();

to load it all into memory then you can repeatedly query:

people.Sum(p => p.Age); //sum of all ages
people.Avg(p => p.Age); //avg age
people.Count(p => p.Color == "Red"); //count of reds
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • By 'trying' i mean it has got me to the point where i can get the index of the column i'm looking for. I just can't find any example of how to get the value of that column index for all the rows. In your example is it possibly to partial match on a column name? – John Mar 02 '22 at 10:57
  • I'm not sure what *partial match on a column name* means but you can use e.g. `[Index(1)]public int Age` to tell CsvH that Age is always in column 1, or you can [supply a list of N possible different column names for a property](https://joshclose.github.io/CsvHelper/examples/configuration/class-maps/mapping-by-alternate-names/) or you can use PrepareHeaderForMatch to change the header before it;s matched e.g. if [sometimes the headers have spaces and other times not](https://stackoverflow.com/questions/26817378/parse-csv-where-headers-contain-spaces-with-csvhelper) – Caius Jard Mar 02 '22 at 11:32