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 · Accepted Answer · 2020-10-28 18:12:39Z

1

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);

edited Oct 28, 2020 at 18:12

answered Oct 28, 2020 at 14:35

Julián

1,4061 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3930306 Over a year ago

Thank you for your answer. But in my case, the exam names could be like Science, Art, History, Mathematics etc..

Julián Over a year ago

But does your excel file always have the number of columns for your exams? or sometimes there are 4, 10, 15 exams (columns)?

user3930306 Over a year ago

Yes, The first three columns are fixed, but the number of exams columns can be vary like 5,8,10 exams per excel sheet.

Julián Over a year ago

I have modified the code to take from the third column to the last column, dynamically taking the name of the exam

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related