Import a CSV file to my datagridview

Question

I am working on a project where I have to import a CSV file, and display the results in a DataGridView. I am struggling to display my data fields to my datagridview, I want to be able add each row at a time so it parses them correctly. Here is my code so far.

   csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;
   int fieldCount = csv.FieldCount;
   string[] headers = csv.GetFieldHeaders();
   fieldCount = fieldCount - 1;

   //TO DO: Reading Header Information 

   for (int i = 0; i <= fieldCount; i++)
   {
       DataGridViewTextBoxColumn headerRow = new DataGridViewTextBoxColumn();
       headerRow.Name = headers[i];
       headerRow.HeaderText = headers[i];
       headerRow.Width = 100;
       dgvComplianceImport.Columns.Add(headerRow);
   }


   while (csv.ReadNextRecord())
   {
       //for (int i = 0; i < fieldCount; i++)
       //    string.Format("{0} = {1};",
       //                    headers[i],
       //                    csv[i] == null ? "MISSING" : csv[i]);



       //TO DO: for loop to add each data field row

       DataGridViewRow dgvr = new DataGridViewRow();
       for (int fieldCount = 0; fieldCount <= csv.FieldCount; fieldCount++)
       {
           string field = csv[fieldCount];


       }
       dgvr.Cells.Add(new DataGridViewCell());
       dgvComplianceImport.Rows.Add(dgvr);
   }

   dgvComplianceImport.DataSource = csv;

}

Solution 1

This is what I usually do:

1. Define a class where each property represents a CSV column
2. Use LINQToCSV (see here and here) to read the CSV file. It already gives me an IEnumerable<T> where T is my class.
3. Populate the DataGridView as you usually do (either manually, through bindings, etc.)

An example of how to read a CSV file

Let's assume the CSV file has the columns Name, Last Name, Age

Then you do define the following class:

class Person {
    [CsvColumn(FieldIndex = 0, CanBeNull = false, Name = "Name")]
    public string Name { get; set; }
    [CsvColumn(FieldIndex = 1, CanBeNull = true, Name = "Last Name")]
    public string Last Name { get; set; }
    [CsvColumn(FieldIndex = 2, CanBeNull = true, Name = "Age")]
    public int Age { get; set; }
}

Once you have it, you can read a list of Person from a CSV file like this:

public IEnumerable<Person> ReadFromCsv(string csvFile) {
    //Here you set some properties. Check the documentation.
    var csvFileDescription = new CsvFileDescription
    {
        FirstLineHasColumnNames = true,
        SeparatorChar = ',' //Specify the separator character.
    };

    var csvContext = new CsvContext();

    return csvContext.Read<Person>(csvFile, csvFileDescription);
}

Solution 2

A CSV file is a normal text file that's just comma delimited.

Basically what you want to do is open the text file and read through each line and split by the comma (",")

Use these links. They should help. http://www.codeproject.com/Articles/16951/Populating-data-from-a-CSV-file-to-a-DataGridView

http://www.c-sharpcorner.com/uploadfile/ankurmee/import-data-from-text-and-csv-file-to-datagridview-in-net/

http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/9efdbbd7-bfd9-4c7f-9198-791a4ca88a44/

Let me know if you still need some help writing the code.

Comments

[Joe M]

Hi Thank you for your response. The trouble is I don't want to spilt it by the comma because the csv file might have cells with commas within them. So I used this link to be able to parse the CSV File: codeproject.com/Articles/9258/A-Fast-CSV-Reader#latup. However, it hasn't worked exactly how I planned as when I display the results in the datagridview it doesn't let me edit it and I need to be able to edit the dgv and resave it.

[Joe M]

In order to prevent this I am trying to make it so it doesn't do this, I need to make loop through each data row in the CSV File and add each row at a time to the dgv. Sorry if this is a bit confusing.

[BeginnerCoder]

Can you tell me where you are getting the CSV file? Are you generating it from another application that you wrote? or is it some other application?

[Joe M]

Same application I just have a browse button to open a csv file from my documents.

[Oscar Mederos]

@JoeMarsden You don't need to do that work by yourself. See my answer.

[BeginnerCoder]

Sorry I meant to ask how you are generating it? I'm not 100% sure about this but if you have commas as part of your cell value then yes there will be that issue and then you could not open and parse the file.. but if you could just take it up a notch and maybe convert the cell values that have a comma to something else for the import process and then when you import it you could change it back. So what I'm trying to say is...example: cell 1 = 13,99 cell 2 = 15,00 then in the CSV file it would be 13,99,15,00 so you just make it convert 13,99 to 13ô99 and 15,00 to 15ô99

[BeginnerCoder]

and then you can change it back in the import code... you can try Oscars solution. It may be better... but if you can't get it then you can solve it by this way

[Oscar Mederos]

@BeginnerCoder If your separator char is ,, CSV allows you to have fields with , on it. The following is valid: john,doe,"1st Ave, City, CA". You just need to place that cell inside ". That's why you can't just split the string by ,. You don't need to worry about that work, since there already are libraries that parsae CSV file correctly for you.

[BeginnerCoder]

Joe use Oscars solution. It would work for you! Thanks Oscar :-)

[Joe M]

Both ideas are good. I will have a look into both, but just a querying if I was to be able to try and replace the commas in cells with nothing. Do you have a idea how I would go about this?

[Oscar Mederos]

@JoeMarsden Test it and let us know how it went