How to Import data from Excel into Multiple tables

Question

User490317677 posted

Hi :)

Im trying Import data from a single Excel file in to, 2 Tables, but  my action just import data to One table when i upload a file , how should i modify my action to import data in to second table ?!

        [HttpPost]
        public ActionResult Index(ImportExcel importExcel)
        {
            if (ModelState.IsValid)
            {
                string path = Server.MapPath("~/Content/Upload/" + importExcel.file.FileName);
                importExcel.file.SaveAs(path);

                string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Properties='Excel 12.0 Xml;IMEX=1'";
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                //Sheet Name
                excelConnection.Open();
                string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
                excelConnection.Close();
                //End

                OleDbCommand cmd = new OleDbCommand("Select * from [" + tableName + "]", excelConnection);

                excelConnection.Open();

                OleDbDataReader dReader;
                dReader = cmd.ExecuteReader();
                SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["DBConString"].ConnectionString);

                //Destination for table one
                sqlBulk.DestinationTableName = "dbo.Table_ONE";

                //Mappings
                sqlBulk.ColumnMappings.Add("ManufacturerCode", "ManufacturerCode");
                sqlBulk.ColumnMappings.Add("SupportDescription", "SupportDescription");
                sqlBulk.ColumnMappings.Add("AlphabetCategori", "AlphabetCategori");


                sqlBulk.WriteToServer(dReader);
                excelConnection.Close();

                ViewBag.Result = "Successfully Imported";
            }
            return View();
        }

Answer 1

User-1038772411 posted

Hello, ManDown

I am trying to give you one logic regarding this you can do easily with that.

1> upload excel file and read data in c# model (master model).

2> create two model for two tables as per tables column.

3> store data in two diffrent model from master model.

4> while you want to insert record in tables (table1 & table2) then please you need to pass diffrent model in particular table.

5> Try this, you can easily do with this. you can also use entityframwork for that.

You can use LinqToExcel library also. if dont aware kindly refer document once before implement.

You can refer below Link alse :

https://stackoverflow.com/questions/3664067/import-data-from-excel-into-multiple-tables

Thanks.

Answer 2

User1520731567 posted

Hi ManDown,

According to your code,I didn't find your insert data to two table,only one table.

If you want to import data which from excel to two tables.

I suggest you could this article.and I can import to two tables successfully based on its code,like:

[HttpPost]
        public ActionResult ImportExcel_Index(HttpPostedFileBase postedFile)
        {
            string filePath = string.Empty;
            if (postedFile != null)
            {
                string path = Server.MapPath("~/Uploads/");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }

                filePath = path + Path.GetFileName(postedFile.FileName);
                string extension = Path.GetExtension(postedFile.FileName);
                postedFile.SaveAs(filePath);

                string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
  

                DataTable dt = new DataTable();
                conString = string.Format(conString, filePath);

                using (OleDbConnection connExcel = new OleDbConnection(conString))
                {
                    using (OleDbCommand cmdExcel = new OleDbCommand())
                    {
                        using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                        {
                            cmdExcel.Connection = connExcel;

                            //Get the name of First Sheet.
                            connExcel.Open();
                            DataTable dtExcelSchema;
                            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            connExcel.Close();

                            //Read Data from First Sheet.
                            connExcel.Open();
                            cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                            odaExcel.SelectCommand = cmdExcel;
                            odaExcel.Fill(dt);
                            connExcel.Close();
                        }
                    }
                }

                conString = ConfigurationManager.ConnectionStrings["sql_textConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name.
                        sqlBulkCopy.DestinationTableName = "dbo.Customers";//first table

                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Country", "Country");

                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }

                using (SqlConnection con2 = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con2))
                    {
                        //Set the database table name.
                        sqlBulkCopy.DestinationTableName = "dbo.Customers2";//second table

                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Country", "Country");

                        con2.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con2.Close();
                    }
                }
            }

            return View();
        }

How it works:

Best Regards.

Yuki Tao

Answer 3

User490317677 posted

Hi Yuki Tao,

I have one more Question, How can i implement  read data from a specific Column with your solution ! :)

As you see in image i want read/Get all data under Column C:

Answer 4

User1520731567 posted

Hi ManDown,

I have one more Question, How can i implement  read data from a specific Column with your solution ! :)

As you see in image i want read/Get all data under Column C:

Do you want to show emails format in SQL server or Excel?

If in SQL server,it can't be implemented,as far as I know,sql can't show html format.

If in Excel,you could use mailto,for example:

<a href="mailto:[e-mail]">Some text</a>

Best Regards.

Yuki Tao

Answer 5

User490317677 posted

i was something else i find out.