CsvReader to ignore blank column

Question

I am using CSVhelper for the first time. Another program is exporting a csv file the file looks like this

,"Employee","Earnings 1/Rate","Note"
,"John Doe","Regular 10.50", "Started Sep 1"

The problem is that I have a blank column to contend with at the start. I don't want the user to have to delete column using excel in order to use the file. So how can I either ignore the first column or get the column to add to my datatable.

The error (see comment line near bottom of program) is Field Blank does not exist in csv file

My class for reading in the data

namespace PayRateTracker
{
    public class TestRecord
    {
        public string Blank { get; set; }
        public string Employee {get; set;}
        public string Earning { get; set; }
        public string Note { get; set; }
    }

    public class MyClassMap : CsvClassMap<TestRecord>
    {
        public override void CreateMap()
        {
            Map(m => m.Blank).Index(0);
            Map(m => m.Employee).Index(1);
            Map(m => m.Earning).Index(2);
            Map(m => m.Note).Index(3);
        }
    }
}

I have a comment were the error is occurring near the bottom

namespace PayRateTracker
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

             //Stream reader will read test.csv file in current folder
             StreamReader sr = new StreamReader(@"G:\2013-09-20.csv");

             //Csv reader reads the stream
             CsvReader csvread = new CsvReader(sr);

            //csvread will fetch all record in one go to the IEnumerable object record
             IEnumerable<TestRecord> record = csvread.GetRecords<TestRecord>();

             //foreach (var rec in record) // Each record will be fetched and printed on the screen
             //{
             //    lblDisplay.Text = (string.Format("Name : {0}, Amount : {1}, Notes : {2} <br/>", rec.Employee, rec.Earning, rec.Note));
             //}
             sr.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            using (var stream = File.OpenRead(@"G:\Test.csv"))
            {
                using (var reader = new StreamReader(stream))
                {
                    using (var csvReader = new CsvReader(reader))
                    {
                        csvReader.Configuration.RegisterClassMap<MyClassMap>();
                        int i = 0;
                        while (csvReader.Read())
                        {
                            if (csvReader.FieldHeaders[0] == "")
                            {
                                csvReader.FieldHeaders[0] = "Blank";
                            }
                            if (i == 0)
                            {
                                foreach (var field in csvReader.FieldHeaders)
                                {
                                    dt.Columns.Add(field);
                                }
                            }

                            DataRow row = dt.NewRow();
                            foreach (var field in csvReader.FieldHeaders)
                            {
                                row[field] = csvReader.GetField(field);  // <-- Error here
                            }
                            dt.Rows.Add(row);

                            i += 1;
                        }
                    }
                }
            }
            //return dt;
            dataGridView1.DataSource = dt;
        }
    }
}

Answer 1

The issue here is that you're looking at the headers and if you see a blank header, you're changing it to "Blank" instead. Now when you're looping the field headers you're trying to get the field by "Blank", which doesn't exist.

Do you need the blank column as a part of your data table?

There are several changes you could make here...

You've created a class and a mapping, and you're not using either of them. You could do this:

var records = csvReader.GetRecords<TestRecord>();

records will be an IEnumerable<TestRecord>.

It sounds like the blank column isn't being used anywhere, so you can remove the mapping for it.

public class MyClassMap : CsvClassMap<TestRecord>
{
    public override void CreateMap()
    {
        Map(m => m.Employee).Index(1);
        Map(m => m.Earning).Index(2);
        Map(m => m.Note).Index(3);
    }
}

It also looks like you may just want to put whatever is in the CSV file into a data table using the same headers, but "Blank" instead if it's empty. You could just modify your current code slightly.

row[0] = csvReader[0];
// Skip the first column.
for( var j = 1; j < csvReader.FieldHeaders.Length; j++ )
{
    row[j] = csvReader[j];
}