Read data from excel using column name and insert in to SQL table in C#

Question

I am implementing a system to upload exam marks using Excel sheet. Excel sheet content the student Index number, Name and Grade and the Exams they have taken with Pass/Fail status. Names of the exams and number of exams need to face may vary from student to student.

[Excel sheet format]

Index# | Name   | Grade | Exam 1 | Exam 2 | Exam 3 | Exam 4 
  1    | John   | 5     | Pass   | Fail   | N/A    | Pass
  2    | Tom    | 6     | Pass   | Pass   | Pass   | N/A
  3    | Bob    | 8     | N/A    | Fail   | Pass   | Pass
  4    | Harry  | 7     | Pass   | Pass   | Pass   | Pass

I need to save this data into a SQL table in this format

Index# | Name   | Grade | Exam_Name | Status
  1    | John   | 5     | Exam 1    | Pass
  1    | John   | 5     | Exam 2    | Fail
  1    | John   | 5     | Exam 4    | Pass
  2    | Tom    | 6     | Exam 1    | Pass
  2    | Tom    | 6     | Exam 2    | Pass
  2    | Tom    | 6     | Exam 3    | Pass
etc.

I manage to read the column name from the excel sheet and add it to a list using this code.

 System.Data.OleDb.OleDbConnection Sconn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Savelocation + ("" + FileName + "") + ";Extended Properties=Excel 8.0");

  var adapter = new OleDbDataAdapter("SELECT * FROM [Marks$]", Sconn);
                var ds = new DataSet();
                adapter.Fill(ds, "myTable");
                DataTable data = ds.Tables["myTable"];
                List<string> ColumnList = new List<string>();

                foreach (DataColumn dc in data.Columns) 
                {
                    ColumnList.Add(dc.ColumnName);
                }

Now I need to read the data row by row in the excel sheet using this column names. Can anyone help me please ?

Julián Julián · Accepted Answer · 2020-10-28T14:35:57

You can try this:

For this example I have created a class to collect the results by rows that will be destined to SQL:

C#

public class Result
{

    public string Index { get; set; }

    public string Name { get; set; }

    public string Grade { get; set; }

    public string Exam_Name { get; set; }

    public string Status { get; set; }

}

And the code for read the Excel file:

C#

System.Data.OleDb.OleDbConnection Sconn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Savelocation + ("" + FileName + "") + ";Extended Properties=Excel 8.0");

var adapter = new OleDbDataAdapter("SELECT * FROM [Marks$]", Sconn);
var ds = new DataSet();
adapter.Fill(ds, "myTable");
DataTable data = ds.Tables["myTable"];
List<Result> r = new List<Result>();

foreach (DataRow row in data.Rows)
{

    for (int i = 3; i < data.Columns.Count; i++)
    {
        string indexCell = (string)row["Index"],
                nameCell =  (string)row["Name"],
                gradeCell = (string)row["Grade"],
                examCell =  data.Columns[i].ColumnName,
                passCell =  (string)row[i];

        r.Add(new Result()
        {
            Index = indexCell,
            Name = nameCell,
            Grade = gradeCell,
            Exam_Name = examCell,
            Status = passCell
        });

        // Or call your logic for send this data to SQL and INSERT the data
    }
                
}

Console.Write(r);

Read data from excel using column name and insert in to SQL table in C#

1 Answers