locked
SqlBulkCopy variable value to own column RRS feed

  • Question

  • User-2054142495 posted

    Hi,

    I'm trying to insert the first column value in my excel document into another column against every record that is inserted to the database.

    I can insert the data from the spreadsheet via SqlBulkInsert which can be seen in the code provided however i'm struggling with adding the value.

    Any help really appreciated.

                string excelPath = Server.MapPath("~/Files") + Path.GetFileName(FileUpload1.PostedFile.FileName);
                FileUpload1.SaveAs(excelPath);
    
                string conString = string.Empty;
                string extension = Path.GetExtension(FileUpload1.FileName);
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        break;
                    case ".xlsx": //Excel 07 or higher
                        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                        break;
                  
    
                }
    
                conString = string.Format(conString, excelPath);
                using (OleDbConnection excel_con = new OleDbConnection(conString))
                {
                    excel_con.Open();
                    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                    DataTable dtExcelData = new DataTable();
                   
                  
                   
                    dtExcelData.Columns.AddRange(new DataColumn[14] {
                    new DataColumn("Type", typeof(string)),
                    new DataColumn("Qty", typeof(string)),
                    new DataColumn("PartNumber", typeof(string)),
                    new DataColumn("Description", typeof(string)),
                    new DataColumn("Revison", typeof(string)),
                    new DataColumn("State", typeof(string)),
                    new DataColumn("Designator", typeof(string)),
                    new DataColumn("Location", typeof(string)),
                    new DataColumn("Process", typeof(string)),
                    new DataColumn("Material", typeof(string)),
                    new DataColumn("Finish", typeof(string)),
                    new DataColumn("Weight", typeof(string)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("File Name", typeof(string)),
                   
                    
                    
                    });
    foreach(){ }
    
                    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                    {
                        oda.Fill(dtExcelData);
    
                    }
                    excel_con.Close();
    
                    string drawno = (dtExcelData.Rows[0]["Type"].ToString());
                    string consString = ConfigurationManager.ConnectionStrings["Contact"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            
                            sqlBulkCopy.DestinationTableName = "dbo.mydatabase";
    
                         
                            sqlBulkCopy.ColumnMappings.Add("Type", "Type");
                            sqlBulkCopy.ColumnMappings.Add("Qty", "Qty");
                            sqlBulkCopy.ColumnMappings.Add("Number", "Number");
                            sqlBulkCopy.ColumnMappings.Add("[Description]", "[Description]");
                            sqlBulkCopy.ColumnMappings.Add("Revision", "Revision");
                            sqlBulkCopy.ColumnMappings.Add("State", "State");
                            sqlBulkCopy.ColumnMappings.Add("Designator", "Designator");
                            sqlBulkCopy.ColumnMappings.Add("Location", "Location");
                            sqlBulkCopy.ColumnMappings.Add("Process", "Process");
                            sqlBulkCopy.ColumnMappings.Add("Material", "Material");
                            sqlBulkCopy.ColumnMappings.Add("Finish", "Finish");
                            sqlBulkCopy.ColumnMappings.Add("Weight", "Weight");
                            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                            sqlBulkCopy.ColumnMappings.Add("[File Name]", "[File Name]");
                                               
    
    
                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
    
    
    
    

    Friday, July 12, 2019 3:09 PM

Answers

  • User665608656 posted

    Hi Weedy,

    You mean that there is a column in your database that is not in your excel table. When you import data in batches, you need to assign that column with custom variables, right?

    If so, I recommend that you add columns that do not exist in the excel table but exist in the database to the returned dtExcelData after getting the data from the excel table.

    After adding it, you can read dtExcelData circularly, assign new columns with your variables, and finally batch them with SqlBulkCopy.

    I did a simple example, assuming that my database has three columns: FirstName, LastName, EmailAddr, but my excel table has only two columns, FirstName and LastName.

    I need to insert the values of the two columns in excel into the database, and at the same time I need to insert custom values into the EmailAddr field:

                DataTable table= new DataTable();
                table.Columns.Add("EmailAddr", typeof(string));
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    table.Rows[i]["EmailAddr"] = "addr"+ i.ToString();
                }
                string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
                {
    
                    conn.Open();
                    using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn))
                    {
                        bulkCopy.DestinationTableName = "[Employee]";
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("EmailAddr", "EmailAddr");
                        bulkCopy.WriteToServer(table);
                    }
                }

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 15, 2019 7:01 AM

