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

VAer 756 Reputation points
2021-11-30T23:30:04.56+00:00

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.

153866-error-keyword-not-supported.jpg

153817-score.jpg

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;  
            }  
  
              
  
  
        }  
  
  
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,982 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jack J Jun 24,266 Reputation points Microsoft Vendor
    2021-12-01T07:11:19.947+00:00

    @VAer , 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:

    156171-image.png

    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:

    153947-image.png


    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.


0 additional answers

Sort by: Most helpful