Import multiple Excel Files into multiple Tables in database in ASP.Net MVC

Last Reply 4 months ago By dharmendr

Posted 5 months ago

Hello,

I want to upload 3 excel files with different column in each excel file and want to upload data of all the excel file in different tables in sql server DB.

Suppose, Excel File A data should be mapped to Table A in database and Excel File B data should be mapped to Table B and so on.

I am executing the code below but unable to figure out how can it work.

Please help me out here. Any suggestions would be much appreciated. Thanks!!

    public class HomeController : Controller
    {
        // GET: /Home/
        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
 
        OleDbConnection Econ;
 
        public ActionResult Index()
        {
            return View();
        }
 
        [HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            string filename = Guid.NewGuid() +Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertExceldata(filepath, filename);
            return View();
        } 
 
        private void ExcelConn(string filepath)
        {
            string constr =string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);
            Econ = new OleDbConnection(constr);
        } 
 
        private void InsertExceldata(string fileepath,string filename)
        {
            string fullpath = Server.MapPath("/excelfolder/") + filename;
            ExcelConn(fullpath);
            string query = string.Format("Select * from [{0}]", "Sheet1$");
            OleDbCommand Ecom = new OleDbCommand(query, Econ);
            Econ.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(query ,Econ);
            Econ.Close();
            oda.Fill(ds);
            DataTable dt = ds.Tables[0];
            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            objbulk.DestinationTableName = "tbl_registration";
            objbulk.ColumnMappings.Add("Email", "Email");
            objbulk.ColumnMappings.Add("Password", "Password");
            objbulk.ColumnMappings.Add("Name", "Name");
            objbulk.ColumnMappings.Add("Address", "Address");
            objbulk.ColumnMappings.Add("City", "City");
            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();
        }
    }

 

Posted 4 months ago

Hi Ayush,

In this example i have read each excel in DataTable and return all the DataTable in DataSet.

Then looping through the DataTable present in the DataSet inserting to database using SqlBulkCopy class WriteToServer method.

For table maping i have assigned DestinationTableName i have used the same name in the excel and passed the excel name to the DestinationTableName property.

And removed the ColumnMappings from the code. If you want to add ColumnMappings then check with condition.

Check this example. Now please take its reference and correct your code.

Namespaces

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Index(HttpPostedFileBase[] postedFile)
    {
        DataSet ds = GetDataTablesFromExcel(postedFile);
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Constring"].ConnectionString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    sqlBulkCopy.DestinationTableName = ds.Tables[0].TableName;
                    con.Open();
                    sqlBulkCopy.WriteToServer(ds.Tables[0]);
                    con.Close();
                }
            }
        }

        return View();
    }

    private DataSet GetDataTablesFromExcel(HttpPostedFileBase[] postedFiles)
    {
        DataSet ds = new DataSet();
        string path = Server.MapPath("~/Uploads/");
        if (!Directory.Exists(path))
        {
            Directory.CreateDirectory(path);
        }

        for (int j = 0; j < postedFiles.Length; j++)
        {
            HttpPostedFileBase postedFile = postedFiles[j];
            string filePath = string.Empty;
            filePath = path + Path.GetFileName(postedFile.FileName);
            string extension = Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(filePath);

            string conString = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03.
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 and above.
                    conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }
            conString = string.Format(conString, filePath);
            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;
                        connExcel.Open();
                        DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        connExcel.Close();
                        string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        connExcel.Open();
                        cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                        odaExcel.SelectCommand = cmdExcel;
                        DataTable dt = new DataTable();
                        dt.TableName = Path.GetFileNameWithoutExtension(postedFile.FileName);
                        odaExcel.Fill(dt);
                        connExcel.Close();
                        ds.Tables.Add(dt);
                    }
                }
            }
        }
        Directory.Delete(path, true);

        return ds;
    }
}

View

<% using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {%>
<input type="file" name="postedFile" multiple required />
<input type="submit" value="Import" />
<% } %>