All replies

  • User-2054057000 posted

    Hi,

    I'm trying to insert the first column value in my excel document into another column against every record that is inserted to the database.

    I can insert the data from the spreadsheet via SqlBulkInsert which can be seen in the code provided however i'm struggling with adding the value.

    Any help really appreciated.

                string excelPath = Server.MapPath("~/Files") + Path.GetFileName(FileUpload1.PostedFile.FileName);
                FileUpload1.SaveAs(excelPath);
    
                string conString = string.Empty;
                string extension = Path.GetExtension(FileUpload1.FileName);
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        break;
                    case ".xlsx": //Excel 07 or higher
                        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                        break;
                  
    
                }
    
                conString = string.Format(conString, excelPath);
                using (OleDbConnection excel_con = new OleDbConnection(conString))
                {
                    excel_con.Open();
                    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                    DataTable dtExcelData = new DataTable();
                   
                  
                   
                    dtExcelData.Columns.AddRange(new DataColumn[14] {
                    new DataColumn("Type", typeof(string)),
                    new DataColumn("Qty", typeof(string)),
                    new DataColumn("PartNumber", typeof(string)),
                    new DataColumn("Description", typeof(string)),
                    new DataColumn("Revison", typeof(string)),
                    new DataColumn("State", typeof(string)),
                    new DataColumn("Designator", typeof(string)),
                    new DataColumn("Location", typeof(string)),
                    new DataColumn("Process", typeof(string)),
                    new DataColumn("Material", typeof(string)),
                    new DataColumn("Finish", typeof(string)),
                    new DataColumn("Weight", typeof(string)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("File Name", typeof(string)),
                   
                    
                    
                    });
    foreach(){ }
    
                    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                    {
                        oda.Fill(dtExcelData);
    
                    }
                    excel_con.Close();
    
                    string drawno = (dtExcelData.Rows[0]["Type"].ToString());
                    string consString = ConfigurationManager.ConnectionStrings["Contact"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            
                            sqlBulkCopy.DestinationTableName = "dbo.mydatabase";
    
                         
                            sqlBulkCopy.ColumnMappings.Add("Type", "Type");
                            sqlBulkCopy.ColumnMappings.Add("Qty", "Qty");
                            sqlBulkCopy.ColumnMappings.Add("Number", "Number");
                            sqlBulkCopy.ColumnMappings.Add("[Description]", "[Description]");
                            sqlBulkCopy.ColumnMappings.Add("Revision", "Revision");
                            sqlBulkCopy.ColumnMappings.Add("State", "State");
                            sqlBulkCopy.ColumnMappings.Add("Designator", "Designator");
                            sqlBulkCopy.ColumnMappings.Add("Location", "Location");
                            sqlBulkCopy.ColumnMappings.Add("Process", "Process");
                            sqlBulkCopy.ColumnMappings.Add("Material", "Material");
                            sqlBulkCopy.ColumnMappings.Add("Finish", "Finish");
                            sqlBulkCopy.ColumnMappings.Add("Weight", "Weight");
                            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                            sqlBulkCopy.ColumnMappings.Add("[File Name]", "[File Name]");
                                               
    
    
                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
    
    
    

    Looking at your code tells me that you are using SQLBULKCOPY Class of C# for doing EXCEL FILE implementation. There is a good article to reference the proper codes for it here - https://www.yogihosting.com/import-excel-asp-net-mvc/

    Saturday, July 13, 2019 9:58 AM
  • User-2054142495 posted
    Hi Yogyogi,
    The code i submitted for my sqlbulkcopy actually works the way i want it to. Im trying to add another field which is not in the excel spreadsheet to the database and fill that field and all the rows in that columnwith a variable.
    Saturday, July 13, 2019 11:35 AM
  • User665608656 posted

    Hi Weedy,

    You mean that there is a column in your database that is not in your excel table. When you import data in batches, you need to assign that column with custom variables, right?

    If so, I recommend that you add columns that do not exist in the excel table but exist in the database to the returned dtExcelData after getting the data from the excel table.

    After adding it, you can read dtExcelData circularly, assign new columns with your variables, and finally batch them with SqlBulkCopy.

    I did a simple example, assuming that my database has three columns: FirstName, LastName, EmailAddr, but my excel table has only two columns, FirstName and LastName.

    I need to insert the values of the two columns in excel into the database, and at the same time I need to insert custom values into the EmailAddr field:

                DataTable table= new DataTable();
                table.Columns.Add("EmailAddr", typeof(string));
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    table.Rows[i]["EmailAddr"] = "addr"+ i.ToString();
                }
                string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
                {
    
                    conn.Open();
                    using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn))
                    {
                        bulkCopy.DestinationTableName = "[Employee]";
                        bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                        bulkCopy.ColumnMappings.Add("LastName", "LastName");
                        bulkCopy.ColumnMappings.Add("EmailAddr", "EmailAddr");
                        bulkCopy.WriteToServer(table);
                    }
                }

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 15, 2019 7:01 AM
  • User-2054142495 posted

    This is exactly what I was after.

    Thank you YongQing

    Monday, July 15, 2019 8:49 AM