C#: How to import excel files into MS SQL table? (error: dsn keyword not supported)

Question

Server: MS SQL
Database: StudentRecord
Table: ExamScore (the table has only three fields: ExamID, StudentID, Score)
Excel file (per screenshot): the excel file has only one sheet, which has only three columns of data)

As an non IT professional, I have a quick question: If I want to import excel file data into server table (append new data to existing database table), how should I write the code? What happens if some records already exist in database table?

Edit (with additional sample code): I don't remember where I get below sample code from (I think I asked similar question a few years ago on previous msdn forum, then I saved the sample code), nor do I fully understand the code (I am not an IT professional). Now I modified the code (for my project), and it returns error "keyword dsn not supported", what does it mean, and how to fix it? If I use ODBC connection, how should I modify below code? I want to batch importing data, not one record at a time.

Thanks.

private void importExcelFileToolStripMenuItem_Click(object sender, EventArgs e)  
        {  
  
  
            var filePath = string.Empty;  
            OpenFileDialog OpenFile = new OpenFileDialog();  
              
            OpenFile.Filter = "Excel Files|*.xl*"; //Filter for excel file  
            OpenFile.Title = "Select your Test Score file";  
            OpenFile.FilterIndex = 2;  //Don't know what it mean  
            OpenFile.RestoreDirectory = true;  
  
            if (OpenFile.ShowDialog() == DialogResult.OK)  
            {  
                //Get the path of specified file  
                filePath = OpenFile.FileName;  
            }  
  
  
  
            Excel.Application xlApp = new Excel.Application();  
              
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);  
              
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];  
  
  
  
            SqlBulkCopy oSqlBulk = null;  
  
  
            OleDbConnection myExcelConn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + filePath + "; Extended Properties = 'Excel 8.0; HDR = Yes; IMEX = 1'");  
              
  
  
            try  
            {  
                myExcelConn.Open();  
  
                OleDbCommand ObjCmd = new OleDbCommand("SELECT ExamID, StudentID, Score FROM [" + xlWorksheet.Name + "$]", myExcelConn);  
  
                // READ THE DATA EXTRACTED FROM THE EXCEL FILE.  
                OleDbDataReader objBulkReader = null;  
                objBulkReader = ObjCmd.ExecuteReader();  
  
  
                /*  
                OleDbCommand objOleDB =  
                    new OleDbCommand("SELECT ExamID, StudentID, Score FROM xlWorksheet", myExcelConn);  
  
                // READ THE DATA EXTRACTED FROM THE EXCEL FILE.  
                OleDbDataReader objBulkReader = null;  
                objBulkReader = objOleDB.ExecuteReader();  
                */  
  
  
                xlWorkbook.Close(false);  
                xlApp.Quit();  
  
  
                // SET THE CONNECTION STRING.  
  
                using (SqlConnection con = new SqlConnection(GlobalVariables.ConnectionString))  
                {  
                    con.Open();  
  
                    // FINALLY, LOAD DATA INTO THE DATABASE TABLE.  
                    oSqlBulk = new SqlBulkCopy(con);  
                    oSqlBulk.DestinationTableName = "Test_score"; // TABLE NAME.  
                    oSqlBulk.WriteToServer(objBulkReader);  
                }  
  
                MessageBox.Show(GlobalVariables.Username + ": Your data imported sucessfully.");  
  
            }  
  
  
            catch (Exception ex)  
            {  
  
                MessageBox.Show(ex.Message);  
  
            }  
  
            finally  
            {  
                    // CLEAR.  
                    oSqlBulk.Close();  
                    oSqlBulk = null;  
                    myExcelConn.Close();  
                    myExcelConn = null;  
            }  
  
              
  
  
        }  
  
  

Answer 1

@VAer-4038 , If you want to use ODBC connection, we need to use OdbcConnection class to insert the data to MS SQL.

Here is a code example you could refer to.

The method needs to install other app:

private void button1_Click(object sender, EventArgs e)  
    {  
        var filePath = string.Empty;  
        OpenFileDialog OpenFile = new OpenFileDialog();  
  
        OpenFile.Filter = "Excel Files|*.xlsx*"; //Filter for excel file  
        OpenFile.Title = "Select your Test Score file";  
        OpenFile.FilterIndex = 2;    
        OpenFile.RestoreDirectory = true;  
  
        if (OpenFile.ShowDialog() == DialogResult.OK)  
        {  
            //Get the path of specified file  
            filePath = OpenFile.FileName;  
        }  
        Excel.Application xlApp = new Excel.Application();  
  
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);  
  
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];  
        string sheetName = xlWorksheet.Name;  
        xlWorkbook.Close();  
        xlApp.Quit();  
        string odbcstr = "Dsn=my sql;description=SQL SERVER;trusted_connection=Yes;app=Microsoft® Visual Studio®;wsid=Your DeviceName";  
  
        using (OdbcConnection con = new OdbcConnection(odbcstr))  
        {  
            con.Open();  
            string sqltext = string.Format("insert into StudentDetail SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database={0}', 'SELECT * FROM [{1}$]')", filePath, sheetName);  
            OdbcCommand cmd = new OdbcCommand(sqltext, con);  
            cmd.ExecuteNonQuery();  
        }  
  
       MessageBox.Show("Your data imported sucessfully");  
    }  

I make a method from the link ODBC Bulk Copy C#

Updated method for not installing any apps:

First, Please install the following nuget-package:

Second, you could try the following code to insert the data from excel to database:

private void button1_Click(object sender, EventArgs e)  
    {  
        var filePath = string.Empty;  
        OpenFileDialog OpenFile = new OpenFileDialog();  
  
        OpenFile.Filter = "Excel Files|*.xlsx*"; //Filter for excel file  
        OpenFile.Title = "Select your Test Score file";  
        OpenFile.FilterIndex = 2;  //Don't know what it mean  
        OpenFile.RestoreDirectory = true;  
  
        if (OpenFile.ShowDialog() == DialogResult.OK)  
        {  
            //Get the path of specified file  
            filePath = OpenFile.FileName;  
        }  
        DataTable table = new DataTable();  
        string odbcstr = "Dsn=my sql;description=SQL SERVER;trusted_connection=Yes;app=Microsoft® Visual Studio®;wsid=LANGUAGE-TEAM";  
        using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))  
        {  
            IExcelDataReader reader;  
            reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);  
            var conf = new ExcelDataSetConfiguration  
{  
    ConfigureDataTable = _ => new ExcelDataTableConfiguration  
    {  
        UseHeaderRow = true  
    }  
};  
  
            var dataSet = reader.AsDataSet(conf);  
  
            // Now you can get data from each sheet by its index or its "name"  
            table = dataSet.Tables[0];  
  
            //...  
        }  
        try  
        {  
            using (OdbcConnection con = new OdbcConnection(odbcstr))  
            {  
            con.Open();  
            foreach (DataRow item in table.Rows)  
            {  
                Console.WriteLine(item[0]);  
                Console.WriteLine(item[1]);  
                Console.WriteLine(item[2]);  
  
                string sqlinsert = string.Format("Insert into StudentDetail(ExamID,StudentID,Score) values('{0}','{1}','{2}')", item[0], item[1], item[2]);  
                OdbcCommand command = new OdbcCommand(sqlinsert, con);  
                command.ExecuteNonQuery();  
  
            }  
            con.Close();  
  
  
        }  
  
            MessageBox.Show(": Your data imported sucessfully.");  
        }  
        catch (Exception ex)  
        {  
            MessageBox.Show(ex.ToString());  
        }  
  
    }  

Result:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.